Replace Excel's Data Validation Drop-down with Smart Userfrom Based Mini Tool

Creating Dropdown from the Validation part of the excel is very easy and very fundamental thing we should know as an Excel Enthusiastic.

When our project required the same list of things to be chosen from the single cell usually make things done with creating the drop-down list from the Data Validation Tool which is inbuilt in Excel.

Now, Sometimes you might have faced or not, but each cell of the column requires the same list of things for selection. I was usually ending up by drag down the dropdown list to the nth row which could be messy sometimes. and it even no looks cool. and you even can't put things apart from the list.

What I have done here is, You don't require a dropdown list at all. Just see the video and you will get what I have done.

The advantage of this tool is you don't need to create hundreds of dropdown in the same column when everything is available to you in just a double-click.

To compile this tool I have required following two scopes.

1. Worksheet Code
2. Userform Design and Codes

You can design the userform and it looks easy not a big deal.

Now let's move towards coding of Worksheet
======
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    If Target.Column = 2 Then
        LISTBOX.Show
    End If
    
End Sub
======
I have used BeforeDoubleClick Event to initiate the things.

Now, as we double click on the cell on column two, the Sheet Code will sense it and trigger the event and will force userform to appear.

More to the Second part of the coding

=====
Private Sub UserForm_Initialize()

    Application.ScreenUpdating = False
    
    Dim DB As Worksheet
    Set DB = Sheets("DATABASE")
        
    Dim I As Long
    I = 1
    
    LISTBOX.ListBox1.Clear
    
    Do Until DB.Cells(I, 2).Value = Empty
        LISTBOX.ListBox1.AddItem DB.Cells(I, 2).Value
    I = I + 1
    Loop

    Application.ScreenUpdating = True

End Sub
=====

(Above code will take each item of the list from the Place called Database Sheet where you have listed all the details over there with Heading.)

Now DOuble Click even to input userform data into the sheet which the same part of userform coding

======
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    With LISTBOX.ListBox1
        If .ListIndex = -1 Then Exit Sub
        ActiveCell.Value = .List(.ListIndex)
        Cells(ActiveCell.Row, ActiveCell.Column + 1).Activate
        Unload LISTBOX
    End With
    
End Sub
======

Now Coding for the Add New Button. this coding will add Item to the list.

Private Sub CommandButton1_Click()
    
    Dim I As Variant
    Dim J As Variant
    
    If ActiveCell.Column = 2 Then
        I = VBA.InputBox("Please Enter New Tail No.:", "Add New Tail No. in The Database")
        If I = vbNullString Then
            Exit Sub
        Else
            J = NewRow("DATABASE", 2)
            Sheets("DATABASE").Cells(J, 2).Value = I
            Dim DB As Worksheet
    Set DB = Sheets("DATABASE")
     
    I = 1
    
    LISTBOX.ListBox1.Clear
    
    Do Until DB.Cells(I, 2).Value = Empty
        LISTBOX.ListBox1.AddItem DB.Cells(I, 2).Value
    I = I + 1
    Loop
        End If
  end if 

end sub
======

So This is it. Please Try. and create more ways to use it in a more effective way.

Thank you

- Kamal Bharakhda

https://www.youtube.com/watch?v=hAEL7TeqOnQ