Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command Button and macro
I have a command button and combobox on sheet1. The combobox is a list
of the sheets by known name ie "Sales " is sheet3 "Expenses" is sheet9 etc. The known name is at cell A4 on the relevant spreadsheet. So if someone picks Sales from the combobox and then presses the command button i want to go to that sheet. What is the best way to do this please. Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command Button and macro
Hi,
You could get rid of the Command Button and add a change event to the sheet: Suppose your combo box puts its results in cell A1 then Private Sub Worksheet_Change(ByVal Target As Range) Dim Isect As Range Set Isect = Application.Intersect(Target, [A1]) If Not Isect Is Nothing Then Sheets(Target).Activate End If End Sub Here a number is being entered into A1 and then it is added to the entry in B1 and then A1 is cleared. 1. To add this code to your file, press Alt+F11, 2. In the VBAProject window, top left side, find your sheet name under your file name and double click it. 3. Paste in or type the code above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Bob" wrote: I have a command button and combobox on sheet1. The combobox is a list of the sheets by known name ie "Sales " is sheet3 "Expenses" is sheet9 etc. The known name is at cell A4 on the relevant spreadsheet. So if someone picks Sales from the combobox and then presses the command button i want to go to that sheet. What is the best way to do this please. Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command Button and macro
On 11 June, 18:06, Shane Devenshire
wrote: Hi, You could get rid of the Command Button and add a change event to the sheet: Suppose your combo box puts its results in cell A1 then Private Sub Worksheet_Change(ByVal Target As Range) * * Dim Isect As Range * * Set Isect = Application.Intersect(Target, [A1]) * * If Not Isect Is Nothing Then * * * * Sheets(Target).Activate * * End If End Sub Here a number is being entered into A1 and then it is added to the entry in B1 and then A1 is cleared. 1. To add this code to your file, press Alt+F11, * 2. *In the VBAProject window, top left side, find your sheet name under your file name and double click it. 3. *Paste in or type the code above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Bob" wrote: I have a command button and combobox on sheet1. The combobox is a list of the sheets by known name ie "Sales " is sheet3 "Expenses" is sheet9 etc. The known name is at cell A4 on the relevant spreadsheet. So if someone picks Sales from the combobox and then presses the command button i want to go to that sheet. What is the best way to do this please. Bob- Hide quoted text - - Show quoted text - Thank you for your comments but i really want to create a simple index / lookup as detailed above. Perhaps i have posted to the wrong group |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command Button and macro
code for cmmandbutton could be:
Private Sub CommandButton1_Click() Worksheets(Range("A4").Value).Activate End Sub "Bob" wrote in message ... On 11 June, 18:06, Shane Devenshire wrote: Hi, You could get rid of the Command Button and add a change event to the sheet: Suppose your combo box puts its results in cell A1 then Private Sub Worksheet_Change(ByVal Target As Range) Dim Isect As Range Set Isect = Application.Intersect(Target, [A1]) If Not Isect Is Nothing Then Sheets(Target).Activate End If End Sub Here a number is being entered into A1 and then it is added to the entry in B1 and then A1 is cleared. 1. To add this code to your file, press Alt+F11, 2. In the VBAProject window, top left side, find your sheet name under your file name and double click it. 3. Paste in or type the code above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Bob" wrote: I have a command button and combobox on sheet1. The combobox is a list of the sheets by known name ie "Sales " is sheet3 "Expenses" is sheet9 etc. The known name is at cell A4 on the relevant spreadsheet. So if someone picks Sales from the combobox and then presses the command button i want to go to that sheet. What is the best way to do this please. Bob- Hide quoted text - - Show quoted text - Thank you for your comments but i really want to create a simple index / lookup as detailed above. Perhaps i have posted to the wrong group |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Command Button and macro
On 11 June, 21:18, "Homey" <none wrote:
code for cmmandbutton could be: Private Sub CommandButton1_Click() * * Worksheets(Range("A4").Value).Activate End Sub "Bob" wrote in message ... On 11 June, 18:06, Shane Devenshire wrote: Hi, You could get rid of the Command Button and add a change event to the sheet: Suppose your combo box puts its results in cell A1 then Private Sub Worksheet_Change(ByVal Target As Range) Dim Isect As Range Set Isect = Application.Intersect(Target, [A1]) If Not Isect Is Nothing Then Sheets(Target).Activate End If End Sub Here a number is being entered into A1 and then it is added to the entry in B1 and then A1 is cleared. 1. To add this code to your file, press Alt+F11, 2. In the VBAProject window, top left side, find your sheet name under your file name and double click it. 3. Paste in or type the code above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Bob" wrote: I have a command button and combobox on sheet1. The combobox is a list of the sheets by known name ie "Sales " is sheet3 "Expenses" is sheet9 etc. The known name is at cell A4 on the relevant spreadsheet. So if someone picks Sales from the combobox and then presses the command button i want to go to that sheet. What is the best way to do this please. Bob- Hide quoted text - - Show quoted text - Thank you for your comments but i really want to create a simple index / lookup as detailed above. Perhaps i have posted to the wrong group- Hide quoted text - - Show quoted text - This produces an error of "Subscript out of range", do i not have to use "combobox1" somewhere in the code as that holds the name to match to A4. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run a macro using a command button | Excel Discussion (Misc queries) | |||
How do you remove a macro command button? | Excel Discussion (Misc queries) | |||
Command Button to run a Macro | Excel Worksheet Functions | |||
Run Macro from Command Button | Excel Discussion (Misc queries) | |||
Assigning a macro to a command button | Excel Discussion (Misc queries) |