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?
VBA to Call Drop Down List
- Thread is marked as Resolved.
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
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
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
And if want to do that in one shot you can test that codeCode
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
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]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]
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 codeCode
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
The font size of a validation list cannot be changed. A workaround is to use an ActiveX ComboBox.
the thing is that they are 4 dependant lists, not really sure how to manage this with the combo box
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
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
Something like this?
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
Ah, that was the issue then
Sir, you got it all sorted, thank you so much
Really appreciate it
Don’t have an account yet? Register yourself now and be a part of our community!