+ Reply to Thread
Results 1 to 2 of 2

Using VBA to Create a Drop Down List and Hide Cells

Hybrid View

  1. #1
    Registered User
    Join Date
    07-08-2011
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Using VBA to Create a Drop Down List and Hide Cells

    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

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,926

    Re: Using VBA to Create a Drop Down List and Hide Cells

    Try:
    1. Formula1:="=" & celRng
    2. Range(celRng).Select
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1