+ Reply to Thread
Results 1 to 3 of 3

Worksheet Freezing After Clicking Hyperlink

  1. #1
    Forum Contributor
    Join Date
    05-17-2007
    Location
    Michigan, US
    MS-Off Ver
    2007
    Posts
    111

    Worksheet Freezing After Clicking Hyperlink

    I'm adding the following code to create a hyperlink from one worksheet tab to another. The hyperlink works fine and takes me to Cell A6 on the other sheet, but then the worksheet kind of freezes. It won't allow me to use my arrow keys to navigate to different cells.

    The reason that I have to add the hyperlink via VBA is that the worksheet the hyperlink will be on is protected. In order for the hyperlink to work it has to be in an unlocked cell. Because it is in an unlocked cell, I need a way to ensure the hyperlink will be rebuilt if the user accidentally deletes it, or otherwise messes it up.

    I know you like to see the offending spreadsheet, but it has too much personal information in it now to upload. I've created a simple spreadsheet with the same hyperlink code and attached that instead, but the problem does not show up in this simple spreadsheet, so it must be something in my real spreadsheet.

    I have some buttons on my real spreadsheet which activate macros. One simple button just performs an Autofit on the columns. If I hit this button to Autofit the columns then everything else is fine again. So my code I'm using to create this hyperlink must somehow be causing a VBA crash, or hang up in some way and it wont get unstuck until more VBA is activated.

    Without seeing the actual offending spreadsheet, can you see anything wrong with the code below?

    Please Login or Register  to view this content.
    In the small spreadsheet I've attached, I am activating the code when the spreadsheet tab is activated. On the offending spreadsheet, I am activating the code from a long If, Then, ElseIf statement. The real code gets activated by the following ElseIf statement:
    Please Login or Register  to view this content.
    Please look at the line where I am trying to tell it to set cell A1 (the one with the hyperlink) to unlocked by stating Locked = False. I had to do that because for some reason even though I would unlock that cell before protecting the worksheet, sometimes it would randomly get locked again.
    Attached Files Attached Files
    Last edited by Spencer; 03-21-2011 at 06:38 PM.

  2. #2
    Forum Contributor
    Join Date
    05-17-2007
    Location
    Michigan, US
    MS-Off Ver
    2007
    Posts
    111

    Re: Worksheet Freezing After Clicking Hyperlink

    I'm wondering if the problem might be with the code on the destination worksheet.

    That destination worksheet (where I land after I click the hyperling) has a Worksheet_SelectionChange sub. I'm wondering if that SelectionChange sub is getting confused when my hyperlink takes me to cell C6.

    Is there some code I can put at the beginning of that SelectionChange sub to prevent confusion?

    I tried these separately and together, but it didn't help:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The problem is random, it doesn't do it all the time, so it is making it harder for me to find a pattern. When it is stuck on cell C6 I can sometimes select a different cell with my mouse and both cells appear to be selected according to their borders.

  3. #3
    Forum Contributor
    Join Date
    05-17-2007
    Location
    Michigan, US
    MS-Off Ver
    2007
    Posts
    111

    Re: Worksheet Freezing After Clicking Hyperlink

    I think I figured it out. Moderators, please feel free to delete this thread if you like.

    In case the thread remains, here is what I think I figured out.

    The code to add the hyperlink was in a long If, Then, ElseIf statement, but that statement was in a SelectionChange sub. I only needed the hyplerlink code to fire if someone deleted it or otherwise messed it up, so it really should have been in a Worksheet Change sub instead.

    I think what was happening is that as I was selecting the hyperlink I really was triggering a SelectionChange, which was rewriting the Hyperlink at the same time I was being directed to cell A6 in the other worksheet. I think the SelectionChange sub on the original worksheet was firing at the same time the SelectionChange sub on the destinatino worksheet was firing (sometimes, not always and that is why I think the problem was intermittent).

    I moved it into a Worksheet Change sub and everything seems alright now. I also moved my End With statement up two lines so it is above the Me.Protect line, which is where I think it should be.

    Thanks, Spence
    Last edited by Spencer; 03-21-2011 at 06:40 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1