Naming vba Buttons
 

In very basic terms I have a spreadhseet with three sheets... I want a
user to be prompted to select 1 of the three sheets and each of the
three sheets will then require different code... I have the different
code but do not know how to get the suer to
select one of the three sheets...

My simple idea of code is thus...

Sub test()
result = MsgBox("Select Sheet", vbSheet1Sheet2Sheet3, "Banking")
If result = vbSheet1 Then
GoTo label1
Else
If result = vbSheet2 Then
GoTo label2
Else
If result = vbSheet3 Then
GoTo label3
End If
End If
End If

label1:
'Some code here
label2:
'Some code here
label3:
'Some code here

End Sub

I've worked out that it's not as simple as I would like it to be... but
can't use logic to work out the other way around... any ideas?

Many many thanks to you fine people...

Will

Naming vba Buttons
 

Why not get them to activate the sheet and use worksheet activate code to
run?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Sheet1":
'sheet 1 code
Case "Sheet2":
'sheet 2 code
Case "Sheet3":
'sheet 3 code
End Select
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"solomon_monkey" wrote in message
oups.com...
In very basic terms I have a spreadhseet with three sheets... I want a
user to be prompted to select 1 of the three sheets and each of the
three sheets will then require different code... I have the different
code but do not know how to get the suer to
select one of the three sheets...

My simple idea of code is thus...

Sub test()
result = MsgBox("Select Sheet", vbSheet1Sheet2Sheet3, "Banking")
If result = vbSheet1 Then
GoTo label1
Else
If result = vbSheet2 Then
GoTo label2
Else
If result = vbSheet3 Then
GoTo label3
End If
End If
End If

label1:
'Some code here
label2:
'Some code here
label3:
'Some code here

End Sub

I've worked out that it's not as simple as I would like it to be... but
can't use logic to work out the other way around... any ideas?

Many many thanks to you fine people...

Will

Naming vba Buttons
 

Nice idea but the code need not run every time the sheet is selected.

How do you set it up so when the spreadsheet is selected it runs the
code?

What other solution would you suggest?

Thanks again...

Naming vba Buttons
 

Maybe

Private Sub Workbook_Open()
Dim ans
ans=MsgBox("Supply a value from this list" & vbCrLf & _
"Sheet1, Sheet2, Sheet3")
Select Case ans
Case "Sheet1":
'sheet 1 code
Case "Sheet2":
'sheet 2 code
Case "Sheet3":
'sheet 3 code
End Select
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)


"solomon_monkey" wrote in message
ups.com...
Nice idea but the code need not run every time the sheet is selected.

How do you set it up so when the spreadsheet is selected it runs the
code?

What other solution would you suggest?

Thanks again...