Passing variable string from WorkbookOpen to userform

[Nyanko]

I'm trying to pass a string value from the WorkbookOpen module to a label or textbox on a userform. This will enable me to devise a custom welcome splash screen

I can't seem to pass the contents of the variable from the module to the userform. I've researched and either my search terms are wrong or I can't find the solution I'm looking for. Thought it would be simple

 

[VoG]

I think that you would need do Dim the variable as Public in a standard module. It would then be available to use in the workbook code module.

 

[tweedle]

How and where is the variable declared?
Some Form event would need to read the variable; so the scope of the variable needs to be appropriate for the use.

 

[Nyanko]

I think that you would need do Dim the variable as Public in a standard module. It would then be available to use in the workbook code module.

Ah I think I see. I've never passed from the workbook open/close modules. Can the Dim be in any module ??

Thanks for all the quick replies !

 

[VoG]

Yes, at the top (before any subs) in any regular module.

 

[RoryA]

You can use:

Dim frm as Userform1
Set frm = New userform1
frm.Label1.Caption= "hello"
frm.show

for example.

 

[Nyanko]

Still can't get this to work ....

In my ThisWorkbook module I have :

        pwdmsg = "Warning ! Your password will expire in " & 90 - PassNo & " days  " & vbCrLf
    frmSplash.Show (pwdmsg)

and the code behind the frmSplash is :

Private Sub UserForm_Initialize()

    MyMsg = pwdmsg & vbCrLf & vbCrLf & "Hello"
    SplashBox2 = MyMsg

End Sub

I can't get the value for pwdmsg to save to the frmSplash code and am now geting mismatch 13 errors.

help ?

 

[tweedle]

If you've Dim'd pwdmsg as Public, you should need to 'pass' it to the Form.
frmSplash.Show () < The only possible parameter here is True or False to set the Modal property of the form.

 

[Nyanko]

Thank you for all of your kind advise. However I still can't get this to work

My coding now looks like this :

In a separate module I have
Public pwdmsg As String

Private Sub Workbook_Open()
pwdmsg = "Text of the message"
frmSplash.Show
End Sub

Private Sub UserForm_Initialize()
SplashBox2.Caption = pwdmsg
End Sub

I'm missing something simple ... help !

 

[RoryA]

Where specifically (which module) is this line:

Public pwdmsg As String

Note: if you use the syntax I posted, you don't need a public variable.