Is there any way to add a "dropdown" type input box in place of an InputBox with a vba Macro? Macro is enclosed, scrubbed for security.

[Arrick]

Good Evening All,

If you look at the code below, you will see the "Dim skillsNeeded As String" and then you will see it at the end of the URL with all the concatenated parts...

What I need to have is a list box to select items from in a pop-up box similar to the InputBoxes such as userName, and etc...

Is there a way to accomplish this?

Sub retrieveData()
'Initialize variables
Dim i As Integer
Dim URL As String
Dim sheet As String
Dim range As String
Dim userName As String
Dim passWord As String
[U][B]Dim skillsNeeded As String[/B][/U]
'Paste API URL here
URL = "http://*****.********.**********/RetrieveDataTable?OpenAgent&dataTable=Customers&userid="
userName = Application.InputBox("Your Username is what you use for logging into ******")
passWord = Application.InputBox("Your Password is what you use for logging into ******")
[U][B]skillsNeeded =[/B][/U]

URL = URL & userName & "&password=" & passWord & "&download=FALSE&includeEmptyTag=FALSE&dereference=TEXT&fieldList=accommodationsNeeded_customerProfile~contEnrollmentOtherEducation_customerProfile~criminalRecord_customerProfile~dateOfBirth_customerProfile~emailOne_customerProfile~employmentStatus_customerProfile~ethnicity_customerProfile~nameFirst_customerProfile~gender_customerProfile~highestGrade_customerProfile~nameLast_customerProfile~militaryService_customerProfile~otherDemographics_customerProfile~race_customerProfile~residenceAddressZipCode_customerProfile&selectionCriteria=experienceAndSkills_customerProfile~STRCON_LS~" & [U][B]skillsNeeded[/B][/U]

'Enter the name of the sheet to insert the data into
sheet = "Sheet1"
'Enter where in that sheet you would like the data to go
range = "A1"
'Remove any previous XML maps (used for schema generation)
For i = ActiveWorkbook.XmlMaps.Count To 1 Step -1
ActiveWorkbook.XmlMaps(i).Delete
Next
'Clear the sheet so data can be inserted into it.
'NOTE: This CLEARS ALL the content in the sheet specified above
'Comment this out if you do not wish to clear all the contents on the sheet
'BUT if you are trying to insert data into cells that already have
'content in them, you will get an error message
ActiveWorkbook.Sheets(sheet).Cells.Clear
'Import XML data file, creating a new XML mapping.
ActiveWorkbook.XmlImport URL:=URL, ImportMap:=Nothing, Overwrite:=True, Destination:=ActiveWorkbook.Sheets(sheet).range(range)
'Remove the connection that was just created in the workbook
ActiveWorkbook.Connections("RetrieveDataTable").Delete

End Sub

 

[mikerickson]

Perhaps something like this user form. Put this code in a user form that has one listbox, one label, and two command buttons (butOK and butCancel)

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub


Public Function ChooseFromList(List As Variant, _
                                        Optional Prompt As String = "Choose an item", Optional Title As String = "Choose From List", _
                                        Optional MultipleSelection As Boolean) As String
    Dim oneItem As Variant
    
    With Me
        .Label1.Caption = Prompt
        If TypeName(List) = "Range" Then
            If List.Cells.Count = 1 Then
                List = Array(List.Text)
            End If
        End If
        With .ListBox1
            For Each oneItem In List
                .AddItem oneItem
            Next oneItem
        End With
        .Show
    End With

    With UserForm1
        If .Tag = "OK" Then
            If .ListBox1.MultiSelect = fmMultiSelectSingle Then
                ChooseFromList = .ListBox1.Text
            Else
                
            End If
        End If
    End With
    Unload UserForm1
End Function

It is used from code like.

MsgBox "The user chose " & Userform1.ChooseFromList(Array("one", "two", "three"), "Pick A Number")

 

[Arrick]

Ok.. Thank you kindly for your response.... Please, Can you act like I don't know what I am doing, and tell me how to put that into the code I already have? I am not sure what all this does, and haven't got a clue as to how to implement it.

Perhaps something like this user form. Put this code in a user form that has one listbox, one label, and two command buttons (butOK and butCancel)

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Private Sub UserForm_Click()

End Sub


Public Function ChooseFromList(List As Variant, _
                                        Optional Prompt As String = "Choose an item", Optional Title As String = "Choose From List", _
                                        Optional MultipleSelection As Boolean) As String
    Dim oneItem As Variant
    
    With Me
        .Label1.Caption = Prompt
        If TypeName(List) = "Range" Then
            If List.Cells.Count = 1 Then
                List = Array(List.Text)
            End If
        End If
        With .ListBox1
            For Each oneItem In List
                .AddItem oneItem
            Next oneItem
        End With
        .Show
    End With

    With UserForm1
        If .Tag = "OK" Then
            If .ListBox1.MultiSelect = fmMultiSelectSingle Then
                ChooseFromList = .ListBox1.Text
            Else
                
            End If
        End If
    End With
    Unload UserForm1
End Function

It is used from code like.

MsgBox "The user chose " & Userform1.ChooseFromList(Array("one", "two", "three"), "Pick A Number")

 

[mikerickson]

What is the list of "skills needed" that the user is to choose from?

 

[Arrick]

What is the list of "skills needed" that the user is to choose from?

Registered Nurse
Nurses Aid
Certified Nurses Aid
Electrician


And the list goes on, but that will give you an idea.

Thanks.

 

[mikerickson]

To integrate my solution with your code,
1) create a userform, with ListBox1, Label1 and two command buttons named butOK and butCancel.
2) put the posted code in the code module for that userform. (If your userform is not named UserForm1, replace that throughout the function ChooseFromList.

3) In your posted code use the lines

SkillsNeeded = Userform1.ChooseFromList(Array("Registered Nurse", "Nurses Aid", "Certified Nurses Aid", "Electrician", Prompt:= "Select the nessesary skill")
    If SkillsNeeded = vbNullString Then Exit Sub: Rem cancel pressed

4) You may want to change the properties and placement of your newly made userform controls to make it visually pleasing.

If you need the user to have the option to select more than one skill, let me know, I need to complete that portion of the code I posted.

 

[Arrick]

Thank You, Almost there I think...

I had to add an ) to the array section..

I get the following error...

VBE is highlighting the Prompt:= when the error is happening.

To integrate my solution with your code,
1) create a userform, with ListBox1, Label1 and two command buttons named butOK and butCancel.
2) put the posted code in the code module for that userform. (If your userform is not named UserForm1, replace that throughout the function ChooseFromList.

3) In your posted code use the lines

SkillsNeeded = Userform1.ChooseFromList(Array("Registered Nurse", "Nurses Aid", "Certified Nurses Aid", "Electrician", Prompt:= "Select the nessesary skill")
    If SkillsNeeded = vbNullString Then Exit Sub: Rem cancel pressed

4) You may want to change the properties and placement of your newly made userform controls to make it visually pleasing.

If you need the user to have the option to select more than one skill, let me know, I need to complete that portion of the code I posted.

 

[mikerickson]

Sorry, I missed a parenthesis

SkillsNeeded = Userform1.ChooseFromList(Array("Registered Nurse", "Nurses Aid", "Certified Nurses Aid", "Electrician"[COLOR="#FF0000"])[/COLOR], Prompt:= "Select the nessesary skill")

 

[Arrick]

First, I want to say thank you for helping a newb....

So here is what I have:

ThisWorkbook

Sub retrieveData()
'Initialize variables
Dim i As Integer
Dim URL As String
Dim sheet As String
Dim range As String
Dim userName As String
Dim passWord As String
Dim skillsNeeded As String
'Paste a API URL here
URL = "[URL]http://****.*****.*****/RetrieveDataTable?OpenAgent&dataTable=Customers&userid[/URL]="
userName = Application.InputBox("Your username is 1st intial and last name")
passWord = Application.InputBox("Use your G-Stars Password")
skillsNeeded = UserForm1.ChooseFromList(Array("Registered Nurse", "Nurses Aid", "Certified Nurses Aid", "Electrician"), Prompt:="Select the nessesary skill")
    If skillsNeeded = vbNullString Then [SIZE=4][B]Exit Function[/B][/SIZE]: Rem cancel pressed
    URL = URL & userName & "&password=" & passWord & "&download=FALSE&includeEmptyTag=FALSE&dereference=TEXT&fieldList=accommodationsNeeded_customerProfile~contEnrollmentOtherEducation_customerProfile~criminalRecord_customerProfile~dateOfBirth_customerProfile~emailOne_customerProfile~employmentStatus_customerProfile~ethnicity_customerProfile~nameFirst_customerProfile~gender_customerProfile~highestGrade_customerProfile~nameLast_customerProfile~militaryService_customerProfile~otherDemographics_customerProfile~race_customerProfile~residenceAddressZipCode_customerProfile&selectionCriteria=experienceAndSkills_customerProfile~STRCON_LS~" & skillsNeeded
