VBA to write VBA?

[ShoYnn]

So I know this sounds crazy, but can VBA inject VBA into a sheet?

Sauce: I am creating a workbook that will bea collection of training data. The first sheet will be a data entry sheet so that all of the stuff put in this sheet will be formatted the same. There will be a submit button at the bottom that will create a new sheet based on the data entered. It will then hide this new sheet. The will also be a sheet that will act as a table of contents, and a hyperlink will be created on that sheet. When the hyperlink is clicked, it will unhide the associated sheet (the one that was created) and take the user to it. On that sheet will be a hyperlink that will take the user back to the index sheet and hide the sheet the user just came from.

From what I have read on the interwebs elsewhere, in order to have a hyperlink hide a sheet when clicked, that sheet will have to have VBA in it that activates upon clicking the hyperlink. As this workbook will be used to create a huge library (which is why I am wanting to hide the sheets that get created), it will be a pain to go into every sheet as it gets created to add the hide on hyperlink click VBA, so I am wondering if there is some way in the coding that will create the sheet can also write the hiding VBA as well? I am hoping to make this workbook as low maintenance as possible.

Or if anyone has a better idea on how to auto hide a sheet while taking the user back to the index sheet? Thanks everyone!

 

[Gokhan Aycan]

Instead you could use Workbook_SheetActivate and _SheetDeactivate events to hide any sheet not relevant perhaps. Worksheet_Activate on index sheet could be used too. Depends on how you want to approach it.

 

[ShoYnn]

Instead you could use Workbook_SheetActivate and _SheetDeactivate events to hide any sheet not relevant perhaps. Worksheet_Activate on index sheet could be used too. Depends on how you want to approach it.

I am definitely looking for the lowest maintenance route possible. I am making this sheet for a lot of people that barely know how to turn computers on, and I am hoping to wash my hands of this project as much as possible once it is made. _SheetActivate and deactivate definitely sound like good ideas. That would just go into the Workbook "sheet" in the VBA window, correct?

 

[Gokhan Aycan]

If I understand it right, yes.

 

[ShoYnn]

If I understand it right, yes.

View attachment 45387

Sweet. When I get to work tomorrow and get it all coded I will let you know the results!

 

[johnnyL]

As far as I know, hyperlinks can't unhide sheets.

 

[ShoYnn]

As far as I know, hyperlinks can't unhide sheets.

Agreed, the hyperlink itself cannot unhide a sheet, but a VBA code that activates upon clicking the hyperlink (I forgot exactly what the action is labeled) could be written to unhide the sheet and go to it

 

[ShoYnn]

If I understand it right, yes.

View attachment 45387

Sorry it took me so long to get back to you on this, but it turns out that, while your idea would definitely have worked, I couldn't use it in my scenario as my index sheets are being created via vba as well, so I would constantly have to go back into the sheet and add the code to each index sheet. Instead I have found this gem which will do exactly what I need, I just need to put the code in that I need it to run!

In Excel, can I use a hyperlink to run vba macro?

I have a spreadsheet that has many rows of data. I would like to be able to click on a cell that will run a macro using the data from that row. Since the number of rows will always be changing, I t...

stackoverflow.com

Thanks for the suggestion all the same!

 

[ShoYnn]

Ok, here is a new one, using method in the link in my previous post I have managed to make a few different things happen except for what I want to happen.

The setup:

On my index sheet, cell B is where the hyperlink is that calls the function to run. In cell D of the associated row is the sheet name that I want to open.

Using this

Dim sht as Range
Set sht = range("D" & selection.row)
MsgBox sht

Gets me a message box that displays the text in cell D of the associated row where the hyperlink is, which is the name of the sheet that link needs to unhide. However, when I add the line

Sheets(sht).visible = true

If gives me an error. Even if I try

Sheets(range(sht).value).visible = true

It still errors. So then I tried this

Dim sht as String
Sht = "D" and selection. Row
MsgBox sht

Which gives me the actual cell label (ie D13) in the message box, but again when I try using that to unhide the sheet it doesn't work. This time though it does something weird. The screen flickers like it is trying to do something, then nothing actually happens and the pointer changes to the big plus sign and won't let me click on the hyperlink again until I go to a different sheet and then back to the index sheet. Anyone got anything for this? Also, not sure if this is relevant or not, but the index sheet is locked. I don't think that matters as when I run the code with just the message box it works fine. Thanks for anything anyone can provide!

 

[Gokhan Aycan]

The syntax is .Visible = xlSheetVisible