Dynamically access a worksheet by its sheet number / index NOT its name in VBA

[awecomms]

I am trying to parse a simple statement but cant seem to work it out.
I have a list of stakeholders, upto 10. I enter their names into a list and I need VBA to rename a hidden sheet to that Stakeholder's name then unhide it. I have 10 hidden sheets, labelled "Stakeholder1" ... "Stakeholder10, with sheet indexes of Sheet11 to Sheet20. Because each one is tied to a cell I want to "link" the VBA to reference Sheet11 when "Stakeholder1"'s cell is edited. I can do it by sheet name but I have to track the name and if some butthead enters the name "Stakeholder3" in the cell for Stakeholder 5, everything goes to heck in a wicker basket.

My problem to date with searching for answers is I either can't explain it properly in my search text or Google is "helping" by "understanding" what I was "really" looking for.

 

[rhratterman15]

Can I see your current code and a sample of the workbook? Could you possibly upload the workbook using the XL2BB function?

 

[awecomms]

I am trying to parse a simple statement but cant seem to work it out.
I have a list of stakeholders, upto 10. I enter their names into a list and I need VBA to rename a hidden sheet to that Stakeholder's name then unhide it. I have 10 hidden sheets, labelled "Stakeholder1" ... "Stakeholder10, with sheet indexes of Sheet11 to Sheet20. Because each one is tied to a cell I want to "link" the VBA to reference Sheet11 when "Stakeholder1"'s cell is edited. I can do it by sheet name but I have to track the name and if some butthead enters the name "Stakeholder3" in the cell for Stakeholder 5, everything goes to heck in a wicker basket.

My problem to date with searching for answers is I either can't explain it properly in my search text or Google is "helping" by "understanding" what I was "really" looking for.

Turns out, I am wanting to access a worksheet by codename, something I am getting the feeling is not possible in the simplistic way I was hoping.
I was trying something like sh = Sheet(sheetnum) where sheetnum is the numeric component of the worksheet's codename.

 

[StephenCrump]

Turns out, I am wanting to access a worksheet by codename, something I am getting the feeling is not possible in the simplistic way I was hoping.
I was trying something like sh = Sheet(sheetnum) where sheetnum is the numeric component of the worksheet's codename.

Correct, it's not as easy as referring to sheet name, where you can, for example:

Set sh = Worksheets("Sheet" & i)

But there are workarounds, e.g. assuming codenames Sheet11, Sheet12 ... Sheet20, you can, for example:

Dim v As Variant
Dim i As Long

v = Array(Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, Sheet20)

i = 3
v(i).Range("A1").Value = "Hello"

 

[awecomms]

ended up getting ugly with it

For Each sh In ThisWorkbook.Worksheets
If sh.CodeName = cursht Then
With sh
.Name = sheetname
.Visible = True
End With
End If
If Left(sh.Name, 11) = "Stakeholder" Then
If Left(sh.Name, 12) <> "Stakeholders" Then
sh.Visible = False
End If
End If
Next sh

But I LOVE the array idea as I know the stakeholders number and codenames never change

Genius!

This would also work with non contiguous sheets too.

Off to simplify some code.