Excel VBA - Font Size of Data Validation List

I have a data validation list with the values showing in a very small font size.  First off, is there anyway in Excel 2010 to change the font on the validation list?  From what I am finding, the answer is no.

So, I found the following article:
Excel Data Validation increase font size

It's good, but it's not working with the attached example.  I want the font size to be 16 and why does the old validation list arrow still show?

Maybe there is a better way of doing this completely.
DDL.xlsm
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The fact is that you can't change the size of a data validation drop-down list. The article you referred to uses a floating combobox to show the list and a combobox allows you to change the font size. A while ago I wrote this article which is a modified version of the Contextures' code.
0
Martin LissOlder than dirtCommented:
If after reading my article and using it's code, I'll be happy to help if you have any problems.
0
maverick0728Author Commented:
Great article Martin!
In the attached DDL.xlsm workbook, column B and C have the combo box.  Is there anyway to put a floating combo box, say in cells B2:B100 and similarily C2:C100.

Similar to the floating textbox example you had, but when you click in the cell in these ranges, the combo box with the data for that row appears.

Can you update the spreadsheet attached?
DDL.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
In the workbook you posted you weren't really using the floating combobox because you were getting an error that was masked by "On Error Resume Next". Also the combobox you added had a font size of 16 which I thought was large so I added a line of code that change it to 12. You can of course remove or change that line if you want.
28703590.xlsm
0
maverick0728Author Commented:
Two more questions:
1.  How can I have the "TempCombo" only work on a particular column?  I ended up moving this code
into my workbook and it needs to work only in column H.

