Community archive

Ask something
Discussion Locked

Creating Pushbutton Forms In Excel

0 helpful
2 replies

by williamsandrew

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
I'm fiddling with an idea at the moment to do with our work objectives sheets.

I created a new lot on Excel 2000 and have 1 for morning, 1 for afternoon and 1 for night shifts.

These are represented on a worksheet as part of the same Excel file (workbook).

I then created a fourth sheet (which is to the left of the other 3) as a menu page and made some square buttons (morning shift, afternoon shift, night shift).

I recorded macros so when I click on the respective buttons, it takes you to the respective worksheets.

I then wanted to hide everything so no one can stuff things up on me. I figured out how to hide the worksheets I wanted. The problem is after
hiding the morning, afternoon and night worksheets, and then clicking on the buttons on the menu page, a runtime error 1004 comes up.

If I unhide everything, it works.

But that's not what I want. I want to create the file so the main menu worksheet is only viewable (and not infinitely when you keep scrolling
down or to the right, I want to scrap scrolling capabilities but hiding the scroll bars don't change it). So when an officer clicks on the
respective button on this menu sheet, it opens up the respective objective sheet which they can then modify and complete.

I just want to take it that one step further and make a flash job of it.

Are you able to shed light please? I know nothing about VBA and only heard about it the other week.

Thanks
Andrew.

2 replies

by mtmoriah

You are on the right track when you say that you have to unhide everything in order for it to work. The key is to include code to specifically hide and unhide each specific sheet, as needed. Edit your macros by pressing Alt + F11 to enter the Visual Basic Editor. Open the project Explorer pane by pressing Ctrl + R. Double click on the appropriate module to open a window containing the code for your macro. In the macro code, you need to have a line of code at the top that unhides the desired sheet being navigated to.

Example: Sheets("Sheet1").Visible = True

You would change "Sheet1" to be the name of the worksheet in your specific instance.

At the end of the code, you need anot
Sheets("Sheet4").Visible = False

by mtmoriah

You are on the right track when you say that you have to unhide everything in order for it to work. The key is to include code to specifically hide and unhide each specific sheet, as needed. Edit your macros by pressing Alt + F11 to enter the Visual Basic Editor. Open the project Explorer pane by pressing Ctrl + R. Double click on the appropriate module to open a window containing the code for your macro. In the macro code, you need to have a line of code at the top that unhides the desired sheet being navigated to:

Example: Sheets("Sheet1").Visible = True

(You would change "Sheet1", as appropriate, to the name of the worksheet in your specific instance.)

At the end of the code, you need another line of code to hide the menu page:

Example: Sheets("Sheet4").Visible = False

The thing with this is that you need to add a command button to each page being navigated to that the user can click on to return to the menu page. This command button would need two lines of code, the first line to make the menu page visible and the second line to hide the page being navigated away from. (Same type of code as shown above.)

I hope this helps. Sorry about the incomplete submission above. That'll teach me to type in the dark.

This discussion is closed to new replies.