Refer to Hidden Sheet from VBA?

[Larry Adams]

I have a spreadsheet in which I have a number of spreadsheet tabs that I
use to store control information for VBA macros -- that I would prefer
to hide from the end user. However, when I hide any of these sheets, my
VBA code can no longer reference and it is almost as though these sheets
had simply been deleted altogether. Unhiding the sheets gets the macros
working again. And it is literally as simple as that.

Any thoughts on how I can hide these sheets and still have macros able
to reference?

Or is this simply an Excel limitation? I am presently working with
Excel 97. Thanks.

 

[Haldun Alay]

Hi,

Can you post some codes to understand how you get the informations from sheets?

--
Regards

Haldun Alay

To e-mail me, please replace AT and DOT in my e-mail address with the original signs.



"Larry Adams" <[email protected]>, iletide sunu yazdi I have a spreadsheet in which I have a number of spreadsheet tabs that I
use to store control information for VBA macros -- that I would prefer
to hide from the end user. However, when I hide any of these sheets, my
VBA code can no longer reference and it is almost as though these sheets
had simply been deleted altogether. Unhiding the sheets gets the macros
working again. And it is literally as simple as that.

Any thoughts on how I can hide these sheets and still have macros able
to reference?

Or is this simply an Excel limitation? I am presently working with
Excel 97. Thanks.

 

[Robert McCurdy]

Whether the sheets are hidden or not, shouldn't prevent you from returning information from them or adding data to them Larry.
Supposing sheet1 was veryhidden - this should still run.

Sub Hiddenornot()
[sheet1!A1].Value = Format(Date, "d ddd mmm yyyy")
MsgBox [sheet1!A1]
End Sub


Regards Robert

 

[Steve Hieb]

Larry,

I'm not aware of any Excel limitation. My guess is you're trying to
Select the hidden worksheet. Example: If Sheet1 is hidden, something
like this will cause an error.

Sheet1.Select
Range("A1") = 1

Instead use:

Sheet1.Range("A1") = 1
or
Worksheets("Sheet1").Range("A1") = 1

I prefer the first syntax so the code doesn't need to change if the
sheet tab name changes. Example: if the tab name for "Sheet1" is
changed to "First Sheet", then Sheet1.Range("A1") would still work but
Worksheets("Sheet1") would need to be changed to Worksheets("First
Sheet").

Very seldom do you ever need to select or activate the object to work
with it. If you use the macro recorder though, it will often select
everything because that is what you're doing while recording. Result
is hard to read and unecessary code.

Just a guess at what might be causing the problems. If no help, try
posting some of the problem code.

HTH,
Steve Hieb

 

[COM]

I agree with the message posted below, however if you are
still having problems with the code, then you can code in
the program to unhide the sheet, do the work you need then
hide it again... Agree also that the problematic code
should be shown so it can be debugged.