Tick boxes in a drop down list

Adnan_razaq

New Member
Joined
Nov 14, 2019
Messages
11
Hi All,

I need to create a drop down list with check boxes if possible.

What also need to derive from the values selected from the check boxes is whether the UK only, non UK and all region products were selected. There will a total of 20 products in the drop down list.

The derivation of regional product will be in a separate box.

Many thanks in advance
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Please provide the data that would populate this drop down. Is it going on a UserForm?
 
Upvote 0
Yes, it will be user form.

the following items will be required:


Continental Europe
Emerging Countries
Global Properties
Japanese
North American
UK
UK 100

Many Thanks
 
Upvote 0
There aren't 20 there, so is the idea that you want to multi-select?

Why not use a list box?
 
Upvote 0
Hi,

There will be 20, I just shared a sample of the 20.

By using a list box will I be able to derive a separate field which will recognize if I selected UK, NON UK or all options within the box?

If so, how can this be done?

Thanks
 
Upvote 0
Yes, you'd need 2 columns - though you can hide one.

What would you want for One UK option, one Non-UK option, but not all have been selected?
 
Upvote 0
Maybe:
VBA Code:
Private Sub UserForm_Initialize()

    With Me.ListBox1
        .ColumnCount = 2
        .ColumnWidths = ListBox1.Width - 20 & ";0"
        .MultiSelect = fmMultiSelectMulti
        .ListStyle = fmListStyleOption
        .List = Sheets(1).Range("a1").CurrentRegion.Value2
    End With

End Sub


Private Sub CommandButton1_Click()
    
    Dim itemsSelected   As Long
    Dim UK              As Boolean
    Dim NONUK           As Boolean
    
    Dim message         As String
    
    Dim x As Long
    For x = 0 To Me.ListBox1.ListCount - 1
        If ListBox1.Selected(x) Then
            Select Case ListBox1.List(x, 1)
                Case "NONUK": NONUK = True
                Case "UK": UK = True
            End Select
            itemsSelected = itemsSelected + 1
        End If
    Next x
    
    If UK And NONUK Then
        message = "BOTH"
    ElseIf UK Then: message = "UK"
    ElseIf NONUK Then: message = "NON UK"
    Else: message = "Nothing selected"
    End If
    
    MsgBox message
    
    
End Sub

Assuming data like this:

Continental EuropeNONUK
Emerging CountriesNONUK
Global PropertiesNONUK
JapaneseNONUK
North AmericanNONUK
UKUK
UK 100UK
 
Upvote 0
Hi,

Slightly changed the format of my UI.

I have now created a drop down list where users can select UK, NON UK and all lists.

A separate drop down currently displays the list of reports selected above i.e. if UK is selected, the list box below will display UK and UK 100

What I need to do now is in the above list create a bespoke option. So if the users want to select, for instance UK 100 and North American reports only, this will give the user option to do so by opening up another drop down and be able to select the reports required?

For stage 2 of this process I would like to attach the each of the reports be it UK, non UK all or bespoke reports to an outlook item looking at cell F14 for the email address.

Many Thanks
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top