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]

You could put the items in a range and pass that array to the function

skillsNeeded = UserForm1.ChooseFromList(Sheet1.Range("A1:A46"), Prompt:="Select the nessesary skill")

 

[Arrick]

Thanks, that does the job...

One more "modification" that I would like to make, simply to make the date more presentable...

Instead of clearing the entire sheet1 with:

For i = ActiveWorkbook.XmlMaps.Count To 1 Step -1
ActiveWorkbook.XmlMaps(i).Delete
Next

I would rather clear everything from Row 5 down, and Column A over....


And then, I would like to have a Delete statement right before

ActiveWorkbook.Sheets("Sheet1").range("D1") = Date

to delete the existing data in cell D1...

The goal, is to be able to have a Formatted report with a header that is automatic so it looks nice and etc...

You could put the items in a range and pass that array to the function

skillsNeeded = UserForm1.ChooseFromList(Sheet1.Range("A1:A46"), Prompt:="Select the nessesary skill")