Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties)
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties)
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. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties
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" wrote: 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. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties
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. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties
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" wrote: 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. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing flag values at worksheet level (Property or Properties
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, Compa=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.) -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW CAN I FLAG A CELL IN EXCEL WHEN IT HITS A REORDER LEVEL? | Excel Worksheet Functions | |||
difference of worksheet name and (name) property in VBA properties | Excel Programming | |||
Storing variable values in Excel worksheet | Excel Programming | |||
Print>Properties>Layout>Pages per sheet: this function/property . | Excel Discussion (Misc queries) | |||
Storing various data in .ID property | Excel Programming |