Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reduce Font Size by 1 Step in VBA

Is there a simple way to reduce the Font Size in Word / Excel etc by 1 step in VBA?

So, say if my Font size was 48 could I reduce it to 36, easily, as per the Font drop down in the standard Word 2007 Font group, rather than reducing the font size by 12 - I will not know what the next font size down is...

So rather than setting Font Size explicitly by float :

MyText.Font.Size = 36;

could I do something like :

MyText.Font.Size -= Reduce by 1 step;....  forgive the pseudo code!
like image 248
Joe.Net Avatar asked Jun 07 '11 10:06

Joe.Net


People also ask

How do I change the font size in VBA?

Easy change To open the VBA Window press Alt + F11. Click the Tools menu and then Options – see image below. Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below. Click OK and the font size will now be increased in the code window.

How do I downsize a font?

Keyboard shortcutHold down the Ctrl and press the + to increase the font size or - to decrease the font size. Pressing either of these keys while continuing to hold down the control key continues to increase or decrease the font until it reaches its maximum.

How do you change the font of a cell in Excel VBA?

To make changes in a font, you need to use the VBA Font object. There is a total of 18 properties with the font object that you can access and make changes.


1 Answers

For Excel, you can check the Font Size combobox on the Formatting toolbar. That's 2003 stuff and I think it will still work in 2007 and beyond, but I don't have it available to test.

Sub FontShrink(rng As Range)

    Dim i As Long
    Dim ctl As CommandBarComboBox

    Set ctl = Application.CommandBars("Formatting").Controls("Font Size:")

    If rng.Font.Size > CDbl(ctl.List(ctl.ListCount)) Then
        'if it's bigger than the biggest, make it the biggest
        rng.Font.Size = ctl.List(ctl.ListCount)
    Else
        For i = ctl.ListCount To 2 Step -1
            If rng.Font.Size > CDbl(ctl.List(i)) Then
                rng.Font.Size = CDbl(ctl.List(i))
                Exit For
            ElseIf rng.Font.Size = CDbl(ctl.List(i)) Then
                rng.Font.Size = CDbl(ctl.List(i - 1))
                Exit For
            End If
        Next i
    End If
End Sub
like image 103
Dick Kusleika Avatar answered Oct 20 '22 04:10

Dick Kusleika