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]

Sorry, that post is all over the place. My question is if I can bring the user input from the userform into the worksheet module to run that process:

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

The other parameters are defined in the worksheet module but I am trying to bring myUserName and myPassword from the userform.

 

[Andrew Poulsom]

Try declaring your variables as Public at the top of a General module and removing all other declarations.

 

[baitmaster]

Declare these variables publicly in a standard code module

Instead of

Dim....

in your userform code, use

Public...

 

[Norie]

How are you running the Essbase pull?

Do you need these values on a worksheet or in code?

 

[laxcat73]

For each to see, this is my code for the worksheet I am trying to use:

Option Explicit
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockflag As Variant) As Long
Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
 
Public Sub Essbase_Update_Pulls()
Dim wbSrc As Workbook
Dim mySheetname As Variant, myServer As Interger, myApp As Variant, myDB As Variant, myUserName As Variant, myPassword As Variant
Dim lockflag As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim strMsgTxt As String
Dim blnRetVal As Boolean
Dim myrng As range
 
Set wbSrc = ActiveWorkbook
 
    mySheetname = "Essbase TCP"
    myServer = "xx.xx.xx.18x"
    myApp = "FIxxOLxx"
    myDB = "FIxxOLxx"
    lockflag = 1
 
[B]frmEssb.Show[/B]
 
 
        x = EssVConnect(mySheetname, myUserName, myPassword, myServer, myApp, myDB)
 
        If x < 0 Then
           blnRetVal = False
           strMsgTxt = "Essbase Login - Local Failure"
        ElseIf x > 0 Then
           blnRetVal = False
           strMsgTxt = "Essbase Login - Server Failure"
        Else
           blnRetVal = True
           strMsgTxt = "Success"
        End If
 
 
            Set myrng = wbSrc.Sheets(mySheetname).range("A1:AI1250")
 
            y = EssVRetrieve(mySheetname, myrng, lockflag)
 
            If y = 0 Then
                MsgBox ("Retrieval successful.")
                Sheets("Essbase TCP").Cells(5, 1) = Now()
                Else
                MsgBox ("Retrieval failed.")
            End If
 
 
        z = EssVDisconnect(mySheetname)
 
            If z = 0 Then
                'MsgBox ("Retrieve successful.")
                'Sheets("Essbase TCP").Cells(i, 13) = Now()
                Else
                MsgBox ("Disconnect failed.")
            End If
 
        z = EssVDisconnect(mySheetname)
 
       Sheets("Essbase TCP").Cells(5, 1).Delete
       Sheets("Essbase TCP").range("A1").Select
 
End Sub

 

[laxcat73]

Am I approaching this right or am I following a failed logic of approaching this userform entry to the worksheet code?

 

[Andrew Poulsom]

Did you try declaring the variables in a General module as suggested?

 

[laxcat73]

Did you try declaring the variables in a General module as suggested?

I'm still quite new to this and I'm not sure exactly what you mean. Create a new module and declare the variables as public?

 

[Andrew Poulsom]

Just insert a module an put this at the top of it:

Public myUserName As Variant
Public myPassword As Variant

Then remove the declarations from your cmdOK_Click event procedure (and any others that may exist).