Question re command buttons in excel.

  oo7juk 15:54 09 Nov 2007


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 2007

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 2007

Many thanks for your assistance, that was great.

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Best Keyboards for Designers & Artists

What to ask Siri on the HomePod

Meilleurs VPN (2018)