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

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

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

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

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

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

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