Store hidden information in a spreadsheet
 

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

Store hidden information in a spreadsheet
 

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

Store hidden information in a spreadsheet
 

Remy wrote:
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.

Store hidden information in a spreadsheet
 

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

Store hidden information in a spreadsheet
 

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

worksheets("Data").visible = xlSheetVeryHidden

--
Regards,
Tom Ogilvy



"Remy" wrote in message
ups.com...
Oh yeah, hidden cells doesn't look like the right thing either, if you
do a copy/paste they show up, right?

Store hidden information in a spreadsheet
 

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
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

Store hidden information in a spreadsheet
 

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

Store hidden information in a spreadsheet
 

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
'<<=============

---
Regards,
Norman


"Remy" wrote in message
ups.com...
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

Store hidden information in a spreadsheet
 

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

---
Regards,
Norman



"Norman Jones" wrote in message
...
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
'<<=============

---
Regards,
Norman


"Remy" wrote in message
ups.com...
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

Store hidden information in a spreadsheet
 

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

Store hidden information in a spreadsheet
 

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


---
Regards,
Norman



"Remy" wrote in message
oups.com...
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