Listbox value not updating on userform and label not updating font settings

[Dave_T_]

Hello,

I have a project where I am creating a font settings box to allow a user to change settings within some defined limits. Almost everything works as expected, but there are two issues.

1. At different times, one of three Listboxes on the userform, while having the correct value selected (highlighted in the list as directed by code, not by the user clicking the form) the associated Listbox.value is null.

2. I have a sub that updates a Label as the user makes selections. On form load, the label correctly displays the selected font, but as the user makes changes, the bold and italic settings will not properly display on the user form. The correct settings are carried back to the spreadsheet correctly.

To test this I have a Command Button on Sheet1 named cmdFont with this code:
Clicking the command button retrieves current font settings from cell A1 and delivers them to the userform. Clicking OK on the userform applies the selected font settings to Cell A1

Option Explicit


Private Sub cmdFont_Click()
    Dim r As Range
    
    Set r = Me.Range("A1")
    With frmFont.lblSample.Font
        .Name = r.Font.Name
        .Size = r.Font.Size
        .Bold = r.Font.Bold
        .Italic = r.Font.Italic
    End With
    frmFont.Show vbModal
    Set r = Nothing
End Sub

And a Userform with the following controls used in the code:

Textboxes: txtFonts, txtSize, txtStyle
Listboxes: lstFonts, lstSize, lstStyle
(Listbox multi-select is false for all listboxes)
Command Buttons: cmdOK, cmdCancel
Label: lblSample

This is the code for the userform:

Option Explicit


Private Sub cmdCancel_Click()
    Unload Me
End Sub


Private Sub cmdOK_Click()
    With Sheet1.Range("A1").Font
        .Name = txtFonts
        .Size = txtSize
        Select Case txtStyle
            Case "Regular"
                .Bold = False
                .Italic = False
            Case "Bold"
                .Bold = True
                .Italic = False
            Case "Italics"
                .Bold = False
                .Italic = True
            Case "Bold Italics"
                .Bold = True
                .Italic = True
        End Select
    End With
    Unload Me
End Sub


Private Sub lstFonts_Click()
    txtFonts = lstFonts
    Call UpdateSample
End Sub


Private Sub lstSize_Click()
    txtSize = lstSize
    Call UpdateSample
End Sub


Private Sub lstStyle_Click()
    txtStyle = lstStyle
    Call UpdateSample
End Sub


Private Sub UpdateSample()
    Static SkipThreeTimes As Integer
    
    'Form updates as 3 listboxes are populated.
    'Do not execute this code on form build.
    
    If SkipThreeTimes < 3 Then
        SkipThreeTimes = SkipThreeTimes + 1
    Else
        With lblSample.Font
            .Name = txtFonts
            .Size = Val(txtSize)
            Select Case txtStyle
                Case "Regular"
                    .Bold = False
                    .Italic = False
                Case "Bold"
                    .Bold = True
                    .Italic = False
                Case "Italics"
                    .Bold = False
                    .Italic = True
                Case "Bold Italics"
                    .Bold = True
                    .Italic = True
            End Select
        End With
    End If
End Sub


Private Sub UserForm_Activate()
    'Populate font list box with available fonts
    Dim wd As Object, fontID As Variant
    Set wd = CreateObject("Word.Application")
    For Each fontID In wd.FontNames
        If Left(fontID, 1) <> "@" Then 'Skip duplicates with @ symbol"
            lstFonts.AddItem (fontID)
        End If
    Next
    wd.Quit
    Set wd = Nothing
    
    With lstStyle
        .AddItem ("Regular")
        .AddItem ("Bold")
        .AddItem ("Italics")
        .AddItem ("Bold Italics")
    End With
    
    With lstSize
        .AddItem ("8")
        .AddItem ("10")
        .AddItem ("12")
        .AddItem ("14")
        .AddItem ("16")
        .AddItem ("18")
        .AddItem ("20")
        .AddItem ("22")
        .AddItem ("24")
        .AddItem ("26")
        .AddItem ("28")
        .AddItem ("30")
    End With
        
    With lblSample.Font
        txtFonts = .Name
        lstFonts.Value = .Name
        If .Size >= 8 And .Size <= 30 Then
                'Only even numbered integer font size 8-36 allowed
            txtSize = IIf((Int(.Size) / 2) = (Int(Int(.Size) / 2)), Int(.Size), Int(.Size) + 1)
            lstSize = IIf((Int(.Size) / 2) = (Int(Int(.Size) / 2)), Int(.Size), Int(.Size) + 1)
        Else
            MsgBox "Invalid Font size"
            txtSize = 8
            lstSize = 8
        End If
        
        Select Case .Bold
            Case True
                If .Italic Then
                    txtStyle = "Bold Italics"
                Else
                    txtStyle = "Bold"
                End If
            Case False
                If .Italic Then
                    txtStyle = "Italics"
                Else
                    txtStyle = "Regular"
                End If
        End Select
        lstStyle = txtStyle
    End With
End Sub

It seems to me this should be fairly straight-forward, but can't figure out what's going on here.

I'm on Windows server 2016 standard and Excel 2016.

Any insight would be helpful.

Thanks,
Dave

 

[Dave_T_]

Good afternoon,

Still hoping for a reply on this. Maybe separating the issues may help? Let's start with the label control.

The label update code is as follows:

        With lblSample.Font
            .Name = txtFonts
            .Size = Val(txtSize)
            Select Case txtStyle
                Case "Regular"
                    .Bold = False
                    .Italic = False
                Case "Bold"
                    .Bold = True
                    .Italic = False
                Case "Italics"
                    .Bold = False
                    .Italic = True
                Case "Bold Italics"
                    .Bold = True
                    .Italic = True
            End Select
        End With

Stepping through the code, any time either .Bold or .Italic is encountered, the property is always set to True, even when the command is to set it to false.

Correct settings sent back the the spreadsheet because the settings are read from the textbox controls, not the label.

Does anyone have any thoughts on why this would be?

Thanks,
Dave