Maintaining UserForm1.Tag value

[Bagharmin]

Is there a way to maintain the Tag value of a UserForm after it is unloaded? I have a spreadsheet where the user signs in and activates this particular form. I initially assigned the user's name to the UserForm1.Tag field. When UserForm1 activates, it fills a text box based on the value of the Tag. However, when they finish with the form (i.e., it unloads) and then come back to it (without closing the workbook), the text box that had their name in it is now blank.

I know I can drop the user's name into a cell and just pull it from there, but is there a simple way to "save" the Tag value of UserForm1 so that it doesn't go away between uses?

Many thanks.

 

[Andrew Poulsom]

You need to use the Designer object:

http://support.microsoft.com/kb/185774

 

[Bagharmin]

Are you saying that, instead of just inserting a user form via VBA and having the code show and unload it, I should create it with:

Set x = Application.VBE.ActiveVBProject.VBComponents.Add _
(vbext_ct_MSForm)

and then change the Tag value by using:

With UserForm1
.Properties("Tag") = MrUser
End With

I'm sorry, but I'm not quite following. I really need more caffeine.

Btw, as you probably already figured out, I "drew" my user form by selecting Insert, then UserForm in the VBA menu.

 

[Andrew Poulsom]

Actually you can do it like this:

ThisWorkbook.VBProject.VBComponents("UserForm1").Properties("Tag") = "Andrew"

 

[Bagharmin]

I entered the suggested code and tested it by signing in (my code is supposed to assign the user's name to the Tag field when you sign in). After clicking my "sign-in" button, I got an error message saying "Programmatic access to Visual Basic Project is not trusted". So I clicked on DeBug then clicked the little "play" arrow in vba and got this error message: "Method 'VBProject' of object '_Workbook' failed".

I assume this means I have larger issues than assigning a value to the Tag field?

 

[Andrew Poulsom]

Choose Tools|Macro|Security, on the trusted Publishers tab check 'Trust access to Visual Basic Project' and click OK.

 

[Bagharmin]

Sorry, I'm using Excel 2007 (should have told you that earlier). I eventually found it in the Trust Center under Macro Settings. Everything works fine now that I clicked that box. However, that means that everyone else who uses this will need to have that box checked as well, correct?

 

[Andrew Poulsom]

Yes, that's right.

 

[Norie]

Bagharmin

Why do you want to keep the tag?

What will happen when another user opens the userform?

Does the existing Tag be replaced with the new username?

 

[Bagharmin]

That's it exactly. When someone new signs in, I want that person's name to show up in a specific textbox on my userform. So, instead of cluttering up a spreadsheet, I thought I'd use the Tag property of the userform. However, since everyone who uses this will have to have the "Trust access to Visual Basic" box checked, it's probably going to be easier just to drop the username into a cell and have the userform pick it up from there. The easier this is for others to use, the better.

Even so, knowing how to do this will be good, in case the need ever arises again.

Thanks for sticking with me on this one.