How can I carry a definition of a Variant from userform to worksheet?

[laxcat73]

This is my userform code:

Public Sub cmdOK_Click()
    Dim myUserName As Variant
    Dim myPassword As Variant
    Dim WB As Workbook
    Dim WS As Worksheet
    Set WB = ThisWorkbook
    Set WS = Worksheets("Essbase TCP")
        If frmEssb.txtUN.Value = "" Then
            frmEssb.txtUN.SetFocus
            MsgBox "Please enter your Username"
        End If
        
        If frmEssb.txtPW.Value = "" Then
            frmEssb.txtPW.SetFocus
            MsgBox "Please enter your Password"
        End If
    
    myUserName = Me.txtUN.Value
    myPassword = Me.txtPW.Value
    Unload Me
    
End Sub

I am trying to carry the "myUserName" and "myPassword" over to a sheet that's running an Essbase pull. I tried using the following in place of "myUserName = Me.txtUN.Value, myPassword = Me.txtPW.Value" but it does not allow this argument.

        WS("myUserName") = Me.txtUN.Value
        WS("myPassword") = Me.txtPW.Value

Is there any way to define one of these inputs as "myUsername" in my worksheet which is defined

Dim myUsername AsVariant

and used in this process

        x = EssVConnect(mySheetname, myUserName, myPassword, myServer, myApp, myDB)

 

[laxcat73]

I got it!!

Thanks guys, declaring them public on another module worked perfectly.

 

[obiron]

Andrew's not taking any prisoners today

Normally you define your variables within a procedure and so they are only available to that procedure.

You can also define them as Public variables OUTSIDE of a procedure and then they are available to all procedures in the workbook

instead of

Private Sub myMacro()
 
  Dim myVariable as Variant
  myVariable = Cstr(format(now(),"dd/MM/YYYY"))
 
End Sub

do

Public myVariable as Variant
 
Private Sub myMacro()
 
  myVariable = Cstr(format(now(),"dd/MM/YYYY")) 
 
End Sub
 
 
Private sub myMacro2()
 msgbox myVariable
 
End Sub

 

[Norie]

How do you close frmEssb?

If you don't unload it you could add this after frmEssb.Show.

myUserName = frmEssb.txtUN.Value
myPassWord = frmEssb.txtPW.Value

That will put the values in the variables and then you can unload the form like this:

Unload frmEssb

 

[Norie]

You don't need public variables for this.

 

[laxcat73]

Thanks guys