Codename passing

[Nigel]

Hi All
I have the codenames for various worksheets stored as strings in another
worksheet. How can I assign each sheet to an object?

e.g. If I have the string "myCodeName" which is the codename for one of the
worksheets, and want to assign it to a worksheet object

Dim wSh as Worksheet
Set wSh = ??

TIA

 

[Joel]

Set wSh = sheets("myCodeName")

 

[Peter T]

Ah, your other post makes more sense now. You need to loop sheets to find
it.

Try the following, rename your sheet that has the codename Sheet2

Sub test()
Dim res As Long
Dim s$
Dim ws As Worksheet
s = "Sheet2" ' the codename

res = WsFromCodeName(s, ActiveWorkbook, ws)
If res = 1 Then
MsgBox ws.Name
ElseIf res = 2 Then
MsgBox "can't return all codenames"
Else
MsgBox s & " not found"
End If

End Sub

Function WsFromCodeName(ByVal sCodeName As String, _
ByVal wb As Workbook, _
ByRef ws As Worksheet) As Long
Dim s As String
For Each ws In wb.Worksheets
s = ws.CodeName
If s = "" Then
' sheet inserted since last saved
WsFromCodeName = 2
ElseIf s = sCodeName Then
WsFromCodeName = 1
Exit For
End If
Next

End Function

Regards,
Peter T

 

[Nigel]

OK thanks, got it!

I need to scan all sheets to find the codename which IDs the relevant sheet

--

Regards,
Nigel
(e-mail address removed)