Storing flag values at worksheet level (Property or Properties)

[bstobart]

In a workbook with a number of worksheets, I have some VBA code that copies
certain worksheets (and then hides the old versions of those that were
copied). I've been playing around with different methods of designating
which worksheets should be copied. It would be helpful if I could simply
attach a boolean flag at the worksheet level to all the worksheets,
designating which are to be copied and which aren't. I also have a second
such need for associating a flag at the worksheet level.

Would it make sense to use a Public Property or a new user-defined member of
the Properties collection? If so, can somoene point me to a good example?

 

[Bill Renaud]

Try adding a new worksheet at the front of the workbook (say "List of
Sheets"), then list each worksheet in column $A and put the flag in
column $B. You can add all the columns you want for other flag purposes.

 

[bstobart]

That's a perfectly reasonable response, but I'd like to avoid creating yet
another worksheet if I can. I'm curious about the idea of creating user
defined properties (flags) at the worksheet level. This would be a very
elegant way to solve my problem. [Perhaps I should have said that I don't
expect the user to ever change these settings.]

 

[Bill Renaud]

Who and when do you decide which worksheets should be copied? Does the
user do this somehow before starting your macro, or does your macro
decide this internally somehow? If internally, then I guesss you would
simply put the flags into an array variable.

 

[bstobart]

The set of worksheets to be copied is fixed. The user does not specify them.
I could use an array or other mechanism, but I'd prefer to simply set a
variable at the source. This would seem the more OO way to do things, and it
seems most compatible with my code as well.

 

[Bill Renaud]

Maybe try using sheet level names. Use the Define Names dialog box to
set each name to TRUE or FALSE.
Caveat: That respective worksheet has to be the active sheet to add the
name for that sheet.

For example:

Name Refers to:
---------------- ----------
Sheet1!PrintFlag TRUE
Sheet2!PrintFlag FALSE
Sheet3!PrintFlag TRUE

and a quick routine to show how to display them:

Public Sub ShowSheetLevelNames()
Dim ws As Worksheet
Dim strSheetLevelName As String
Dim blnRefersTo As Boolean

For Each ws In Worksheets
strSheetLevelName = "'" & ws.Name & "'!PrintFlag"

'Strip the "=" off of the front of the string.
blnRefersTo = Replace(Names(strSheetLevelName).RefersTo, Find:="=",
Replace:="", Start:=1, Compare:=vbTextCompare)

If blnRefersTo _
Then
MsgBox strSheetLevelName & " is " _
& Names(strSheetLevelName) & ".", _
vbInformation + vbOKOnly, "Sheet Level Name is TRUE"
Else
MsgBox strSheetLevelName & " is " _
& Names(strSheetLevelName) & ".", _
vbInformation + vbOKOnly, "Sheet Level Name is FALSE"
End If
Next ws
End Sub

Final question: If you have chart sheets that also need to be printed,
how will you use this technique? (I think you would be forced to return
to the original idea of a separate list of sheets, which I have used in
my own work. You would have to include a property in your worksheet
class that would simply call a property in a class designated to return
the value of the flag for that sheet.)