Hiding data in a ExcelSheet

[tmarko]

I have the following problem:

The users produce a report that is placed in an empty Excelsheet (a
application). Then the user sometime later wants to update the report
For this I need to store some parameters that decide what Report wil
be placed again in the same Excelsheet. Í do not want the paramete
data to be vissible for the user.

I decided to try to produce a hidden sheet when the user first creat
his/her report. This hidden Excel sheet's name is exactly the same a
the visible data with an additional suffix so I can keep track whic
hidden sheet belong to which visible sheet . Several Reports can b
found in different sheets in a single workbook.

The problem is that my users want to be able to change sheet names. B
doing this my application can't find the correct hidden sheet to th
correct visible sheet so the application can update the visible shee
with correct report.

Is it possible to set something else than sheetname so I can "link"
sheets to each other?

Or is it an other way to store the hidden parameters in a the visibl
sheet? I do not want to use any ceel, Rows/columns that can be used fo
the Report. The user should not be aware of these parameters.

I read something about CUSTOMdocumentproperties, but this just apply t
a workbook. But I need to save unique parameters per sheet

 

[Dave Peterson]

Maybe you could use another hidden sheet with a crossreference. But instead of
using the worksheets("xxx").name, you could use the .codename property.

Not many users would know how to change the .codename. (and you could protect
the project to make it more difficult--but not impossible.)

Another option, put some indicator on the worksheet (a hidden name????) and
match them up by that:

You could assign them this way:

Option Explicit
Sub testme1()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Names.Add Name:="MyKey", RefersTo:=wks.CodeName, Visible:=False
Next wks
End Sub

and you could find out what it was like this:
Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
MsgBox wks.Evaluate("myKey") & vbLf & wks.Name
Next wks
End Sub

 

[tmarko]

Thanks Dave. That sounds like a perfect solution. But I need som extra
reference for that to work don't I?

Visual Basic Extensibility library or is it VBA Extensibility library.
But cannot find either of them in References. Only

VISUAL BASIC EXTENSIBILITY
*VBA Extensibility*

but not library. Where can I find the library?

 

[Dave Peterson]

It worked ok for me with no extra references.

And even though you don't need it in this situation, it's called:
"microsoft visual basic for applications extensibility x.x"

(it's in with the Ms!)

 

[tmarko]

Sub ChangeAllWorksheetCodenames()
' requires a reference to the Visual Basic Extensibility library
Dim ws As Worksheet, i As Integer
If TypeName(ActiveWorkbook) = "Nothing" Then Exit Sub
' assign a temporary name to avoid naming conflicts
i = 0
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
On Error Resume Next

ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") =
_
"fubar" & i
On Error GoTo 0
Next ws
' assign the proper name
i = 0
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
On Error Resume Next

ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") =
_
"Sheet" & i
On Error GoTo 0
Next ws
Set ws = Nothing
End Sub

 

[Dave Peterson]

But I didn't change any codenames. I just used them.

 

[tmarko]

I've got an anoying problem with the codename thing. When I try m
application I get an error message saying

Description = Object variable or With block variable not set
Error number =91

But when I try to run the code with the Visual basic Editor open
Everything works fine. So it is impossible to trap the error. What th
is the problem??? Something with the references? Very strang

 

[Dave Peterson]

Aaaaah.

There was just a discussion about using codenames with worksheets that were just
created. It seems like there's a delay (I don't know how long) between the
creation of the worksheet and the availability of the codename.

Here's a link to the thread:
http://google.com/[email protected]

But maybe that won't be a problem. You've created your sheets to keep track a
while ago. So the codename stuff should work for you.

In fact, if you put the naming routine into the auto_open or workbook_open
event, then each time the workbook is opened, the names will be updated (to the
same name) or created (if it didn't exist before).

Does that seem ok?

Option Explicit
Sub auto_open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
wks.Names.Add Name:="MyKey", RefersTo:=wks.CodeName, Visible:=False
If Err.Number <> 0 Then
Debug.Print wks.Name & "--" & Err.Number
Err.Clear
End If
On Error GoTo 0
Next wks
End Sub

You can see any errors in the immediate window inside the VBE.

And the new sheets without an accessible codename shouldn't hurt your
application?????

 

[tmarko]

Thanks Dave will look at it now. Seems to be a strange problem after
all!

Great help this site

 

[tmarko]

I did try your code *Dave Peterson* but can't get the codename settin
to work when I do not have the VBE open even after looping through th
sheets as you suggested.

Really annoyin

 

[tmarko]

Now it works But something strange happens to the naming of the
codename??? I must be able to know what the naming is on the sheets .
One sheet's codename is set to sheet1 instead of the name I provided.
Very strange. And as you write I see some names in the imidiate windows
(the error sheets?)

 

[tmarko]

If I Re-run the sub ONE extra occasion after naming all se sheets I get
the proper names

Thanks for all folks

 

[Dave Peterson]

Glad it worked for you. But I'm not sure if I'd depend on it to work all the
time.

 

[tmarko]

Been using the code

ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName")
strCodeName

To change codename for NT4 Clients, but when I now use the same Add-i
on a Excel2003 computer, it does not work. I get
Error Number =1004 when using the above code.

What should I do

 

[tmarko]

The problem with accessing a VBA project at run-time is that (at leas
w/ Office XP) you have to "allow" for this to happen. Changing th
Worksheets' Reference Names at run-time might cause an error message t
appear because the acces to VBA Projects using macros has not bee
turned on. To do this, do the following:

1. In Excel goto Tools->Options . . .
2. Select the Security tab and click the Macro Security . . . button.
3. In the Trusted Sources tab check the Trust Access to Visual Basi
Project checkbox

 

[Chip Pearson]

Try going to the Tools menu, choose Macros, then the Protection
item. In that dialog, choose Trusted Sources, and check the
"Trust Access To VB Project" check box.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Been using the code

ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeNa
me") =

 

[tmarko]

Programmatically accomplish access to Visual Basic Project. How do I d
that?

I've seen others asking for this, but no answer

 

[Dave Peterson]

That's because if you could change this setting with a program, it wouldn't
offer much protection to the users who wanted it to be off.

(much like the enable macros prompt can't be changed in your program.)

 

[tmarko]

But How should I then be able to change the names of the CodeName i
Excel2003 without informing my users that they manually need to chec
"Trust access to Visual Basic Project".

Isn't it possible to set that via the registry or some other way. No
only manually. Is it possible to set it centrally for a lot of clien
installation

 

[Dave Peterson]

If your nice code could make that change for me, the someone else's not so nice
could do the same thing.

That's why it's a user setting.

And even if you could do this sort of thing centrally, I (as a user) wouldn't
want you to change those settings for me.