Programming Buttons in Excel

[Jerminator]

Just getting started with VB and VBA. I do a lot of Workbooks monthly for my customers that I import a .csv and I try to have it automated as much as possible. What I would like to do is add buttons to my first worksheet and hide the remainder sheets. Which I have accomplished. The problem I am having is programming the buttons on click to open a particular hidden sheet in the workbook. Is this possible and does anyone know what the command for this would be? I would greatly appreciate it.

 

[Jerminator]

I believe so. I would start the recorder, then perform how I would unhide the sheet. I think thats right??

 

[elgringo56]

yes, ok, open your first sheet, then start the recorder and give a name to your macro. Then click ok, and a small box saying stop will appear. disregard that box for now. go thru all the steps to open your hidden sheet. when it is open, then click on the stop button. then close the hidden sheet to get back to your initial sheet and save it. Then get back to me.

 

[Jerminator]

OK, I got the macro recorder and the sheet saved. I assumed when you said to close the second sheet, you meant for me to re-hide it?

 

[Jak]

Hi Jerminator

If you follow the suggestion of recording a macro i.e. you go to Format, select Sheet and click on Unhide it will show you all the hidden sheets. In this example I clicked sheet5 and ended the macro recorder. This is what you get.

Sub Showit()
'

Sheets("Sheet5").Visible = True
End Sub

You indicate that you do not want the user to input any data. Therefore using the above method you would need a button for each sheet. Are you looking for a way to select a sheet from a list and unhide it?

 

[elgringo56]

Ok, now you have the macro for it. now we need to make the button and assign it to the macro

 

[elgringo56]

I guess JAK wants to take over here, so its your choice

 

[Jerminator]

I have the button built on the first sheet.

JAK, Would I ultimately am looking for is for the user to click button and a popup type window opens with a hidden sheet in its contents. Don't know if this is possible. Maybe, but might require a bit of programming. If you have any idea, I would greatly appreciate it!

 

[elgringo56]

If the button is made, right click on it and assign it to the macor that we just made. then when the user clicks this button, that sheet will open

 

[Jerminator]

It really doesn't matter, I am just happy that I am getting some help. I have been reading books and books and websites trying to figure out how to accomplish what I am trying to do. I will owe you both if we can make this happen.

 

[Jak]

Hi Elgringo56

Sorry for jumping in both feet first, I did not read all the posts. I now see what you were doing. I will stand in the corner for 10 minutes ;P)

Hi Jerminator

I will play around with some ideas and get back to you with some instructions.