Using Excel Macro to Create a Drop Down List and Hide Cells

DJL_DASH

New Member
Joined
Jul 8, 2011
Messages
16
I am having trouble with a macro that is supposed to perform the following tasks:

Prompt the user to input a cell where the drop down list is to be located
Prompt the user for a range of data to use in the drop down list values
Create drop down list
Hide the rows in which the drop down list values are located

I originally recorded a macro to do this, and then I am tweaking the code accordingly. What is troubling me is getting the macro to select a range from an input box, the using that input to create a list. It's the partcular language included in the .Add command that I do not understand I defined the input box as a range variable celRng, and I am trying to get Formula1: = celRng. But it is not working.

I have attached the code below, and marked in which lines the errors are located:

Code:
Sub CreatDropDownList()
'
' CreatDropDownList Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Dim celNm, celRng As Range
    On Error Resume Next
       Application.DisplayAlerts = False
           Set celNm = Application.InputBox(Prompt:= _
               "Please select a cell to create a list.", _
                   Title:="SPECIFY Cell", Type:=8)
    On Error GoTo 0
       Application.DisplayAlerts = True

       If celNm Is Nothing Then
           Exit Sub
       
       Else
            With Selection.Validation
                .Delete
                On Error Resume Next
                Application.DisplayAlerts = False
                Set celRng = Application.InputBox(Prompt:= _
                    "Please select the range of cells to be included in list.", _
                        Title:="SPECIFY RANGE", Type:=8)
                On Error GoTo 0
                Application.DisplayAlerts = True

                If celRng Is Nothing Then
                        Exit Sub
                Else
                    .Add Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=celRng            'THIS IS WHERE THE ERROR HAPPENS
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                 End If
            End With
    End If
    Range("celRng").Select               'ALSO ANOTHER ERROR HAPPENS HERE
    Selection.EntireRow.Hidden = True
End Sub
I feel as if the bug in this code is very simple and that I am not fundamentally understanding something. Any help is greatly appreciated!

DJL
 

BrandonWLH

Board Regular
Joined
Jun 14, 2011
Messages
97
Have you tried the basic drop down list through data validation?

You could have the user enter the range, then you use Addname and give it a reference.

Then you can Range(ReferenceName).hide

as well as add datavalidation and the data source is also Range(ReferenceName).

Here is some code that will get you most of the way there

Code:
Sub Rectangle1_Click()
     'Defines Categories
    Dim ListName As String
    Dim myCell As Range
    Dim myRange As Range
 
    Set myCell = Worksheets("Categories").Range("B8")
    ListName = "Categories"
    Set myRange = Range(myCell.Offset(0, 0), myCell.Offset(0, 0).End(xlDown))
        'Get the number of items in the drop down list
    ThisWorkbook.Names.Add Name:=ListName, RefersTo:=myRange
End Sub
This code grabs a list of entries from a range until it hits the end of the list. It reference names it. You could easily adapt this for your user input, they select the range, i.e., MyRange.

Then once you have the range named like this

you can just do a datavalidation

targetRange.Validation.Add xlValidateList,,,"=MyReferenceName"
 

DJL_DASH

New Member
Joined
Jul 8, 2011
Messages
16
Okay thanks that looks really good. However, I need the user to input the range. Running to the end of the list won't work for my purposes.
 

BrandonWLH

Board Regular
Joined
Jun 14, 2011
Messages
97
Thats fine, the user is giving you the range so all you need is this line

ThisWorkbook.Names.Add Name:=ListName, RefersTo:=myRange

change myRange to the cell range from the user input.
ListName is the reference name.

Than data validate, hide, done.
 

DJL_DASH

New Member
Joined
Jul 8, 2011
Messages
16
Thats fine, the user is giving you the range so all you need is this line

ThisWorkbook.Names.Add Name:=ListName, RefersTo:=myRange

change myRange to the cell range from the user input.
ListName is the reference name.

Than data validate, hide, done.
Is this line in reference to your code or mine? I am inexperienced with visual basic, so I appreciate your patience...
 

BrandonWLH

Board Regular
Joined
Jun 14, 2011
Messages
97
This might kick out an error for you since the full error catching stuff isnt in there, just checks for nothing, but this is a much cleaner code.

Code:
dim strRange as string
strRange = "DataRange"
dim celNm as range
dim celRng as Range
 
Set celNm = Application.InputBox(Prompt:= _
               "Please select a cell to create a list.", _
                   Title:="SPECIFY Cell", Type:=8)
 
If celNm Is Nothing Then Exit Sub
 
Set celRng = Application.InputBox(Prompt:= _
                    "Please select the range of cells to be included in list.", _
                        Title:="SPECIFY RANGE", Type:=8)
 
If celRg Is Nothing Then Exit Sub
 
'user defined data range is now called strRange, refer to it as Range(strRange) 
ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng
 
'format the refernce name for use in Validation.add
strRange = "=" & strRange
 
'Add the drop down list to the target range using the list range
celNm.Validation.Add xlValidateList,,,strRange
 
'hide the range where the list came from
celRng.hide
Now if you need more than 1 list like this done in the same workbook, we'll need to change this as its uses a reference name and they will overwrite themselves, but thats simple, we just add the time to the reference name so it makes it always unique.
 
Last edited:

DJL_DASH

New Member
Joined
Jul 8, 2011
Messages
16
Thanks! This looks much better, I guess all of those .commands were just extraneous code record macro added. There are two, errors however. The second to last "paragraph" of code is reporting an "application define or object defined error"

In the last "paragrahp", celRng.Hide reports that celRng object does not support this method. Should I somehow convert this to another type of object(or convert the selection into rows), or is there another command I can use on this?
 
Last edited:

BrandonWLH

Board Regular
Joined
Jun 14, 2011
Messages
97
Fixed, forgot to delete previous validation. here is adjusted code. Also found a typo in a var name. tested i this a few times, good to go.

Code:
Dim strRange As String
strRange = "DataRange"
Dim celNm As Range
Dim celRng As Range
Dim holder As Variant
 
On Error GoTo pressedCancel:
Set celNm = Application.InputBox(Prompt:= _
                "Please select a cell to create a list.", _
                   Title:="SPECIFY Cell", Type:=8)
 
If celNm Is Nothing Then Exit Sub
 
Set celRng = Application.InputBox(Prompt:= _
                    "Please select the range of cells to be included in list.", _
                        Title:="SPECIFY RANGE", Type:=8)
 
If celRng Is Nothing Then Exit Sub
On Error GoTo 0
 
'user defined data range is now called strRange, refer to it as Range(strRange)
ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng
 
'format the refernce name for use in Validation.add
strRange = "=" & strRange
 
'Add the drop down list to the target range using the list range
celNm.Validation.Delete
celNm.Validation.Add xlValidateList, , , strRange
 
'hide the range where the list came from
celRng.EntireRow.Hidden = True
pressedCancel:
 
Last edited:

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top