Is there an Excel equivalent to the Access tag property 3

[SBendBuckeye]

I have done much more Access VBA than Excel VBA. Is there an Excel VBA equivalent to the tag property in Access, preferably at the Workbook or Worksheet level? The tag property in Access is just a property Access exposes but never uses to allow UserDefinedStuff.

Thanks for the help and have a great day!

 

[jcrater]

I don't see a tag property for worksheets or workbooks in 97. I suppose you could use global variables to hold your data.

 

[RobBroekhuis]

Global variables don't keep their data when the workbook is saved and closed. I think most of us define a worksheet, or an area of a worksheet, where we keep this kind of user-defined information. Depending on your preference, this data area or worksheet could be hidden. Also, you can put values directly into the Names collection, e.g.
names.add "MyTag","It's Spring!"
These will be saved with the workbook.
Rob

 

[SteveBausch]

Create your own Property, it will show up in Intellisense, like any obedient property.

This can be done for any object, and since a worksheet is a object......

Just tested "Tagging" a code module, that works too, though I cannot say it's usefulness.

Normally, people think of adding properties to userforms, to allow retrieval of "Canceled" or "Completed" booleans, etc. Keep in mind you shouldn't unload the form until you retrieve the property.


Have yet to see a decent tutorial that explained the "flow", so I have commented the code, and created descriptive namings.

Dim InternalVariable As String 'Internal variable that holds the property's value.

Property Let Tag(AssignedValue As String) 'Allows assignment
InternalVariable = AssignedValue 'Internal variable assigned as argument
End Property

Property Get Tag() As String 'Allows accessing
Tag = InternalVariable 'Property assigned value of internal variable
End Property

 

[RobBroekhuis]

That's cool (star!) - I didn't know you could assign properties to pre-existing objects. But the tag is not saved with the workbook, which may or may not be what SBB intended.
Rob

 

[SteveBausch]

Rob,
Yep, you are so right about the volatility. Could be combined with the techniques you suggested.

I am not familiar with Names, will have to research. Thanks for that insight.

Steve

 

[SteveBausch]

Rob,
You got me thinking...... See if you like this.

Add a worksheet, call it "PropertySheet" as .Name and .Codename

Assign .Visible as xlSheetVeryHidden (if you want).

Use the following code, modify as needed.

Static Property Let Tag(AssignedValue As String) 'Allows assignment
PropertySheet.Cells(1, 1) = AssignedValue 'Cell holds the value
End Property

Static Property Get Tag() As String 'Allows accessing
Tag = PropertySheet.Cells(1, 1) 'Retrieves Cell value
End Property

I assume the

Static

statement isn't needed, but just in case.....

Form modules already have .Tag, but no reason a .Tag1, .Tag2, etc. couldn't be added (or change the name).

Steve

 

[SBendBuckeye]

Steve and Rob,

Thanks a lot for the discussion. Here is a little bit of background on what I am trying to accomplish. Perhaps this will help.

Hospital client has old patient billing system which exposes a scripting process to automate batch updates. It is just a terminal emulation process. You can sit there and watch it paint the screen over and over.

After some digging, I was able to control the scripting process via Access97 enough to logon, select the facility and then fire off one of the above scripting files to do the updates.

I then put together an Excel spreadsheet to use as a control file for the Access. This had information such as name and path of needed data files, name and path of script files, etc. Access just processes the spreadsheet one row at a time until it hits an empty row.

The Excel file had several worksheets named GeneralFileInfo, InputFileInfo, OutputFileInfo, eMailInfo, etc as well as a sheet for data validation and a sheet named RunStats which Access updated when running the scripts.

For data validation purposes, I was looking for a way to identify a given sheet as being one that contains user data. For now, I spin though the sheets using each sh in thisworkbook.sheets and then use instr(sh.name, "Info&quot > 0 to identify them. That's where I wanted a tag property.

Rob, based on your idea above I could have created a name called Tag on each sheet and tested names for name and tag.
This would have accomplished my objective nicely.

Have a great day!

 

[RobBroekhuis]

SBB,
Let us know how you fare. Steve's approach is a very nice one - use one hidden sheet to keep the tag values of all other sheets. Of course, you have to put the property functions into the code of each of the sheets manually (I guess you could automate that too, though). I'm intrigued by the possibilities of custom properties...
Rob

 

[SteveBausch]

Referencing a worksheet can be done in various ways, and it can be confusing. Hopefully I can clarify.

Open Excel, do not open the editor, just yet. Open a new workbook. Move "Sheet1" to between "Sheet2" and "Sheet3". Rename "Sheet1" as "SheetOne".

Open the VBA Editor. View-Project Explorer, View-Immediate.
All entries will be in Immediate pane.

Look at Project Explorer. Notice the sheetnames; the first is .CodeName, the second (in parantheses) is .Name

Referencing by position:

? Sheets(1).Name

returns "Sheet2", because it's the first sheet in the ordering, from left to right. Not a reliable way to reference sheets.


Referencing by .Name:

? Sheets("SheetOne").Name

returns "SheetOne"

Referencing by .CodeName:

? Sheet1.Name

returns "SheetOne"

Just thinking you may not need to loop through the sheets to locate the one in question. Have a reliable naming convention and reference the sheet directly.

The .CodeName can only be changed from the Editor, from the Properties pane. When I need to specify which sheet to manipulate, I use the .Codename. Less typing, too.