VBA to jump the Sheet on base of cell value

[abmati]

Hi Excel VBA Experts,
I have created a VBA to jump the Sheet on base of cell value.
But it does not work, can someone help me?
I have a Menu Sheet where a value has stores in B1 cell (Between 1 and 8)
If I click a Text Box link to this Macro, on the base of this value, need to go that Sheet.
Sub Menu_Sheet()
ThisWorkbook.Sheets("MENU").Select
Dim myOp As Integer
myOp = Range("B1").Value
If myOp = 1 Then ThisWorkbook.Sheets("Daily Data").Select
If myOp = 2 Then ThisWorkbook.Sheets("QNB Nexus").Select
If myOp = 3 Then ThisWorkbook.Sheets("Salary").Select
If myOp = 4 Then ThisWorkbook.Sheets("T Summary").Select
If myOp = 5 Then ThisWorkbook.Sheets("Muhsin").Select
If myOp = 6 Then ThisWorkbook.Sheets("Sponsor Fees").Select
If myOp = 7 Then ThisWorkbook.Sheets("S Summary").Select
If myOp = 8 Then ThisWorkbook.Sheets("Hassan Al Hilal").Select
End Sub

Thanks and Regards

 

[mumps]

Try:

Sub Menu_Sheet()
    Application.ScreenUpdating = False
    Select Case Sheets("MENU").Range("B1").Value
        Case Is = 1
            Sheets("Daily Data").Select
        Case Is = 2
            Sheets("QNB Nexus").Select
        Case Is = 3
            Sheets("Salary").Select
        Case Is = 4
            Sheets("T Summary").Select
        Case Is = 5
            Sheets("Muhsin").Select
        Case Is = 6
            Sheets("Sponsor Fees").Select
        Case Is = 7
            Sheets("S Summary").Select
        Case Is = 8
            Sheets("Hassan Al Hilal").Select
    End Select
    Application.ScreenUpdating = True
End Sub

 

[cooper645]

or this

Sub Menu_Sheet()Sheets("MENU").Select
Dim myOp As Integer
myOp = Range("B1").Value
If myOp = 1 Then Sheets("Daily Data").Select
If myOp = 2 Then Sheets("QNB Nexus").Select
If myOp = 3 Then Sheets("Salary").Select
If myOp = 4 Then Sheets("T Summary").Select
If myOp = 5 Then Sheets("Muhsin").Select
If myOp = 6 Then Sheets("Sponsor Fees").Select
If myOp = 7 Then Sheets("S Summary").Select
If myOp = 8 Then Sheets("Hassan Al Hilal").Select
End Sub

 

[My Aswer Is This]

Your script works for me. Check your sheet names.

Hi Excel VBA Experts,
I have created a VBA to jump the Sheet on base of cell value.
But it does not work, can someone help me?
I have a Menu Sheet where a value has stores in B1 cell (Between 1 and 8)
If I click a Text Box link to this Macro, on the base of this value, need to go that Sheet.
Sub Menu_Sheet()
ThisWorkbook.Sheets("MENU").Select
Dim myOp As Integer
myOp = Range("B1").Value
If myOp = 1 Then ThisWorkbook.Sheets("Daily Data").Select
If myOp = 2 Then ThisWorkbook.Sheets("QNB Nexus").Select
If myOp = 3 Then ThisWorkbook.Sheets("Salary").Select
If myOp = 4 Then ThisWorkbook.Sheets("T Summary").Select
If myOp = 5 Then ThisWorkbook.Sheets("Muhsin").Select
If myOp = 6 Then ThisWorkbook.Sheets("Sponsor Fees").Select
If myOp = 7 Then ThisWorkbook.Sheets("S Summary").Select
If myOp = 8 Then ThisWorkbook.Sheets("Hassan Al Hilal").Select
End Sub

Thanks and Regards

 

[abmati]

Sir, thanks for your reply, but sorry to say even any number in cell B, it goes to "Daily Data" Sheet (Is=1).
Please note that the value of cell B1 comes from the "Option Button (Form Control)".

 

[My Aswer Is This]

Not sure who your talking to. There are 3 or 4 differerent people here providing answers.
So are you saying none of theses scripts work for you.

Sir, thanks for your reply, but sorry to say even any number in cell B, it goes to "Daily Data" Sheet (Is=1).
Please note that the value of cell B1 comes from the "Option Button (Form Control)".

 

[abmati]

Only 2 persons have replied, Mumps and Cooper645, my question is to both, it does not work.
Even any number in cell B1, it goes to "Daily Data" Sheet (Is=1).
Please note that the value of cell B1 comes from the "Option Button (Form Control)".

 

[MARK858]

What happens if you change

myOp = Range("B1").Value

to

myOp = CLng(Range("B1").Value)

or

Select Case Sheets("MENU").Range("B1").Value

to

Select Case CLng(Sheets("MENU").Range("B1").Value)

 

[My Aswer Is This]

In post 4 I said your script works for me.

And your saying your script does not work for you. So not sure why all three scripts shown here are not working for you.

And I tried Mumps script and it works for me also.

Not sure what this means:

You said:

Please note that the value of cell B1 comes from the "Option Button (Form Control)"

How does a Option button put the value 3 into a range

Show us the script in the option button
​

 

[My Aswer Is This]

I now suspect that you are clicking on your Option button which you have linked to Range("B1")

When you click on the option button the number 1 is being entered into B1

So that would mean only 1 is ever entered into B1.

And you have the script in the option Button

Why are you using a Option Button to run the script?