Refer to Hidden Sheet from VBA?
 

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.

Refer to Hidden Sheet from VBA?
 

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" , 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.

Refer to Hidden Sheet from VBA?
 

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

"Larry Adams" wrote in message ...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003

Refer to Hidden Sheet from VBA?
 

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

Refer to Hidden Sheet from VBA?
 

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.


-----Original Message-----
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
.