VBA to Call Drop Down List

  • 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?

  • 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

    Sheets("Sheet1").Range("A1").Font.Size = "11"
  • 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

  • May be something like that

    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
        ActiveWindow.Zoom = x
    End Sub
  • 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

  • May be slight change will solve it

    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
        Target.Font.Size = x
    End Sub
  • And if want to do that in one shot you can test that code

  • 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?

  • 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

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

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • 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 1st Combo Box to include the options of X/Y/Z
    And the 2nd one to include A/B/C or D/E/F or G/H/I, depends on the 1st selection
    And the 3rd & 4th Combo Boxes to work the same way

  • 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?

  • 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 4th combo box
    I need just the technique

  • 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?

  • 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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!