I want to create a data validation drop down list using VBA which would reference a single cell in Excel and in that cell is a list that goes something like BGT, FOO, HR, IT. The list is a list of unit codes that each user will have access too and the list changes based on the user access and each user could have access to nothing, 1 unit, or any number of units but I just want the data validation to always read the list in the cell that I point it to. Is this possible in VBA?
I happened to have something laying around on the bench that I could modify
The macro CreateList does the dirty work, but you need to pass it the parameters. SheetName is the name of the sheet on which you want to place the validation. CellName is the cell address on which you want to place the validation, LookupString is the lookup string that is the source of the validation. You can either "assemble" it in code, or in the example, I put it in a named range and read that.
Sub CreateList(SheetName As String, CellName As String, LookupString As String) ' SheetName = Sheet where you want the drop-down list ' CellName = Address of the cell where you want the drop-down Dim Sh As Worksheet ' General pointer to sheets in the workbook Dim shD As Worksheet ' Pointer to the sheet on which you want the drop-down Dim ListString As String ' List of the sheet names ' Initalize variables Set shD = Sheets(SheetName) ListString = "" ' "Remove" the trailing comma from the string ListString = LookupString ' Make the validation With shD.Range(CellName).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListString .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub ApplyLookup() CreateList "Sheet1", "B2", Range("Lookup_List") End Sub
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thanks dflak! Apparently you have helped me on multiple occasions as the forum won't let me add to your reputation. It says "You must spread some reputation around before giving it to dflak again". Just know that I appreciate your help and this worked fantastically and was really easy to follow.
LOL! I wouldn't worry about the rep. I got enough points. I cleaned up what I gave you a bit and added it to my library of modules that I import from time to time. I have over 120 modules, but in reality only use about 10 of them routinely. I have no idea when this one will come in handy.
I am running into a bit of a snag with the above code. I got the code to work but I am struggling to add some additional code that will automatically display the 1st item in the list. Any ideas on how to modify the code to pick the first item in the drop down? Also if I change this from a Sub() to a Private Sub() will that run the VBA when opening the workbook? I am hoping that it runs when the workbook opens and then chooses the first item from the drop down list. Thanks in advance.
Option Explicit Sub CreateList(SheetName As String, CellName As String, LookupString As String) ' SheetName = Sheet where you want the drop-down list ' CellName = Address of the cell where you want the drop-down Dim Sh As Worksheet ' General pointer to sheets in the workbook Dim shD As Worksheet ' Pointer to the sheet on which you want the drop-down Dim ListString As String ' List of the sheet names ' Initalize variables Set shD = Sheets("HOME") ListString = "UnitAccess" ' "Remove" the trailing comma from the string ListString = LookupString ' Make the validation With shD.Range("L20").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=ListString .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Sub ApplyLookup() CreateList "HOME", "L20", Range("UnitAccess") End Sub
Perhaps this
Also just be aware that if your string is longer than 255 characters, you’ll get a corrupt load message when reopening the file and the data validation will be removed.Range("L20").Value = Split(Range("UnitAccess").Value, ",")(0)
Don
Please remember to mark your thread 'Solved' when appropriate.
The simplest way to run the code on workbook open is to go to the VB Editor, double click on ThisWorkBook and paste in.
With the appropriate sheet name, cell name and Lookup_List name.Sub ApplyLookup() CreateList "Sheet1", "B2", Range("Lookup_List") End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks