Pass userform values into general module

Question

I have been trying to feed values from my login user form into the main module code that generates a sql query that will pull data from our general ledger straight into Excel.

To mask the password as it is typed I set up a user form that has two text boxes (username and password) a login command button and a cancel button. I do some checks to the login click command checking the user details fields are not empty (there is further code in the download request that will validate if the user name is correct based on the response from the server).

What I can't get through is on my main module that feeds the class that will generate the query string, to pick up the items that are fed into the user form. I tested with hardcoded login details so I know the code works.

Below is the portion of my user form that relates to the login click and the general module portions around this user form call and then the updating of the class module. The main module is triggered by a button click on the worksheet.

user form snippet

Public Sub Login_Click()

'check user name is present
Dim corect_details As Integer
Dim uname As Integer
Dim pswrd As Integer
Dim nBIUsername As String
Dim nBIPassword As String
pswrd = 0
uname = 0
If Len(BIUsername) = 0 Then
    MsgBox "please enter youre ISFE user name", vbOKOnly
    Exit Sub
Else
    uname = 1
    nBIUsername = BIUsername
End If
If Len(BIPassword) = 0 Then
    MsgBox "please enter a password", vbOKOnly
    Exit Sub
Else
    pswrd = 1
    nBIPassword = BIPassword
End If
details = pswrd + uname
If details = 2 Then
    MsgBox "username is " & BIUsername & vbCrLf & "password is " & BIPassword
    Me.Hide
    
Else
End If

End Sub

main module snippets

Dim nBIUsername As String
Dim nBIPassword As String

Sub showlogin()
Login.Show
GetDataFromBI 
End Sub


Public Sub GetDataFromBI()

    Dim BIReport As CBIReport: Set BIReport = New CBIReport
    
    Load Login
    
    With BIReport
        .BIUsername = nBIUsername  'works when replaced with hardcoded username
        .BIPassword = nBIPassword       'works when replaced with hardcoded Password
        .REPORTPATH = MyReportPath
        .ReportName = MyReportName
        .FilterString = FilterString
        .OutputOrigin = ThisWorkbook.Sheets("Output").Range("A1")
        .GetData
        If Not .IsLoginSuccessful Then MsgBox "Login not successful", vbCritical + vbOKOnly: GoTo CleanExit
        If InStr(.LastDownloadStatus, "Success") > 0 Then
            MsgBox "Download successful", vbOKOnly
        Else
            MsgBox "Download not successful", vbCritical + vbOKOnly
        End If
    End With
    
CleanExit:
    Set BIReport = Nothing

End Sub

Answer 1

In order to make your 2 module level variables available to the userform declare them as Public instead of using Dim:

Public nBIUsername As String
Public nBIPassword As String

You need to delete the identically named variables:

Dim nBIUsername As String
Dim nBIPassword As String

from your userform as these local variables will shadow the public ones.

---

Rather than using globals/public variables which is generally bad practice you can use a dialog based approach to return the data.

Create a Type in the module to store the user details:

Public Type LoginDetals
    Username As String
    Password As String
    IsValid  As Boolean
End Type

Add an exit button & change the userform to:

Public Function getLogin() As LoginDetals
    Me.Show vbModal
 
    '// logic here
    getLogin.Username = "bob"
    getLogin.Password = "123"
    getLogin.IsValid = True
End Function

Private Sub ExitButton_Click()
    Unload Me
End Sub

Then in the module you can fetch the details:

Sub foo()
    Dim userDetails As LoginDetals

    userDetails = Login.getLogin()

    If (userDetails.IsValid) Then
        GetDataFromBI userDetails
    End If
End Sub

Function GetDataFromBI(userDetails As LoginDetals)
    MsgBox "hello " & userDetails.Username
End Function