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

[DJL_DASH]

Hello again!
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

 

[Stoneboysteve]

DJL,

It looks to me like you open the 'With Selection Validation' statement without closing it before the If statement.
Try adding End With before the If statement above your error, then add another 'With' Selection Validation' after the Else statement.

You can also put msgbox = celRng after the else statement to check the variable value before the error.

Stoneboysteve

 

[DJL_DASH]

Okay here's the new code:

        Dim strRange As String
        strRange = "DataRange"
        Dim celNm As Range
        Dim celNm2 As Range 'use only if necessary
        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
        
        'Inserts a copy of the row where the drop down list is going to be
        celNm.EntireRow.Copy
        celNm.EntireRow.Insert
        
        'moves the cell to the appropriate location
        celNm2 = Range(celNm).Offset(-1, 0).Select  
    
        
        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
         
        
        'celNm.Offset(-1, 0).Select
        
        'Add the drop down list to the target range using the list range
        celNm2.Validation.Delete
        celNm2.Validation.Add xlValidateList, , , strRange
         
        'hide the range where the list came from
        celRng.EntireRow.Hidden = True
        
pressedCancel:

I have gotten it about where I want it only there is one problem. The drop down list ends up hidden along with the selected cells. Note: when I select the cell where the drop down list is going to be placed, I will also highlight a range of values to be included in the dropdown list that, will encompass that same row (but a different column).

My goal is to:
Select a cell where drop down list is to be placed
Select a range which lists values to be included in the drop down list
Insert a copy of the entire row of where the drop down list is to be placed, above
Move selection of where drop down list is to be placed upwards, such that it is located in the new row
Hide all rows included in the range (note this does not include the row newly created).

My dilemma is that I just don't know how to do this. I already have a named range (that is defined by the macro not by the worksheet) as celNm.

I have previously tried using celNm.offset(-1,0).select , but that does not work either.

Can anyone give me any hints?