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!

 

[ShoYnn]

The syntax is .Visible = xlSheetVisible

Sweet. I will try this tomorrow when I get back to work and report on the results! Thanks!

 

[ShoYnn]

Lol, so through a lot (and I mean at least 2 hours worth) of trial and error I did a ton of research and both figured out my issue and my solution, and the solution is so simple that it makes sense why I missed it in the first place!

So the method that I was trying to use was the one where the hyperlink calls forth a function, and the function was what was going to unhide the specific sheet and then go to it. Well, it turns oit that if a function was called by a cell, the most it could do is slightly manipulate the sheet that the cell is in, but it can't manipulate the workbook. So those times where I would click on the hyperlink and the screen would flicker like it was trying to do something but not actually doing anything was me having the code correct, just Excel telling me I'm dumb.

The solution that I ran into was to create a master index sheet and a master file sheet for the sheets that the index will take you to, and in the vba for those sheets would be the follow_hyperlink sub with all of the coding to make the unhide and go or rehide and return functionality work, and then have the coding that creates the new sheets just make a copy of the master sheets and rename them instead of creating a whole new sheet from scratch!

Thanks to everyone who tried helping me out with this one, and hopefully other people find this and find it helpful! When I get the book working fully I can post it here so that anyone interested can download it and see the coding and stuff.