VBA to Call Drop Down List

Magdoulin · Mar 9th 2018

Guys, what is the way to define drop down list (validation data) in VBA?

I mean, I have a sheet with many drop down lists, and I’d like to refer to each one in VBA code trying to enlarge the list font size, there is no other way to do it through Excel Programme directly, and the only probable solution left is to try VBA somehow, any suggestions?

chirayuw · Mar 9th 2018

Data Validation lists are basically just cells from where you can select your value. So the way to change the font size is the same way as you would normally from the home tab in Excel. So you can either record a macro & modify it or use below code sample & modify it

Code

Sheets("Sheet1").Range("A1").Font.Size = "11"

Magdoulin · Mar 10th 2018

I'm afraid they’re not the same
This’ll be applied on the selection you make from the drop down menu
But for the menu elements (in the list itself), this code won’t help

And basically I need the items of the list itself when you get the drop down shown, to be with bigger font

BaraaKhalil · Mar 10th 2018

May be something like that

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Skipper
    Dim x As Long
    
    x = 100
    If Target.Validation.Type = xlValidateList Then x = 130
Skipper:
    ActiveWindow.Zoom = x
End Sub

Magdoulin · Mar 10th 2018

But this will zoom in the whole sheet
Not good for my target
I need something working on the list element font size itself
I googled a lot to find something like that
But all I found was something working on zooming not the font size itself

BaraaKhalil · Mar 10th 2018

May be slight change will solve it

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Skipper
    Dim x As Long
    
    x = Target.Font.Size
    If Target.Validation.Type = xlValidateList Then Target.Font.Size = 15: Exit Sub
Skipper:
    Target.Font.Size = x
End Sub

BaraaKhalil · Mar 10th 2018

And if want to do that in one shot you can test that code

Code

Sub Test()
    Dim rngLists    As Range
    Dim listCell    As Range


    On Error Resume Next
        Set rngLists = Sheets(1).UsedRange.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0


    If Not rngLists Is Nothing Then
        For Each listCell In rngLists.Cells
            listCell.Font.Size = 15
        Next listCell
    End If
End Sub

Display More

Magdoulin · Mar 11th 2018

None of them worked
Maybe I'm doing something wrong
Just to confirm, the 1st code I have tried it putting it in seperated Module and in the same sheet, none worked
the second one, I'm not sure if I've adjusted it in a right way
The shee name I'm working on is H
And the list is located in Cell D10
And I needed font 20 for the list elements
I've tried the below adjustment but it didn't work
do I do something wrong?

Code

[B]Sub Test()[/B]
[B]Dim rngLists As Range[/B]
[B]Dim listCell As Range[/B]


[B]On Error Resume Next[/B]
[B]Set rngLists = Sheets(H).D10.SpecialCells(xlCellTypeAllValidation)[/B]
[B]On Error GoTo 0[/B]


[B]If Not rngLists Is Nothing Then[/B]
[B]For Each listCell In rngLists.Cells[/B]
[B]listCell.Font.Size = 20[/B]
[B]Next listCell[/B]
[B]End If[/B]
[B]End Sub[/B]

Display More

Code

[B]Sub Test()[/B]
[B]Dim rngLists As Range[/B]
[B]Dim listCell As Range[/B]


[B]On Error Resume Next[/B]
[B]Set rngLists = Sheets(H).D10.SpecialCells(xlCellTypeAllValidation)[/B]
[B]On Error GoTo 0[/B]


[B]If Not rngLists Is Nothing Then[/B]
[B]For Each listCell In rngLists.Cells[/B]
[B]listCell.Font.Size = 20[/B]
[B]Next listCell[/B]
[B]End If[/B]
[B]End Sub[/B]

Display More

BaraaKhalil · Mar 11th 2018

The code is put in standard module ... If the sheet name was H then when referring to the sheet, it should be Sheet("H")
and if you want to deal with column D you can refer to it
Try this modified code

Code

Sub Test()
    Dim rngLists    As Range
    Dim listCell    As Range


    On Error Resume Next
        Set rngLists = Sheets("H").Columns(4).SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0


    If Not rngLists Is Nothing Then
        For Each listCell In rngLists.Cells
            listCell.Font.Size = 15
        Next listCell
    End If
End Sub

Display More

**KjBox** · Mar 11th 2018

The font size of a validation list cannot be changed. A workaround is to use an ActiveX ComboBox.

Magdoulin · Mar 12th 2018

the thing is that they are 4 dependant lists, not really sure how to manage this with the combo box

sarisri7 · Mar 12th 2018

Dear Ms.Magdoulin,

to get away from all these problems, I would suggest you pls upload the xl file in this forum to get perfect solution.

sarisri7

Magdoulin · Mar 12th 2018

Well, what about trying to replace the whole thing with Combo Boxes?

I’ve attached sample sheet which is prototype of my original sheet
As you should see, I need the 1^st Combo Box to include the options of X/Y/Z
And the 2^nd one to include A/B/C or D/E/F or G/H/I, depends on the 1^st selection
And the 3^rd & 4^th Combo Boxes to work the same way

Ingo_Ingo · Mar 12th 2018

Hi,

Why you need the 4^{[SIZE=10px]th[/SIZE]} ComboBox? Here is a simple cell in which it will appear - regardless of the previous selections, of the 3 ComboBox - Red, Yellow or Green.
Then, you need a solution with formula or code VBA?

Magdoulin · Mar 13th 2018

Well, this sheet is way simplified, so maybe the purpose of the last combo box isn’t that clear
However, the original one isn’t like that 100%
It’s way complicated and the options aren’t unified with the 4^th combo box
I need just the technique

chirayuw · Mar 13th 2018

Something like this?

[ATTACH]n1200891[/ATTACH]

Magdoulin · Mar 13th 2018

Well first of all, let me salut you, you're such an elegant person, your sheets speak loudly and says so

Well, now there's a little issue, it seems that this method doesn't let the Combo Box show all the elements which are supposed to be there

As I confirmed, the attached is just a prototype, the original sheet is quite different, the combo boxes number of elements isn't identical all the time

I've adjusted your sheet with my original sheet elements, in the "Lists" sheet, as you could see the Combo Boxes don't work properly anymore

Could I ask for your kind assistance here? What's wrong I'm doing?

chirayuw · Mar 14th 2018

The reason that not all of the options are showing in your combobox is because you did not change the properties for the ActiveX ComboBox to extend the range to show 19 rows of data. On google search for ActiveX ComboBox ListFillRange which is used to tell it what range of values to display in the combobox and also search for LinkedCell which is the cell in which you get the selected output from the combobox. Attached updated. I've moved the links & ranges to calculation sheet

[ATTACH]n1200967[/ATTACH]

Magdoulin · Mar 14th 2018

Ah, that was the issue then
Alright
Sir, you got it all sorted, thank you so much
Really appreciate it

VBA to Call Drop Down List

Participate now!

Share

Users Viewing This Thread