Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Store hidden information in Excel throw Add-In | Excel Discussion (Misc queries) | |||
How to store hidden binary data? | Excel Programming | |||
Store Information but not in a Workbook | Excel Programming | |||
store some of the information from one cell into another | Excel Discussion (Misc queries) | |||
Store Hidden Data in the Cell | Excel Programming |