Assigning a variable directly from a userform

Question

I am trying to add some code to have a userform pop up which allows the end user to input their login/password for s specific website. This code Passing variable from Form to Module in VBA got me closer to my goal but I am not sure how to make it work the way I need to. Here is the code for my userform.

Private Sub CommandButton1_Click()
UPass = UserForm1.UserID
Unload UserForm1
End Sub

Private Sub CommandButton1_Click()
UID = UserForm1.WavePassword
Unload UserForm1
End Sub

And I am using the below in the code to log into the website.

    Public Sub Connect_To_Wave()
    Dim Dasboard As Worksheet
        Set Dashboard = ActiveWorkbook.Worksheets("Dashboard")
    Dim UID As String
        UID = driver.findElementByName("PASSWORD").SendKeys UID
    Dim UPass As String
        UPass = driver.findElementByName("PASSWORD").SendKeys Upass




Set ie = CreateObject("InternetExplorer.Application")
my_url = "url of website - not a variable"

With ie
    .Visible = True
    .Navigate my_url
    .Top = 100
    .Left = 530
    .Height = 700
    .Width = 400

Do Until Not ie.Busy And ie.readyState = 4
    DoEvents
Loop

End With

ie.Document.getElementById("txtLoginUsername").Value = ""
ie.Document.getElementById("txtLoginPassword").Value = ""
ie.Document.getElementById("txtLoginUsername").Value = UID
ie.Document.getElementById("txtLoginPassword").Value = UPass
ie.Document.getElementById("btnLogin").Click

Do Until Not ie.Busy And ie.readyState = 4
    DoEvents
Loop
End Sub

The issue I am running into is that I get an error of "expected end of statement" on the uid/upass variables. How do I correctly get the userform to pass the input directly into the variable so the variable can be used to log into the website? I am completely open to change the method if there is a better way as well.

I haven't even gotten to the point of running either set of code. As I finished typing the UID = / UPass = lines they both turned red and flagged the error. — TonyP, Jul 14 '16 at 18:25
Procedure `CommandButton1_Click` is defined twice. That can't compile. Also `Connect_To_Wave()` should be `ConnectToWave(ByVal uid As String, ByVal pwd As String)` - you're not showing where the form is instanciated, but it assigning global variables and unloading the global/default instance of the form isn't a sign of solid code. — Mathieu Guindon, Jul 14 '16 at 18:26
I made those changes as suggested. I am not sure how to test it from here. I hit F8 to run the userform and that is running as expected I think but I am not sure how to test the login code at the same time. When I try to F8 the login code now it just dings but does not provide an error message. — TonyP, Jul 14 '16 at 18:32
@TonyP if the code is now working, then you can submit it to [Code Review](http://codereview.stackexchange.com) instead. — SierraOscar, Jul 14 '16 at 18:34
@MacroMan I am not sure if it is working. For some reason it won't let me run it? — TonyP, Jul 14 '16 at 18:34

score 3 · Accepted Answer · answered Jul 14 '16 at 18:38

This can't possibly compile:

Private Sub CommandButton1_Click()
UPass = UserForm1.UserID
Unload UserForm1
End Sub

Private Sub CommandButton1_Click()
UID = UserForm1.WavePassword
Unload UserForm1
End Sub

A procedure cannot exist twice. Rename your button OkButton, add some CancelButton and rewrite your form's code-behind as follows:

Option Explicit
Private cancelling As Boolean

Public Property Get UID() As String
    UID = UserID.Text
End Property

Public Property Get PWD() As String
    PWD = WavePassword.Text
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelling
End Property

Private Sub OkButton_Click()
    Me.Hide
End Sub

Private Sub CancelButton_Click()
    cancelling = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        cancelling = True
        Me.Hide
    End If
End Sub

Notice OkButton, CancelButton and the QueryClose handler only ever Hide the form, so the calling code can still read the IsCancelled, UID and PWD property values.

That calling code could do this - assuming the UserForm is renamed to LoginPrompt:

Public Sub DownloadStuff()
    With New LoginPrompt
        .Show vbModal
        If .IsCancelled Then Exit Sub
        ConnectToWave .UID, .PWD
    End With
End Sub

And last, the ConnectToWave procedure, taking the user's input:

Private Sub ConnectToWave(ByVal userID As String, ByVal password As String)
    ' there, you got your values from the form - now use them!
End Sub

This is a basic question but where would I put the Public Sub Downloadstuff() in the module with the ConnecttoWave or with the userform? — TonyP, Jul 14 '16 at 18:45
That would be in a standard module, and could be exposed as a macro attached to a button on the worksheet, for example - I'd put it alongside the `ConnectToWave` procedure. — Mathieu Guindon, Jul 14 '16 at 18:45

score 1 · Answer 2 · answered Jul 14 '16 at 18:25

1

I am not sure what driver is but this statement is wrong

UID = driver.findElementByName("PASSWORD").SendKeys UID as Sendkeys is a method so when trying to assign the return value you need to use brackets.

Try this :

UID = driver.findElementByName("PASSWORD").SendKeys(UID)

answered Jul 14 '16 at 18:25

cyboashu

10,196
2
27
46

driver was some object used in the linked post/ findElementByName is a method that is part of the ArcObjects library. Given that the OP has no idea how to declare global variables, I highly doubt he is using ArcObjects. – Tim Jul 14 '16 at 18:43

score 1 · Answer 3 · answered Jul 14 '16 at 18:26

This:

Dim UID As String
    UID = driver.findElementByName("PASSWORD").SendKeys UID
Dim UPass As String
    UPass = driver.findElementByName("PASSWORD").SendKeys Upass

Should be:

Dim UID As String
    UID = driver.findElementByName("PASSWORD").SendKeys(UID)
Dim UPass As String
    UPass = driver.findElementByName("PASSWORD").SendKeys(Upass)

If you are calling a function that isn't assigning anything back to a value then you don't need to use parentheses, but if it's assigning something to a variable then you need to use the above syntax instead.

Where Foo() is a function and Bar is a variable

'// Not assigning a value
Foo Bar

'// Assigning a value
someVar = Foo(Bar)

score 0 · Answer 4 · answered Jul 14 '16 at 18:38

0

To accomplish what you want, you have to create a global variable at the top of your module. I doubt you are using ArcObjects so scrap the whole driver.findElementByName stuff. Besides, you already set the value of the username and password fields correctly (this bit: ie.Document.getElementById("txtLoginUsername").Value = UID) so there's no need for any SendKeys method.

What you need is something like this at the very top of your code module:

Option Explicit
Public UID as String
Public UPass as String

answered Jul 14 '16 at 18:38

Tim

2,701
3
26
47

I should have mentioned I do have the publics at the top. – TonyP Jul 14 '16 at 18:40
@Tim the error message is being caused by incorrect syntax, not variable scope. – SierraOscar Jul 14 '16 at 18:43

Assigning a variable directly from a userform

4 Answers4

Linked