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

 

[DJL_DASH]

Thank you so much!
One more addition I would like to add to this macro that seems a bit more complicated than I first realized. Can we have this program insert a row where the cell with the drop down list is going to be created. Only the Program will then copy the row in which the cell exists above itself. I tried adding this code after line 12 but it just exits out of this program:

'Inserts a copy of the row where the drop down list is going to be
    celNm.EntireRow.Copy = True
    celNm.EntireRow.Insert = True
    celNm.EntireRow.PasteSpecial = True

 

[BrandonWLH]

They aren't booleans. Just copy, insert. No need to paste as well. insert is a paste operation.

celNm.EntireRow.Copy
celNm.EntireRow.Insert

 

[DJL_DASH]

Okay now what I need to do is move the selection of celNm upwards. Otherwise the list gets hidden each time I do this. What is the appropriate command to do this?

 

[BrandonWLH]

Just relocate the the Copy/Insert.

I put it right before the validation and it wasnt hiding the drop down list, unless you put the drop down list in the same rows as the list of value.

You may want to have the drop down list lists on a separate sheet just to keep it clean. I do that, I have a automated drop down list sheet generator. I just fill in lists and it auto creates all the named references for the lists. We could make the code make a sheet called DDL appear when you run the code, you put the lists and the list range you select there and when its done, it rehides the DDL sheet so it leaves you main front page uneffected by hidden cells.

You would just take a sheet, name it DDL for simplicity. Then in the code, at the start, Worksheets("DDL").Hidden = False and righrt before you select the data range you do a Worksheets("DDL").Activate to get to it, then Worksheets("Sheet1").Activate or what ever ur 1st sheet is called to go back to the main page, and at the end of the code, make Worksheets("DDL").Hidden = true and remove the hiding of the range celRng.

 

[DJL_DASH]

For my purposes this has to be on the same sheet. My code is still creating the drop down list as part of one of the cells that are hidden. Would it be possible to change the value of celNm so it is shifted up one row after the copy is inserted? That way, the Validation will be in its proper place...

 

[DJL_DASH]

I think one thing that I assumed that was not part of your assumption is that celRng will be selected on the same row as celNm. The reason why I am creating a copy of the row above it is so that the Validation will not be hidden. The row above is also necessary because it is going to appear differently then the rows that are part of the list.