PDA

View Full Version : Referencing worksheet name using code name



Salvo
11-17-2009, 06:58 PM
I would like to be able to reference several worksheets (the number and code name determined at run time) using their code name such as sheet5. How do I do this with code to account for the fact that this code name needs to be a variable. Thus I need to hide various sheets but their sheet name and sheet position is variable; the only thing that I know for sure is their code name. Thus I am after something like this.

for each sheet
(sheet5 as a variable).visible=false
next sheet


How do I achieve this?:hi:

RolfJ
11-17-2009, 07:24 PM
Hi Salvo:

I am not quite sure whether I follow you, but may be this code example will point you in the right direction:


Dim sh() As Worksheet
ReDim sh(0)
Set sh(0) = Sheets("Sheet1")
ReDim Preserve sh(1)
Set sh(1) = Sheets("Sheet2")
Dim s As Variant
For Each s In sh
Debug.Print s.Name
Next s

Salvo
11-17-2009, 07:35 PM
To clarify further i have 2 columns in part of my spreadsheet such as

12 1
6 0
5 0
7 1
5 0


I then look at first column which refers to the sheet code name and the second column refers to whether sheet is visible or not.
so first row means make sheet12 visible, second row mean make sheet6 not visible and so on. I have numerous sheets in workbook and i can not use index names or sheet names as order of sheets may alter and sheets may get renamed.

RolfJ
11-17-2009, 08:14 PM
I am still a bit confused: exactly which worksheets are you referring to with 'Sheet12' or 'Sheet6' in your example?

Salvo
11-17-2009, 08:48 PM
Rolf J - To clarify further sheet12 = "calculation 4" , sheet6 = "calculation 7". They are just 2 of many worksheets.

Rolf J - In reference to your first reply - i could use your proposal but with 30 or so sheets I need to have 30 or so lines of code (1 for each sheet). What i am asking is there a way to automate this so that i have less than 10 lines of code regardless of whether i am dealing with 30 sheets or 100 sheets?

geekgirlau
11-17-2009, 10:08 PM
Let's go back a step.

"Sheet6" is a really bad name - it's always a good idea to change the sheet name in the VBE window so that it actually means something.

Now you are referring to setting this at runtime based on 2 columns in a particular sheet. What determines whether the sheet is visible? Is this a calculation or a hard-coded value?

RolfJ
11-17-2009, 10:36 PM
Hi Salvo:

Now you are really about to lose me! It would help if you were to try to explain how you index your worksheets since you insist on not using the index your workbook uses intrinsically.

Jan Karel Pieterse
11-18-2009, 12:51 AM
You might use a function like this:


Public Function GetWorksheetFromCodeName(sShtCodeName) As Worksheet
'-------------------------------------------------------------------------
' Procedure : GetWorksheetFromCodeName
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (www.jkp-ads.com (http://www.jkp-ads.com))
' Created : 15-10-2009
' Purpose : Returns the sheet object belonging to the codename passed.
'-------------------------------------------------------------------------
Dim oSh As Object
On Error Resume Next
For Each oSh In ThisWorkbook.Worksheets
If oSh.CodeName = sShtCodeName Then
Set GetWorksheetFromCodeName = oSh
Exit Function
End If
Next
End Function


Use the function as follows:

Dim oSht as Worksheet
Dim sName As String
sName="Sheet" & Range("A2")
Set oSht=GetWorksheetFromCodename(sName)