September 09, 2010

Excel VBA: Multi-selection Listbox using pop-up window to display column values

Description:

We have following excel sheet with the two columns Names and Requirednames.
Names column has all the names
Required Names column should have some selected name(s)

Names Required Names
ABC,DEF,GHI  
   
PSR,DOT,COM  


What we can do other than copy and paste?

We can create a pop-up window which will display all the names by using list box and we can select the required names from that list box.

Procedure:
1.Go to Developer Tab -> Design Mode
2.Click on Insert and click -> CommandButton
3.Drag the cursor to create a button
4.Rename button with name "Show Names" in sheet
5.Assign a macro to it with name "DisplayNames"
6.Open VBEditor
7.Goto Insert -> User Form
8.We will get one form, Design the form as follows
9.
UserForm1



10.Change Caption to "Names Selection" in Form Properties
11.Change Caption to "List of Names" in Frame Properties
12.Change Caption to "Select the names and click OK" in Lable Properties
13.Change Name to "lbEmp", MultiSelect to 1-fmMultiSelectMulti in ListBox Properties
14.Change Name to "submit" and Caption to "OK" in Button1 Properties
15.Change Name to "close" and Caption to "Close" in Button2 Properties
16.Change Name to "reset" and Caption to "Reset" in Button3 Properties
17.Select Excel window, Go to Macros and Select "DisplayNames" macro(which is already assigned to the Button in Sheet), Click on Edit
18.Write the following code:
Dim rngEmp() As String
Dim emps As String
Dim i As Integer

Application.Sheets("Sheet1").Select
emps = ActiveCell.Offset(0, -1).Value
If emps = "" Then
MsgBox ("No Name(s) Available")
End
Else
rngEmp = Split(emps, ",")
End If

For i = LBound(rngEmp) To UBound(rngEmp)
UserForm1.lbEmp.AddItem rngEmp(i)
Next

UserForm1.Show
19.
Double-click on the UserForm1 from the Forms directory in VBEditor
20.
Double-click on any one of the button on the form to view code(shortcut: F7)
21.
Select all the code and delete it. Paste the following code

Private Sub close_Click()
Unload UserForm1
End Sub
Private Sub reset_Click()
UserForm1.lbEmp.Clear
End Sub
Private Sub submit_Click()
Dim emps As String
emps = ""
For i = 0 To UserForm1.lbEmp.ListCount - 1
If lbEmp.Selected(i) = True Then
'MsgBox lbEmp.List(i)
If emps = "" Then
emps = lbEmp.List(i)
ElseIf emps <> "" Then
emps = emps & "," & lbEmp.List(i)
End If
End If
Next i
ActiveCell.Value = emps
End Sub
21.Save and Close the VBEditor. Goto Excel sheet
22.Select the right adjacent Cell say B1 Cell
23.Click the button which we created on the sheet
24.Select the name(s) and Click Ok to see values in the selected cell
25.We can select Multiple Names at a time which will display names with the delimiter "," in the selected cell
26.If A's cell doesn't contain any value then it will display a message "No Name(s) Available"
27.We have to select Close to close the pop-up window
28.We can select Clear to clear list and then select close to cancel

You can see the sample output

Names Required Names
ABC,DEF,GHI ABC
   
PSR,DOT,COM PSR,COM

Note:
For any further queries and/or suggestions please contact psrdotcom@gmail.com
Post a Comment

Featured Post

Java Introdcution

Please send your review and feedback to psrdotcom@gmail.com