Codename passing
Set wSh = sheets("myCodeName")
"Nigel" wrote: 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 -- Regards, Nigel |
Codename passing
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" wrote in message ... 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 -- Regards, Nigel |
Codename passing
OK thanks, got it!
I need to scan all sheets to find the codename which IDs the relevant sheet -- Regards, Nigel "Peter T" <peter_t@discussions wrote in message ... 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" wrote in message ... 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 -- Regards, Nigel |
All times are GMT +1. The time now is 11:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com