Sorry for what is a probably a dumb question and the generic title. However, I have been trying to feed in 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.
In order to mask the password as it is typed I set up a very simple user form that has two text boxes (username and password) a login command button and a cancel button. I then 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).
Whist 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 have tested with hardcoded login details so I know the code works. I have included below 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
CodePudding user response:
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