Change Default Label Caption with VBA

**nhoj** · 08-07-2020, 01:22 PM

Hi, I'm having trouble figuring out how to get a label caption change to 'Stick'

I'd like to make the label change 'permanent' when I execute a particular event.

If my default label is "Hello" and I execute the below, it will change to "World" until I close the userform. Upon executing, I'd like to change the default caption so the next time the form opens, it will say "World."

me.label.caption = "World"

I've dug around a bit and have seen some code like the below, but haven't been able to get it do do anything other than the code above.

With ThisWorkbook.VBProject.VBComponents("userform").Designer

Thanks in advance

**CK76** · 08-07-2020, 01:28 PM

Personally, I'd just use your code in UserForm_Initialize or Activate event.

Edit: If you want to change label caption based on some condition...
You could have a sub/function in standard module that dynamically changes your caption string.

**nhoj** · 08-07-2020, 01:42 PM

Ya, this is simplified to the root of what I'm trying to accomplish....essentially I have a ticketing system. Request -->Approval-->Approval kicks out several files, one of which is a new excel file with the userform with a header label caption locked in with the ticket number.

I can always put something into a very hidden sheet/etc but I was wondering if I could accomplish a "clean" file generation.

**CK76** · 08-07-2020, 02:07 PM

You could do it like below as well...

Sub ChangeLabel()
Dim ctrl As Control
Dim uf As UserForm

 Set uf = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer

For Each ctrl In uf.Controls
    If ctrl.Name = "Label1" Then
        ctrl.Caption = "World"
    End If
Next

End Sub

Note this only works when Userform is not loaded.

**nhoj** · 08-07-2020, 02:16 PM

That's IT!! Thanks!

**CK76** · 08-07-2020, 02:53 PM

You are welcome and thanks for the rep

Change Default Label Caption with VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Change Default Label Caption with VBA

Re: Change Default Label Caption with VBA

Re: Change Default Label Caption with VBA

Re: Change Default Label Caption with VBA

Re: Change Default Label Caption with VBA

Re: Change Default Label Caption with VBA

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Label caption change combobox

Properties for Label Caption when using VB Script to change.

[SOLVED] Change the caption of OLEobjects label

[SOLVED] Macro to change ActiveX Label Caption

[SOLVED] Change Label Caption VB...

Label caption change... Run Code

Label in Excel... change caption from VB

Bookmarks

Bookmarks

Posting Permissions