Command Button and macro

[Bob]

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

 

[Shane Devenshire]

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.

 

[Bob]

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 underyour
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

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

 

[Homey]

code for cmmandbutton could be:

Private Sub CommandButton1_Click()
Worksheets(Range("A4").Value).Activate
End Sub

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

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

 

[Bob]

code for cmmandbutton could be:

Private Sub CommandButton1_Click()
    Worksheets(Range("A4").Value).Activate
End Sub

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.

Cheers,
Shane Devenshire

- 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.