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

[DJL_DASH]

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:

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]

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

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]

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]

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]

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]

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.

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.

 

[DJL_DASH]

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?

 

[BrandonWLH]

faux paux

celRng.hidden = true

 

[DJL_DASH]

still giving me an error at Validation.Add

 

[BrandonWLH]

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.

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: