Re-defining a drop down object with VBA

[braindiesel]

I believe this is an easy one. I just need a little help.

In a macro that chooses year 1, 2, or 3, I have the following code

Sheets("Dashboard").Shapes("Drop Down 36").ControlFormat.ListFillRange = "=myYear1"
Sheets("Dashboard").Shapes("Drop Down 37").ControlFormat.ListFillRange = "=myYear1"

Which works perfectly to define my list range.

The Cell Link looks should look at 'Year 1'!$H$1, or 'Year 2'!$H$1 or the same for year 3.

Can you help me with the code to declare that?

 

[braindiesel]

Never mind. Solved it.

I used the following lines

Sheets("Dashboard").Shapes("Drop Down 36").ControlFormat.LinkedCell = "=myYear1From"
Sheets("Dashboard").Shapes("Drop Down 37").ControlFormat.LinkedCell = "=myYear1To"

and just renamed the relevant cells.

I had tried not naming the cells by using
Sheets("Dashboard").Shapes("Drop Down 37").ControlFormat.LinkedCell = Sheets("sheetName").range("H1").address
but the result was $H$1, not sheetName!$H$1 as was needed

If someone can resolve how to make the sheet name stick, I'd be interested