Display worksheet with prompt/button

  • Thread starter Thread starter alanad
  • Start date Start date
A

alanad

I was wondering if there is a way to have Excel prompt the user with
question, then open up a worksheet based on the answer to tha
question?

For example, when Excel opens it could ask the user to make th
appropriate choice about what part of the workbook they want to use
then open up the corresponding worksheet....ie. if the user wants th
"apples" worksheet they could select the apples button and if they wan
the "oranges" worksheet they could select the oranges button and woul
be taken to the appropriate worksheet based on the button they select
 
Create a userform (named, say, ChooseForm) with a command button for
each sheet. Then put this code in the ThisWorkbook code module:


Private Sub Workbook_Open()
Dim cf As ChooseForm
Set cf = New ChooseForm
cf.Show
Set cf = Nothing
End Sub

and put this in the userform's code module:

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("Apples").Select
Unload Me
End Sub


Private Sub CommandButton2_Click()
ThisWorkbook.Sheets("Oranges").Select
Unload Me
End Sub
 
Hi

yes - one way, copy and paste the following code into a module sheet in the
workbook of interest (right mouse click on a sheet tab, choose view code,
choose Insert / Module ... paste the following from SUB to END SUB on the
right hand side of the screen) ... adjust the question as appropriate.

Then to link it to a button, create a button (if you use the command button
control off the Forms toolbar the macro dialog box will open up
automatically - choose selectsheet and click OK.


sub selectsheet()
dim gotosheet as string
Do
gotosheet = Inputbox("What is your favourite fruit?","Choose
Fruit","Apples")
Loop Until gotosheet<>""
ActiveWorkbook.Sheets(gotosheet).activate
End Sub

let us know how you go
Cheers
JulieD
 
Back
Top