2.  I am getting the error 438 (object doesn't support this property or method) in procedure ShowAutocomplete
0
maverick0728Author Commented:
Nevermind on #2 - the error.  I found the issue.
0
maverick0728Author Commented:
I have also noticed that the cell with data validation that does:
=OFFSET(MtlStart,MATCH(BD7,MtlColumn,0)-1,1,COUNTIF(MtlColumn,BD7),1)

doesn't display anything in the combo box.  The other combo boxes without this work.

Maybe I should be clicking in the cell and have a form come up.
0
Martin LissOlder than dirtCommented:
If you want the code in my workbook to work only in column H then change this line

    If Target.Validation.Type = 3 Then

to

    If Target.Validation.Type = 3 And Target.Column = 8 Then

As for the one cell that doesn't display anything I suspect the formula is wrong but I can't test it because I don't believe that the workbook you posted has that formula.
0
maverick0728Author Commented:
Martin,
See attached.  This is the actual workbook with just the data we need for this example.

Column H - has the data validation formula:
=OFFSET(MtlStart,MATCH(P6,MtlColumn,0)-1,1,COUNTIF(MtlColumn,P6),1)

The combo box doesn't work with this.
DDL.xlsm
0
Martin LissOlder than dirtCommented:
This one has got me stumped. I will continue to work on it unless you tell me to to.
0
maverick0728Author Commented:
This is why I posted the other question on a userform pop-up with adjacent cell values.  I thought I'd then take the adjacent values and run a SQL query and drop the recordset into a list.  Double click and your selection goes into active cell.

I don't really like this because having a form pop-up is not as efficient for data entry.

I will wait until you post back.
0
Martin LissOlder than dirtCommented:
The data Validation formula in column H of sheet Part Info selects a range of cells in column B of sheet epicorMtl. Would you know how to copy (via VBA) that range to D1 on epicorMtl?
0
maverick0728Author Commented:
Like this?
    Sheets("epicorMtl").Select
    Range("B1:B1000").Copy
    Sheets("epicorMtl").Select
    Range("D1:D1000").Select
    ActiveSheet.Paste

Open in new window

0
Martin LissOlder than dirtCommented:
Sorry, no. What I meant was that a particular portion of B is used in the Data Validation, and that potion is based on the active cell in H.  I'd like to be able to show those values in D1:D<whatever>.

If it helps, the Data Validation formula (when H19 is selected) is =OFFSET(MtlStart,MATCH(P19,MtlColumn,0)-1,1,COUNTIF(MtlColumn,P19),1)
0
maverick0728Author Commented:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If Target.Column = 8 Then
      Range("O2").Copy
      Sheets("epicorMtl").Range("D1").PasteSpecial xlPasteValues
      Range("I" & ActiveCell.Row).Copy
      Sheets("epicorMtl").Range("E1").PasteSpecial xlPasteValues
      Range("J" & ActiveCell.Row).Copy
      Sheets("epicorMtl").Range("F1").PasteSpecial xlPasteValues
      Range("M" & ActiveCell.Row).Copy
      Sheets("epicorMtl").Range("G1").PasteSpecial xlPasteValues
      
     
    End If

End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Here is a picture of what I'm after. If you select cell H12 and then go to Data Validation and select the text in the 'source' box, the cells bounded by the "marching ants" and pictured here will be selected. I assume they are what you'd like to see in the combobox in that case.
DV
1
Martin LissOlder than dirtCommented:
Public Sub ShowAutocomplete(Target As Range)

    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lngNR As Long
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    'new
    ws.OLEObjects("TempCombo").Object.Font.Size = 10
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    'If Target.Validation.Type = 3 Then
    If Target.Validation.Type = 3 And Target.Column = 8 Then
        ' The cell contains a data validation list
        Application.EnableEvents = False
        ' Get the data validation formula
'        strVF = Target.Validation.Formula1
'        strVF = Right(strVF, Len(strVF) - 1)
        
        With cboTemp
            ' Show the combobox with the list
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            With Sheets("epicorMtl")
                For lngRow = 2 To .UsedRange.Rows.Count
                    If .Cells(lngRow, "B").EntireRow.Hidden = False Then
                       ws.OLEObjects("TempCombo").Object.Font.Size = 16
                       ws.OLEObjects("TempCombo").Object.AddItem .Cells(lngRow, "B")
                    End If
                Next
            End With
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        ' Open the drop down list automatically
        ActiveSheet.TempCombo.DropDown
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

0
maverick0728Author Commented:
I put the new "ShowAutoComplete" code into place.  It's displaying every possible value, instead of just the ones needed.  So for instance, (your assumption above was correct).  If you click on cell "H12"  you are then searching on:  "INRectangleCarbon0.12", which means these are the values we need in the combo box.Cell H12 Values
The updated workbook is attached.
DDL.xlsm
0
Martin LissOlder than dirtCommented:
Sorry about that.

Public Sub ShowAutocomplete(Target As Range)

    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lngNR As Long
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    ws.OLEObjects("TempCombo").Object.Font.Size = 14
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    'If Target.Validation.Type = 3 Then
    If Target.Validation.Type = 3 And Target.Column = 8 Then
        ' The cell contains a data validation list
        Application.EnableEvents = False
        ' Get the data validation formula
'        strVF = Target.Validation.Formula1
'        strVF = Right(strVF, Len(strVF) - 1)
        
        With cboTemp
            ' Show the combobox with the list
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 15
            .Height = Target.Height + 5
            With Sheets("epicorMtl")
                ws.OLEObjects("TempCombo").Object.Clear
                For lngRow = 2 To .UsedRange.Rows.Count
                    If .Cells(lngRow, "A").Value = Sheets("Part Info").Cells(ActiveCell.Row, "P").Value Then
                       ws.OLEObjects("TempCombo").Object.AddItem .Cells(lngRow, "B")
                    End If
                Next
            End With
            .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        ' Open the drop down list automatically
        ActiveSheet.TempCombo.DropDown
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maverick0728Author Commented:
Awesome.  It works.  Martin is "the Godfather" for Excel VBA.  Thanks.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
Martin LissOlder than dirtCommented:
This better handles the situation where MtlLookup is blank.

Public Sub ShowAutocomplete(Target As Range)

    Dim strVF As String
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lngNR As Long
    
    On Error GoTo errHandler

    Set ws = ActiveSheet
    
    If gbMaintBeingDone Then
        Exit Sub
    End If
    
    ws.OLEObjects("TempCombo").Object.Font.Size = 14
    
    Set cboTemp = ws.OLEObjects("TempCombo")
    With cboTemp
        ' Clear and hide the combo box
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    
    'If Target.Validation.Type = 3 Then
    If Target.Validation.Type = 3 And Target.Column = 8 Then
        ' The cell contains a data validation list
        Application.EnableEvents = False
        ' Get the data validation formula
        If Sheets("Part Info").Cells(ActiveCell.Row, "P").Value <> "" Then
            With cboTemp
                ' Show the combobox with the list
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 15
                .Height = Target.Height + 5
                With Sheets("epicorMtl")
                    ws.OLEObjects("TempCombo").Object.Clear
                    For lngRow = 2 To .UsedRange.Rows.Count
                        If .Cells(lngRow, "A").Value = Sheets("Part Info").Cells(ActiveCell.Row, "P").Value Then
                           ws.OLEObjects("TempCombo").Object.AddItem .Cells(lngRow, "B")
                        End If
                    Next
                End With
                .LinkedCell = Target.Address
            End With
            cboTemp.Activate
            ' Open the drop down list automatically
            ActiveSheet.TempCombo.DropDown
        End If
    End If
    
    Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub

errHandler:

    Application.EnableEvents = True
    ' If it's 1004 there's no data validation in the cell
    If Err.Number <> 1004 Then
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"
    End If
    
End Sub

Open in new window

1
maverick0728Author Commented:
Thanks for the followup.  Yes that does work much better.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.