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

laxcat73

Board Regular
Joined
Aug 5, 2011
Messages
143
This is my userform code:

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.

Code:
        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
Code:
Dim myUsername AsVariant
and used in this process
Code:
        x = EssVConnect(mySheetname, myUserName, myPassword, myServer, myApp, myDB)
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Code:
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.
 
Upvote 0
Declare these variables publicly in a standard code module

Instead of
Code:
Dim....
in your userform code, use
Code:
Public...
 
Upvote 0
How are you running the Essbase pull?

Do you need these values on a worksheet or in code?
 
Upvote 0
For each to see, this is my code for the worksheet I am trying to use:

Code:
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
 
Last edited:
Upvote 0
Am I approaching this right or am I following a failed logic of approaching this userform entry to the worksheet code?
 
Upvote 0
Just insert a module an put this at the top of it:

Code:
Public myUserName As Variant
Public myPassword As Variant

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

Forum statistics

Threads
1,224,267
Messages
6,177,549
Members
452,783
Latest member
back1ply

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top