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

An interesting observation.

I have a suggestion ... instead of hiding the data, hide the worksheet. But, put the data in plain sight along with a warning not to delete it; so that if someone unhides the worksheet, they will know not to mess with it.

Better ... make the worksheet very hidden. That way, they have to know a little something about visual basic (at least they need to open the editor) to unhide the sheet.

Not sure what I mean by "very hidden"? Try this:

Sheet1.visible = xlSheetVeryHidden

Sheet1 vanishes, and in Excel the Unhide command does nothing about it.

You can also make a sheet very hidden by selecting it in the Project Explorer window in VBE and changing the visible property.

To unhide again, you can either do the reverse of the selection in VBE, or

Sheet1.visible = True

- Pete

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)


<[email protected]> wrote in message
news:[email protected]...
> 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
>

You could define your variables as names within the workbook. Their
values will (I think) be preserved on saving the file.

Tim.


<[email protected]> wrote in message
news:[email protected]...
> 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
>

Tom Williams, you're a genius! I never thought of using names like that. But, it works perfectly. The only catch is that, for some reason, the Val function does not work unless you first truncate the = sign off the value.

This is one trick I'm going to remember. THANKS!

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

I guess it's not clear to me what you want to do with the Sheet Module that you can't do any other module? Replace the values in Row 1 every time the sheet is accessed?

Also, I'm perplexed as to why hiding values to the far left of an active worksheet is any simpler than hiding them in a hidden worksheet.

- Pete

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)


<[email protected]> wrote in message
news:[email protected]...
> 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
>

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)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

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


<[email protected]> wrote in message
news:[email protected]...
> 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)
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
>