Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Option Buttons/Radio Buttons | New Users to Excel | |||
have toggle buttons but everytime print preview buttons move | Excel Discussion (Misc queries) | |||
Naming of MsgBox Buttons | Excel Programming | |||
Naming of MsgBox Buttons | Excel Programming | |||
Naming buttons in Userform on the fly | Excel Programming |