Please need VBA code: Select records from drop down list

jduck

Board Regular
Joined
Nov 9, 2006
Messages
63
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jduck

Board Regular
Joined
Nov 9, 2006
Messages
63
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Board Regular
Joined
Nov 9, 2006
Messages
63

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Board Regular
Joined
Nov 9, 2006
Messages
63

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top