Using Userform to switch between worksheets

[hernandj]

Is it possible to use a VBA user form to switch between worksheets. I would like to use a VBA userform as a menu selector. The user would make a selection from a list box and a sheet would be opened. When that sheet is closed, the list box would be displayed for the next worksheet selection.

Can this be done? If so, is there an example available?

Thanks in advance for your help.

 

[diddi]

oh yes, thats quite easy.

drow a userform with a couple of command buttons .

the code for the commandbuttons is something like

sub commandbutton1_click
    Sheets("Sheet1").select
End sub

set the userform ShowModal to false and it will sit like a pallet and switch all your windows for you

 

[diddi]

oh, and to get it to appear automatically...

in the thisworkbook module place this sub

Sub workbook_open()
    MyMenuUserForm.Show    'use your userform name here
End Sub

and to block the close button on the userform so your menu doesnt disappear...
put this in the userform code module

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
    End If
End Sub

 

[Trebor76]

You can do this simply by right clicking on the navigation bar (bottom left hand corner of your workbook) and clicking on the desired tab (the active tab will have a tick next to its name).

 

[diddi]

@Trebor76
i think the idea is to hide the tabs and make a pretty menu page...?

 

[Trebor76]

Sorry diddi, my bad.

 

[diddi]

@Trebor76
just not good enough

 

[Trebor76]

just not good enough

LOL

How's the weather in VIC today? Overcast in Sydney.

 

[diddi]

nice, mid 20s, bit of cloud.

was up your way sort of on the weekend working at canberra show.. stinking hot one day and blowing and rainy the next. a bit like the pollies.

 

[hernandj]

Thanks. I am very anxious to try this.

You are correct. After 40 years of working on computers, I am amazed by how little I know.