Code to change font size

[wpryan]

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.

 

[mvptomlinson]

Either unlock the cell and allow users to edit it while the sheet is protected, or unprotect the sheet in the macro, e.g.

[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]

 

[wpryan]

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!

 

[mvptomlinson]

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.

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"

 

[wpryan]

Thanks again, you're awesome!