VBA - run time error '13' type mismatch selecting sheet based on named range

[TBD8913]

Hi everyone,

Please can someone help me with the following, apologies if this is quite basic. I've not done any VBA for a long time and I'm out of practice.

In my Excel workbook I have a number of buttons to take you to different sheets in the model, I'm trying to write code that can be assigned to all buttons that pulls through from a range so I can easily update it in future.

Sub Sheet_Tab()
Dim sheetname As Range
 Sheets("Controls").Range("SelectedSheet") = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
 Set sheetname = Sheets("Controls").Range("SheetTabName")
 Sheets(sheetname).Select
End Sub

I'm getting a run time 13 error, when I hover over sheetname, it is pulling through the right range, so I'm a bit confused as to what is causing this.

Thanks in advance.

 

[davesexcel]

What is Range("SelectedSheet")?
What is .Range("SheetTabName")

What line does the error occur?

 

[TBD8913]

Thanks for your reply.

I've fixed the problem by using referring to the sheet number rather than sheet name, in case anyone else has a similar problem in the future.