List Combox in VBA EXCEL (Macro or any other help)

abdullahsikandar

Board Regular
Joined
Feb 10, 2014
Messages
52
Hi Guys,
I need your help in making a macro or any trick where i can have list of checkboxes and when i select any checkbox from there so the value or the label or that checkbox automatically added in the cell of excel.
There can be two ways we can do that:
1) we can make a macro where when we click on that cell in excel the "listbox of checkbox" will be open and we can select a bunch of checkboxes and there values will be automtically added into that cell.( is this possible?)?
2) We can make on user forum and when we click on the cell that "User forum" will be open and once we select our checkboxes their values will be added automatically into that cell?
Is it possible? If yes, so, Kindly advice me i am new in in Vb and trying to make this project ... Please help me.
Thank in advance.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Hi, and welcome to the forum.

Rather than use multiple check boxes you may be better off using a multi select ListBox.

Start a new instance of Excel.
Press Alt+F11 to open the vba editor window.
Click Insert => UserForm
From the toolbox, select and draw a ListBox onto the user form.
Right Click the ListBox, select properties, and set MultiSelect = frmMultiSelectMulti
From the toolbox, select and draw a CommandButton onto the user form.

Right Click the form and select View Code.
We place values into the ListBox from the form's Initilalize Event.
Copy, paste and edit the code below.
Rich (BB code):
Private Sub UserForm_Initialize()
   With Me.ListBox1
      .AddItem "A"
      .AddItem "B"
      .AddItem "C"
   End With
End Sub

The code behind the CommandButton loops through the Listbox entries and builds up an output string of the selected items. This is written back to the cell and the form is unloaded.

Copy and paste the code below:
Rich (BB code):
Private Sub CommandButton1_Click()
   Dim txt As String
   Dim i As Long
   
   'loop through the list box items and build up output string of selected values
   For i = 0 To Me.ListBox1.ListCount - 1
      If Me.ListBox1.Selected(i) Then
            txt = txt & Me.ListBox1.List(i) & " "
        End If
   Next i
   
  'update worksheet and unload the form
  ActiveCell = Trim(txt)
  Unload Me
End Sub

For the purposes of this example we want the form to load when the user selects a cell in column A of Sheet1.
In the Project window on the left hand side, double click on the Sheet1 module.
Copy and paste the code below:
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
   'only trigger on column A
   If Target.Column <> 1 Then Exit Sub
   
   Load UserForm1
   UserForm1.Show
End Sub

Hope this gives you some ideas,
Bertie
 

abdullahsikandar

Board Regular
Joined
Feb 10, 2014
Messages
52
Thanks a lot for your help, I have another question for you. Can you help me in that also please.

there are 5 rows and 5 columns, we have a button which checks for null values with respect to a specific column (here it should check with respect to ID and it shouldn`t leave any null values in the Date, Name, Age and the user should be able to give the null values for the cities.)
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
For future reference: if you are starting a new topic it is best to start a new thread. New threads tend to get more views, and therefore, a better chance of a speedy reply. It would also be a great help if you could provide some sample data, and specify which columns/rows you are working with.

As for your query, see if the link below helps.
Excel VBA Interactive Userform - Easy Excel Macros
 

abdullahsikandar

Board Regular
Joined
Feb 10, 2014
Messages
52

ADVERTISEMENT

Hi,

I am trying to find the way to attach the file but i didn`t know how to attach the file here.

Let me tell you the description there are 5 columns and 5 rows where the data is being filled for example A3 and C4 is empty and when we click the button for check validation it must show the message box that A3 or "Row Name" is empty. It shouldn`t show error for "C4", this means we need to check for null validation for specific column which is "A" and for rest of the columns excluding column C.


I hope that will help you to understand what we need i also made new thread for this and thanks again for your help.
 

abdullahsikandar

Board Regular
Joined
Feb 10, 2014
Messages
52

ADVERTISEMENT

Thanks for your help quick question can we do it for 2 columns at one time?
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Thanks for your help quick question can we do it for 2 columns at one time?

There are many ways to restrict what triggers a Change Event, e.g.,

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
   'enter column number which triggers user form
   If Target.Column <> 1 Or _
      Target.Column <> 2 Then Exit Sub
   
   Load UserForm1
   UserForm1.Show
End Sub

For the above example you could put:
If Target.Column > 2 Then Exit Sub
 

abdullahsikandar

Board Regular
Joined
Feb 10, 2014
Messages
52
Thanks a lot for your help, I have another question, I did this thing and working perfectly fine .

What i am doing right now ...

in my listbox there are 252 countries and i want if i select Australia so in the column it will come like AUS or if i select India it will IN... Is it possible that i would declared any where the shortcuts of Countries and whenever we select any country from the list and the abbrevation will occur in the cell?


Thanks in advance
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Create a user form with a list box and play about with this code:

Rich (BB code):
Option Explicit


Private Sub UserForm_Initialize()
   With Me.ListBox1
      .AddItem "Australia"
      .AddItem "India"
   End With
End Sub


Private Sub ListBox1_Click()
   MsgBox UCase(Left(Me.ListBox1.Value, 3))
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,822
Members
409,839
Latest member
akashsadhu
Top