Consulting

Results 1 to 1 of 1

Thread: Control Box Data Validation won't change font size

  1. #1

    Control Box Data Validation won't change font size

    Hi. Hopefully someone can assist me...
    The code pasted below allows me to click on a sell that has data validation....then control box appears enabling me to use validation drop down list that is also controlled in vba.
    the purpose for me doing this is so I can change font size in drop down list....everything works fine except in combo box properties I change font & listrow but it has no effect on the box.
    I use Activex (Microsoft Professional Plus 2010. I pasted code below.

    Option Explicit
    
    Private Sub ComboBox1_Change()
        MyComboBox.Font.Size = 20
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim str As String
        Dim cboTemp As OLEObject
        Dim ws As Worksheet
        Set ws = ActiveSheet
        On Error GoTo errHandler
        If Target.Count > 1 Then GoTo exitHandler
        Set cboTemp = ws.OLEObjects("TempCombo")
        On Error Resume Next
        If cboTemp.Visible = True Then
            With cboTemp
                .Top = 10
                .Left = 10
                .ListFillRange = ""
                .LinkedCell = ""
                .Visible = False
                .Value = ""
            End With
        End If
        On Error GoTo errHandler
        If Target.Validation.Type = 3 Then
            ' if the cell contains a data validation list
            Application.EnableEvents = False
            ' get the data validation formula
            str = Target.Validation.Formula1
            str = Right(str, Len(str) - 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
                .ListFillRange = ws.Range(str).Address
                .LinkedCell = Target.Address
            End With
            cboTemp.Activate
            ' open the drop down list automatically
            Me.TempCombo.DropDown
        End If
        exitHandler:
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
        errHandler:
        Resume exitHandler
    End Sub
    Last edited by Aussiebear; 06-29-2025 at 03:47 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •