Referencing to selected value in a combo box- Excel

[kajobkajob2]

Hi people
I was wondering if you could help me with this.

I have a drop down list/ combo box in Excel. I made it by changing the validation properties of that cell.
Could someone tell me how to refer to the selected text in that combo box using VBA as i'm trying to make something similar to this forum's "forum jump".
The values are on a list on another sheet, they are January, February etc...

Basically, Could someone tell me what the VBA code would be to assign to an object so that the following happens:
When the button is clicked,Excel looks at what is in the combo box, it then sgoes to the work sheet with the name that is the same as what was selected in combo box.

Thanks in advance!!

 

[Isskint]

I do not think you can get at the data validation drop down through VBA. However, what you can do is set some code in the worksheet_SelectionChange event. Check the cell that triggered the event is where your data validation is, then use the value in the cell to open another sheet.

So cell A1 has the validation data
User clicks on it and selects January

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Sheets(Target.Value).Activate
End If
End Sub

 

[kajobkajob2]

I do not think you can get at the data validation drop down through VBA. However, what you can do is set some code in the worksheet_SelectionChange event. Check the cell that triggered the event is where your data validation is, then use the value in the cell to open another sheet.

So cell A1 has the validation data
User clicks on it and selects January

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Sheets(Target.Value).Activate
End If
End Sub

so all I have to change to this code is the "$A$1" right, coz i did that and it doesn't go to the selected sheet

 

[Isskint]

Yes, change $A$1 to your cell. Use the $ $ also

Make sure the code is in the correct worksheet.

 

[kajobkajob2]

I was wondering if I could Assign something like macro this to a picture?

Sub GoToSheet()
Dim range As range
range = (J3)
Sheets(range.Value).Activate
End Sub

I have tried it before and there is a problem with the line where i set the value of my variable. can someone tell what i've done wrong?

 

[Isskint]

Yes this also can be done. No need for variable declaration either.

Sub GoToSheet()
Sheets("J3").Activate
End Sub

All you need do is put the picture on the sheet, right click>>Assign Macro and select GoToSheet

 

[kajobkajob2]

Also maybe someone could fix this to make it work.
Once again I will assign it to a button

Sub GoToSheet()


Sheets(ActiveWorkbook.Worksheets(Profits.Cell(J3)).Value).Activate
End Sub

 

[kajobkajob2]

Yes this also can be done. No need for variable declaration either.

Sub GoToSheet()
Sheets("J3").Activate
End Sub

All you need do is put the picture on the sheet, right click>>Assign Macro and select GoToSheet

what does "subscript out of range" mean?
I got that when I put in the code:

Sub GoToSheet()
Sheets("J3").Activate
End Sub

 

[kajobkajob2]

thx for help and support guys!
I solvewd my prob by assigning this code to a button


Sub GoToSheet()
Sheets(range("J3").Value).Activate
End Sub