Code to change font size

W

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have some code to change the font size of a range of merged cells based on the length of text within the cells. The code works fine when the sheet is unprotected, however when I protect it the code fails with the message "Run-time error 1004 - Unable to set the Size property of the Font class".

The code is:

If Len(Range("A26")) > 1000 Then
Range("A26").Font.Size = 6
ElseIf Len(Range("A26")) > 500 And Len(Range("A26")) < 1000 Then
Range("A26").Font.Size = 7
Else:
Range("A26").Font.Size = 8
End If

I can't figure out what is the problem. Can anyone chime in here? Thanks.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Either unlock the cell and allow users to edit it while the sheet is protected, or unprotect the sheet in the macro, e.g.
Code: 
[COLOR=Red]Sheets("your_sheet_name").Unprotect Password:="your_password"[/COLOR]
If Len(Range("A26")) > 1000 Then
       Range("A26").Font.Size = 6
    ElseIf Len(Range("A26")) > 500 And Len(Range("A26")) < 1000 Then
       Range("A26").Font.Size = 7
    Else:
       Range("A26").Font.Size = 8
End If
[COLOR=Red]Sheets("your_sheet_name").Protect Password:="your_password"[/COLOR]
 
Upvote 0
Thanks for your quick reply. I had tried something similar, but I wanted to keep it within the 'if - end if' construct. I had put the unlock code immediately after the first line of the 'if' statement, and immediately before the 'end if' statement, which didn't work.
I added the unprotect and protect lines within each step, and is now working fine. I don't really know if it's necessary, but I'm a newbie to VBA and I wrote some other code to unprotect a sheet in the wrong place and accidentally left it unprotected.
Thanks again!
 
Upvote 0
Another option besides using nested IF statements is to use a Select Case statement, which only checks the length of the cell once rather than multiple times.
Code: 
Sheets("your_sheet").Unprotect Password:="your_password"
With Range("A26")    
    Select Case Len(.Value)
        Case Is > 1000
            .Font.Size = 6
        Case Is > 500
            .Font.Size = 7
        Case Else
            .Font.Size = 8
    End Select
End With
Sheets("your_sheet").Protect Password:="your_password"
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
ComboBox List selection to trigger code
Replies
1
Views
219
MichaelRSnow
M
F
  • Question
Returning changed dynamically created textbox value to corresponding cell
Replies
2
Views
334
frateg8r
F
K
  • Solved
VBA change the font of the first letter only in each cell in a range to a specific font
Replies
1
Views
91
Fluff
Fluff
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
375
Legacy 352679
L
E
  • Solved
Change the font on Emails
2
Replies
11
Views
535
Eric Penfold
E

Forum statistics

Threads
1,214,590
Messages
6,120,423
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top