Question re command buttons in excel.

  oo7juk 15:54 09 Nov 07


I have a worbook that contains 2 sheets, but want to add a further two if the following (last paragraph)can be done.

Info -

The first sheet is a results page that displays info relating to streets.
The second sheet contains the streets data that populates sheet1 which uses the vlookup function.
The third street is the exact same, but contains info relating to sites.
And the fourth contains site data as above.

I'm also running 2 macros on this workbook. The first restricts the scroll area and the second removes scroll bars, formula bars etc.(Auto open/ Auto close)

Is it possible to create a blank sheet, And when the workbook is opened the blank sheet contains 2 buttons, one titled streets and the other sites. After the button has been selected opens up target worksheet.

Thank you.

  silverous 17:15 09 Nov 07

Yes. Do you know how to create buttons?

View, Toolbars, Control Toolbox.

Find the "Command Button" on that toolbar.

Click and drag where you want your button on your first sheet.

It will get named "CommandButton1"

Right click on it and pick "properties" you can then set its caption to be whatever you want to call it (e.g. "Streets")

Double click the button and you will be presented with a VBA window. In between the "Private" and "End Sub" put the following:


(replacing Streets with whatever sheet you want to go to.

To test it you need to take your sheet out of "Design mode" - to do that you click on the icon on the control toolbox which looks like a blue protractor with a ruler underneath.

Then it should work! Do it again for the other button replacing the relevant bits (caption and the sheet name) and you are sorted.

  oo7juk 11:38 13 Nov 07

Many thanks for your assistance, that was great.

This thread is now locked and can not be replied to.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…