Store hidden information in a spreadsheet

[Remy]

Hey
Is there a good way to store hidden information (from the user) in a
spreadsheet?
I know that one can use the custom properties, but they are not hidden.
Hidden names seems not exaclty to be the right thing either.
Perfect would be if I could store it relative to a spreadsheet.

Any ideas?

Thanks

Remy Blaettler
www.collaboral.com

 

[Remy]

Oh yeah, hidden cells doesn't look like the right thing either, if you
do a copy/paste they show up, right?

 

[davegb]

Hey
Is there a good way to store hidden information (from the user) in a
spreadsheet?
I know that one can use the custom properties, but they are not hidden.
Hidden names seems not exaclty to be the right thing either.
Perfect would be if I could store it relative to a spreadsheet.

Any ideas?

Thanks

Remy Blaettler
www.collaboral.com

I just hide the columns with the data I don't want the end users to
see.

 

[johnhildreth]

Or put the info on a separate sheet and hide that sheet.

Worksheets("Sheet1").Visible = xlSheetVeryHidden

will require VBA code to make the sheet1 visible again.

HTH,
John

 

[Tom Ogilvy]

Dedicate an entire sheet to your values and hide the sheet using
xlSheetVeryHidden

worksheets("Data").visible = xlSheetVeryHidden

 

[Tom Ogilvy]

Or you can go in the VBE and do it manually.

 

[Remy]

Thanks all to for the help. A hidden sheet is a good idea. Not sure
what you mean by doing it manually with VBE? Would you just create a
code module and write the data in there?

I actually found even one more way, seems like the most fitting, but
not sure if it has some major drawbacks:
I can create a name and actually just assign a value to it. It then
overwrites the RefereTo property and it seems to store it just fine. I
tried it in Excel 2000 and 2003. Does anyone see a major drawback here?

'Create the name
ActiveSheet.Names.Add Name:="Total",
RefersTo:=ActiveSheet.Range("A1")
'Then set the value to something
ActiveWorkbook.Names("Total").Value = "This is a test"

'List all the names with the values
row = 10
For Each n In ActiveWorkbook.Names
Cells(row, 1) = n.Name
Cells(row, 2) = " " & n.RefersTo
Cells(row, 3) = n.Value
row = row + 1
Next n

If you run that, you can see that the value is set to "This is a
test". And that stays even after a save and reopen.

Anyone sees any drawbacks (besides not using it exactly according to
the MS intention)?

Cheers

Remy

 

[Norman Jones]

Hi Remy,

I can create a name and actually just assign a value to it.

In which case, why not additionally hide the name, e.g.:

'=============>>
Public Sub Tester()
ActiveSheet.Names.Add _
Name:="Total", _
RefersTo:=ActiveSheet.Range("A1").Value, _
Visible:=False
End Sub
'<<=============

 

[Norman Jones]

Hi Remy,

Additionally, if you decide to use the defined names option, I would suggest
that you to download Jan Karel Pieterse's invaluable Name Manager addin. It
is downloadable, free of charge, at:

http://www.jkp-ads.com/Download.htm

 

[Remy]

Cool tool, thanks.
Was actually intending to hide them too.
But no drawbacks in using names this way? After all they are not
intended to actually hold real values, but rather to point to a range
in a spreadsheet.
When I set the value, it actually overrides the RefereTo property.

Thanks

Remy

 

[Norman Jones]

Hi Remy,

But no drawbacks in using names this way?

None that I am aware of..

If you see additional reassurance, see xlDynamic at:

http://www.xldynamic.com/source/xld.Names.html