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?
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 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.
Bookmarks