'Enter the name of the sheet to insert the data into
sheet = "Sheet1"
'Enter where in that sheet you would like the data to go
range = "A4"
'Remove any previous XML maps (used for schema generation)
For i = ActiveWorkbook.XmlMaps.Count To 1 Step -1
ActiveWorkbook.XmlMaps(i).Delete
Next
'Clear the sheet so data can be inserted into it.
'NOTE: This CLEARS ALL the content in the sheet specified above
'Comment this out if you do not wish to clear all the contents on the sheet
'BUT if you are trying to insert data into cells that already have
'content in them, you will get an error message
ActiveWorkbook.Sheets(sheet).Cells.Clear
'Import XML data file, creating a new XML mapping.
ActiveWorkbook.XmlImport [URL="http://www.mrexcel.com/forum/=URL"]URL:=URL[/URL], ImportMap:=Nothing, Overwrite:=True, Destination:=ActiveWorkbook.Sheets(sheet).range(range)
'Remove the connection that was just created in the workbook
ActiveWorkbook.Connections("RetrieveDataTable").Delete
ActiveWorkbook.Sheets("Sheet1").range("D1") = Date
End Sub

UserForm1

Private Sub ListBox1_Click()
End Sub
Private Sub butCancel_Click()
    Unload Me
End Sub
Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub
Private Sub UserForm_Click()
End Sub
Public Function ChooseFromList(List As Variant, _
                                        Optional Prompt As String = "Choose an item", Optional Title As String = "Choose From List", _
                                        Optional MultipleSelection As Boolean) As String
    Dim oneItem As Variant
    
    With Me
        .Label1.Caption = Prompt
        If TypeName(List) = "Range" Then
            If List.Cells.Count = 1 Then
                List = Array(List.Text)
            End If
        End If
        With .ListBox1
            For Each oneItem In List
                .AddItem oneItem
            Next oneItem
        End With
        .Show
    End With
    With UserForm1
        If .Tag = "OK" Then
            If .ListBox1.MultiSelect = fmMultiSelectSingle Then
                ChooseFromList = .ListBox1.Text
            Else
                
            End If
        End If
    End With
    Unload UserForm1
End Function

and I MUST be doing something wrong... because I get the following error, text is bold and bigger above that it is highlighting.

 

[Arrick]

Hey Mike,

I got it working...

However.... I have more skills than will fit into one array... How to I change it so that I get something like below to insert??

SkillsNeeded = Userform1.ChooseFromList(Array("Registered Nurse", "Certified Nursing Assistant", "Licensed Practical Nurse (LPN)", "Medical Records & Health Information Technician", "Medical Coding / Billing", "Physical Therapist", "Medical Assistant", "Speech-Language Pathologist", "Occupational Therapist", "Medical + Health Service Manager", "Pharmacy Technician", "Medical Secretary""Counselor / Social Worker", "Pharmacist", "Mechanical  / Manufacturing Engineer", "Production Worker / General Labor", "Machinist", "Production Supervisor", "CNC Operator / CNC Programmer", "Quality Engineer", "Welder, Cutter, Polisher, Solderer", "Account Manager / Sales Specialist", "Utility Worker", "Electrical Engineer", "Quality Technician", "Quality Manager", "Mantenance Mechanic / Technician", "Customer Support / Representative", "Computer Systems Engineer""Network & Computer Systems Administrator", "Electrician", "Hand Harvesting & Tree Pruning", "Forklift Operator", "RETAIL & HOSPITALITY", "Retail Salesperson / Sales Associate", "Restaurant Manager", "Food Service / Restaraunt Manager", "Host / Server / Wait Staff", "Customer Service Representative", "Bartender - Barista", "Mechanic / Service Technician", "Cook & Line Cook", "Cashier", "Merchandiser", "General / Retail Store Manager", "Janitors & Cleaners / Maids & Housekeeping", "Heavy & Tractor-Trailer Truck Driver", "Teller / Account Represetative", "Education Teacher, Postsecondary", "Roofer", "Education Teacher, Primary / Secondary", "Landscaping & Groundskeeping", "Light Truck or Deliver Service Driver", "Administrative Assistant", "Loss Prevention / Security Guard"), Prompt:= "Select the nessesary skill")

If (Array1="",Array2,exit)

Thanks in advance.
Arrick.