Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
Hidden in cells to the far right of row1 are 12 variables. Some are
used to set the condition of CommanBar Controls when a worksheet event occurs; others are displayed as a value on a worksheet properties userform. This worked well until recently when a user deleted row1. I did think that any text written in the Sheet Module would be detected when the workbook was opened if the Macro Security Level was set above the lowest level. I was surprised to find that I could save the workbook with a variable name and value, shCondition = "formatted" or line number 1 = "formatted", and open the workbook without tripping the Macro Security. This may be completely useless, but is there perhaps a method that can be used to read and write variables in a Sheet Module? Thanks as always for your help Regards Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
Phil,
You could use a hidden worksheet, or used workbook names (which also can be hidden). Not foolproof, but more secure than you currently have. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... Hidden in cells to the far right of row1 are 12 variables. Some are used to set the condition of CommanBar Controls when a worksheet event occurs; others are displayed as a value on a worksheet properties userform. This worked well until recently when a user deleted row1. I did think that any text written in the Sheet Module would be detected when the workbook was opened if the Macro Security Level was set above the lowest level. I was surprised to find that I could save the workbook with a variable name and value, shCondition = "formatted" or line number 1 = "formatted", and open the workbook without tripping the Macro Security. This may be completely useless, but is there perhaps a method that can be used to read and write variables in a Sheet Module? Thanks as always for your help Regards Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
You could define your variables as names within the workbook. Their
values will (I think) be preserved on saving the file. Tim. wrote in message ups.com... Hidden in cells to the far right of row1 are 12 variables. Some are used to set the condition of CommanBar Controls when a worksheet event occurs; others are displayed as a value on a worksheet properties userform. This worked well until recently when a user deleted row1. I did think that any text written in the Sheet Module would be detected when the workbook was opened if the Macro Security Level was set above the lowest level. I was surprised to find that I could save the workbook with a variable name and value, shCondition = "formatted" or line number 1 = "formatted", and open the workbook without tripping the Macro Security. This may be completely useless, but is there perhaps a method that can be used to read and write variables in a Sheet Module? Thanks as always for your help Regards Phil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
Hi Bob and Tim, a Happy New Year to you both.
I did use the hidden worksheet method in an early stage of development, but as the program advanced I ran into problems. As to using workbook names, I didn't realise that the values were preserved. I can certainly see how this could be used to replace the missing values if a user deleted row 1 and it raises other possibilities. I will investigate further. Do you think the idea of using the Sheet Module is impracticable and not worth pursuing? Phil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
Phil,
No I am not aware of any problems with hidden sheet modules. In many ways, certainly in VBA, they are a bit easier to use than workbook names, but I would be interested to hear what problems that you encountered. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... Hi Bob and Tim, a Happy New Year to you both. I did use the hidden worksheet method in an early stage of development, but as the program advanced I ran into problems. As to using workbook names, I didn't realise that the values were preserved. I can certainly see how this could be used to replace the missing values if a user deleted row 1 and it raises other possibilities. I will investigate further. Do you think the idea of using the Sheet Module is impracticable and not worth pursuing? Phil |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
Hi Bob,
The add-in I have developed compiles text data files retrieved from a mainframe into a report. Each workbook will normally contain between two and six worksheets. Row1 cells 100 to 106 holds information from the original file header (date the query was run - reference number of the query - date period requested - etc.) This data is picked up by a Worksheet Properties Userform Row1 107-112 holds information about the type of code process that were used to compile the report, and the condition of the worksheet (so for example Cell 107 can be one of four values - WTT, SPT, DSN or ISW. Cell 108 - "Formatted" or "Unformatted") this data is referenced when a WindowActivate, SheetActivate or WorkbookClose event occurs to set the condition of CommandBar Controls. As the user moves between workbooks or worksheets the state of the controls on the CommandBar reflex actions the user can make with each sheet. If the sheet is in an "Unformatted" condition then Controls(8).Enabled = TRUE, 'Format Control Controls(9).Enabled = FALSE. 'Un-format Control ActiveSheet.ProtectContents = TRUE. These conditions are reversed when the sheet is "Formatted" and the value in Cell 108 is changed to show the current state of the sheet. I did try using a hidden worksheet to hold this information. Row1 of the hidden sheet referring to Sheet1, Row2 to Sheet2 etc. This worked okay when the workbook was compiled from code but I ran into difficulties if a user decided, for example, to add sheets for another workbook. I decided that the information needed to be on (or in) the worksheet and could therefore move with the worksheet. This has worked well until a user wanted to change the Print Titles and deleted Row1. As it appears than it might be possible to read and write text in the worksheet code module without tripping the Macro Security, could this be a more secure location? The user could do what ever they wished to the worksheet and it wouldn't affect the values held in the sheets code module. The problem is that I don't know of a method I could use, or indeed if it is practicable. When an event occurs the code would need to read the variable in the ActiveSheet Code Module, act upon the value and write back any change. Thank you for taking the time to help Regards Phil Bob Phillips wrote: Phil, No I am not aware of any problems with hidden sheet modules. In many ways, certainly in VBA, they are a bit easier to use than workbook names, but I would be interested to hear what problems that you encountered. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... Hi Bob and Tim, a Happy New Year to you both. I did use the hidden worksheet method in an early stage of development, but as the program advanced I ran into problems. As to using workbook names, I didn't realise that the values were preserved. I can certainly see how this could be used to replace the missing values if a user deleted row 1 and it raises other possibilities. I will investigate further. Do you think the idea of using the Sheet Module is impracticable and not worth pursuing? Phil |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables Held in Sheet Module
I believe Bob was talking about the hidden sheet method, but for doing what
you describe, see Chip Pearson's site about coding with code. http://www.cpearson.com/excel/vbe.htm just note that the user can still access the sheet code module, but it would have to be an overt act rather than your current problem. also, it would cause macros to exist in your workbook causing the virus alert and in later versions of excel, there is a security permission that allows code acess to the vbe. -- Regards, Tom Ogilvy wrote in message oups.com... Hi Bob, The add-in I have developed compiles text data files retrieved from a mainframe into a report. Each workbook will normally contain between two and six worksheets. Row1 cells 100 to 106 holds information from the original file header (date the query was run - reference number of the query - date period requested - etc.) This data is picked up by a Worksheet Properties Userform Row1 107-112 holds information about the type of code process that were used to compile the report, and the condition of the worksheet (so for example Cell 107 can be one of four values - WTT, SPT, DSN or ISW. Cell 108 - "Formatted" or "Unformatted") this data is referenced when a WindowActivate, SheetActivate or WorkbookClose event occurs to set the condition of CommandBar Controls. As the user moves between workbooks or worksheets the state of the controls on the CommandBar reflex actions the user can make with each sheet. If the sheet is in an "Unformatted" condition then Controls(8).Enabled = TRUE, 'Format Control Controls(9).Enabled = FALSE. 'Un-format Control ActiveSheet.ProtectContents = TRUE. These conditions are reversed when the sheet is "Formatted" and the value in Cell 108 is changed to show the current state of the sheet. I did try using a hidden worksheet to hold this information. Row1 of the hidden sheet referring to Sheet1, Row2 to Sheet2 etc. This worked okay when the workbook was compiled from code but I ran into difficulties if a user decided, for example, to add sheets for another workbook. I decided that the information needed to be on (or in) the worksheet and could therefore move with the worksheet. This has worked well until a user wanted to change the Print Titles and deleted Row1. As it appears than it might be possible to read and write text in the worksheet code module without tripping the Macro Security, could this be a more secure location? The user could do what ever they wished to the worksheet and it wouldn't affect the values held in the sheets code module. The problem is that I don't know of a method I could use, or indeed if it is practicable. When an event occurs the code would need to read the variable in the ActiveSheet Code Module, act upon the value and write back any change. Thank you for taking the time to help Regards Phil Bob Phillips wrote: Phil, No I am not aware of any problems with hidden sheet modules. In many ways, certainly in VBA, they are a bit easier to use than workbook names, but I would be interested to hear what problems that you encountered. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... Hi Bob and Tim, a Happy New Year to you both. I did use the hidden worksheet method in an early stage of development, but as the program advanced I ran into problems. As to using workbook names, I didn't realise that the values were preserved. I can certainly see how this could be used to replace the missing values if a user deleted row 1 and it raises other possibilities. I will investigate further. Do you think the idea of using the Sheet Module is impracticable and not worth pursuing? Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables in Module vs. Public | Excel Discussion (Misc queries) | |||
Create Global Variables in a module | Excel Programming | |||
4 variables to one sheet, then back to original sheet with answer | Excel Programming | |||
Passing variables from module to userform | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |