Creating userforms in Microsoft Excel is great in helping users navigate through their choices and help them achieve their desired outcome. I create customized userforms to help me organize my information and get work done faster. But when creating listboxes in the userforms, I don’t like the old fashion way of using my mouse to highlight and select my choices.
I don’t want to get off my keyboard and move my hand towards the mouse to select and then get back to the keyboard. I want to be able to rapidly type in my information and then highlight my choices of similar properties for having my tasks done quickly. So, I turned that around by using a textbox above a listbox containing my choices.
The textbox is linked to a keypress event where every character I type into the box, that input from the box will search through every choice and then highlight the choices having similar characters to my text input. The more specific characters I put in, the more the specific values will be highlighted and selected for my task input. The great thing about my text input is it is not case sensitive, so it will not add a burden to my choosing and other users when typing to make their choices. The characters can mixed case and it will still search through all values.
I don’t have to bother using my mouse to choose similar values for me from a list unless, I want to choose different values, then I will have to use the mouse to make the more diverse choices. In any case, this type of process can speed up selection through typing
This video shows how it is done in my type of process and how you can make yours:
The codes that run my usersform, these are the lines of codes:
**************************************************************
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub
Private Sub CommandButton2_Click()
b = ThisWorkbook.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
t = ThisWorkbook.ActiveSheet.Cells(Rows.Count, b + 5).End(xlUp).Row
For m = 0 To UserForm1.ListBox1.ListCount – 1
If UserForm1.ListBox1.Selected(m) = True Then
ActiveSheet.Cells(t, b + 5) = UserForm1.ListBox1.List(m)
t = t + 1
End If
Next m
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
a = UserForm1.TextBox1
If a = “” Then
GoTo re
End If
a = UCase(a)
For v = 0 To UserForm1.ListBox1.ListCount – 1
d = UserForm1.ListBox1.List(v)
d = UCase(d)
m = InStr(1, d, a)
If m > 0 Then
UserForm1.ListBox1.Selected(v) = True
Else
UserForm1.ListBox1.Selected(v) = False
End If
Next v
re:
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
End Sub
Private Sub UserForm_initialize()
j = ThisWorkbook.ActiveSheet.Cells(1, 1).Width
v = “118;” & “145;”
gs = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For t = 1 To gs
UserForm1.ListBox1.AddItem ActiveSheet.Cells(t, 1)
Next t
UserForm1.TextBox1.SetFocus
End Sub
**************************************************************
**************************************************************
Sub userform1_new()
Load UserForm1
UserForm1.Show
End Sub
**************************************************************
Note: After you paste these codes into the module of Excel VBA, then you have to assign a keyboard shortcut. The first step to do that is go to the VIEWS tab and click a button where it says MACROS. Click on “View Macros” and if you see the name userform1_new, select that and click on the OPTIONS button. Then, you will see a blank box where you can assign a letter. If you put in b in the box, then the shortcut will be Ctrl+b. If you put in c, the shortcut will be Ctrl+c and so on. Choose any letter you want and then click ok. Click cancel on the next dialog box and press the shortcut key you assigned. It will begin and now you will be able to move to your work tabs faster.
Connect with me:
https://twitter.com/trickoutexcel
https://www.facebook.com/ExcelQualityProgramCustomize34?ref=aymt_homepage_panel
Watch my other videos:
https://www.youtube.com/channel/UCdhZjVbM-pS3sL1iqtjyDgQ
See my professional story:
https://www.linkedin.com/in/towsifchowdhury
Pay for my services:
https://www.fiverr.com/towcal/accelerate-excel-work-through-macros-and-vba
See my other articles:
https://teachexceltrick.wordpress.com/
Please leave comments on how the macros helped you out.