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!
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With