Please need VBA code: Select records from drop down list

[jduck]

Can someone please help with a bit of code. I'm new to the VBA stuff and have an immediate need that I'm not able to figure out just yet.

I have a spreadsheet that contains multiple rows per user. I need a combo or list box that pops up asking them to select their name from the drop down list. The list would be based on column A and would only include their name once.

When they select their name, the macro would then open the Form option from the Data menu, the Criteria button would be pushed and their name entered into the form, to return the first record their name appears on, then they could forward through the records, and update using the Next record button.

Any help would be greatly appreciated, as I'm just not getting the hang of this stuff very quickly. I'm studying as vigerously as possible. The learning curve just isn't meeting this need quickly enough.

 

[stanleydgromjr]

jduck,

Have you considered using Data, Filter, AutoFilter?

Have a great day,
Stan

 

[jduck]

Thanks Stan,
I've thought of that, but is there a way to have a combobox or other control that could contain the auto filter results?

 

[Joe Was]

This code will Make an In-Cell-Validation-Drop-Down List or update the current list in a cell you indicate, with Sorted Unique Items from a Data Source you indicate.

Note: Currently the data list and the drop-down are on the same sheet, but this can be changed in the code.

Sub MakeDDList()
'Standard module code, like: Module1.
Dim strCol$, strDDLCell$, strMyList$, strSheetNm$
Dim lngBotOfList&, lngColNum&, lngRowToStartWith&
Dim objDataRng As Object, objUniqueList As Object

'**** Start: User Defined, SetUp Parameters *******************************************
'The Sheet's Name containing the items for the unique list?
strSheetNm = "Sheet2"

'The column containing the items for the unique list?
strCol = "A"

'The First Row of the items list?
lngRowToStartWith = 2

'The Cell that gets the Drop-Down List?
strDDLCell = "B1"
'**** End: User Defined, SetUp Parameters *******************************************

Application.ScreenUpdating = False

lngColNum = Sheets(strSheetNm).Range(strCol & ":" & strCol).Column
lngBotOfList = Sheets(strSheetNm).Range(strCol & "65536").End(xlUp).Row

Set objDataRng = Sheets(strSheetNm).Range(Cells(lngRowToStartWith, lngColNum), Cells(lngBotOfList, lngColNum))

Sheets.Add
ActiveSheet.Name = "TempList"

objDataRng.Copy Destination:=Worksheets("TempList").Range("A1")

Worksheets("TempList").Columns("A:A").Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Set objUniqueList = Sheets("TempList").Range("A1:A" & Sheets("TempList").Range("A65536").End(xlUp).Row)

For Each cell In objUniqueList
strCellVal = cell.Value
If (strCellVal <> "" And strCellVal <> strMyTest) Then strMyList = strMyList & cell.Value & ","
lngItem = lngItem + 1
strMyTest = Sheets("TempList").Range("A" & lngItem).Value
Next cell

strMyList = Left(strMyList, Len(strMyList) - 1)

Application.DisplayAlerts = False
Sheets("TempList").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

On Error GoTo myRS
With Sheets(strSheetNm).Range(strDDLCell).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strMyList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

myRS:
Sheets(strSheetNm).Range(strDDLCell).Activate
End Sub

 

[jduck]

Well, thanks...
Without renaming my sheet to "Sheet2", it would Run Time Error out on
lngColNum = Sheets(strSheetNm).Range(strCol & ":" & strCol).Column

After changing the worksheet name to Sheet2... Excel has crashed 3 times now... Anything in particular I'm doing wrong?

 

[Joe Was]

That is odd it works on all my systems here?
I tested it on Excel 2000, 2002 & 2003 XP.

Did you Insert a Standard code module, like: Module1, not a Sheet Module, like: Sheet1 and paste the code there?

Is the list of items on the sheet that you coded the variable: strSheetNm to in the Sub, that same sheet name?

Did you enter a cell address in: strDDLCell for the dropdown that is on the same sheet name that you put in the variable: strSheetNm?

If in your Test Workbook you have a sheet named "TempList" delete it!

Do you have any Event Code?
This Sub Adds a temp worksheet to play with the list, so it may have a problem if you have some Events?

If it does not work, still:

From the VBA Editor select that sub's name and use "F8" to step through the code to see where it is having a problem.

 

[jduck]

Did you Insert a Standard code module, like: Module1, not a Sheet Module, like: Sheet1 and paste the code there? Yes

I do not yet have any event code. I believe the problem is that I don't know exactly where to make adjustments to the code you've provided to make it properly interact with the spreadsheet that I have. As in, being as remedial as I am, I don't know where to make the changes to the code for sheet names, cell references, etc.

 

[Joe Was]

The code tells you how to make these changes:

'**** Start: User Defined, SetUp Parameters *******************************************
'The Sheet's Name containing the items for the unique list?
strSheetNm = "Sheet2"

'The column containing the items for the unique list?
strCol = "A"

'The First Row of the items list?
lngRowToStartWith = 2

'The Cell that gets the Drop-Down List?
strDDLCell = "B1"
'**** End: User Defined, SetUp Parameters *******************************************

These current settings say:
For the Raw Data List of Items: Use Sheet2, Column A, with the first item of my list in Row 2.
Then load the Unique list of items Drop-Down list into Sheet2 Cell B1.

You only need to make any number of these four possible changes.

The rest is automatic.
An optional change would be if you want your raw list of items on a different sheet than the Drop-Down List.

To have the DropDown on a Sheet different that the Raw List of Items, change the highlighted name, in the bottom code to the name of that other Sheet!

On Error GoTo myRS
With Sheets(strSheetNm).Range(strDDLCell).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strMyList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

myRS:
Sheets(strSheetNm).Range(strDDLCell).Activate

So if you wanted the Dropdown on Sheet1 rather than the same Sheet as the Items List, then:

strSheetNm

would be changed to that Sheet's name, like: "Sheet1" [with the quotes included], so one change would be:

Sheets("Sheet1").Range(strDDLCell).Activate

 

[stanleydgromjr]

Joe,

Very nice.

Thanks, I can use this routine.

Have a great day,
Stan

 

[Joe Was]

Stan, Did you try it?
Did you have any problems?
The original poster has not been able to get it to work and I cannot re-create the problem?