VBA IF cell in a specific sheet is equal to a number, the go to a specific sheet

[nickthebizz]

Hi, my skills in VBA is at beginning level.

I have a sheet where I have my clients names written. I have made a button and I would like this button to take me to a specific sheet when each client is selected. I have manage to make a list where when i choose a client it returns a number in a cell starting from 1 and on in my clients sheet.

So if this number is equal to 1 I need the button to take me to a specific sheet which is for that specific client. If the cell is equal to 2 to another sheet etc.

Could anyone give me an insight please

thank you

Nik

 

[My Aswer Is This]

We need specifics:
Like what is the name of the sheet with your button?
And you said:
So if this number is equal to 1 I need the button to take me to a specific sheet
so if number is 1 take me to what sheet?

Why not double click on "Alpha" to be taken to the sheet name "Alpha"
Would this not work?
What column on your sheet is these client names.

 

[nickthebizz]

1. Name of Sheet with Button is "Main".
2. If number is equal to 1 go to sheet "Shoretanks"
3. Client names is in sheet "Clients" where the number changes in cell A1 and names are in column B.

Reason I do not use links is is that I wish to work on this code to also add in a print with another button later on

thank you

 

[My Aswer Is This]

I do not see logic here:
You said:
If number is equal to 1 go to sheet "Shoretanks"
I do not see how 1 equals Shoretanks

 

[nickthebizz]

yes the cell A1 in clients sheets has value of 1

 

[My Aswer Is This]

yes the cell A1 in clients sheets has value of 1

So what happens if 2 is entered in Range("A1")

 

[nickthebizz]

So what happens if 2 is entered in Range("A1")

It will go to sheets("shoretanks2")

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  3/19/2021  9:34:57 AM  EDT
On Error GoTo M
Dim ans As Long
ans = Range("A1").Value
Sheets("shoretanks" & ans).Activate
Exit Sub
M:
MsgBox "Sheet named" & vbNewLine & "shoretanks" & ans & vbNewLine & "Does not exist", , "Oops"
End Sub

 

[nickthebizz]

Works as I wanted! thank you.

One last question.

Can i make
ans = Range("A1").value

to reference to A1 in a different sheet where the button is.

Something like

ans = Sheets("Calculations").Range("A1").value

?

 

[My Aswer Is This]

Works as I wanted! thank you.

One last question.

Can i make
ans = Range("A1").value

to reference to A1 in a different sheet where the button is.

Something like

ans = Sheets("Calculations").Range("A1").value

?

So it looks like you have the solution
But I thought in post 3 you said button was in sheet named "Main:
Your quote in post 3
Name of Sheet with Button is "Main"

Or all you putting buttons in more then one sheet?

If so write code like this: ans=Activesheet.name