"Wrap Cells and Resize": Podcast #1472

legogeek

New Member
Joined
Mar 1, 2011
Messages
28
I want to thank Bill for helping me with this, it works very well. However, I have this error come up when I update where the concatenations come from:
run time error 1004 excel macro "unable to set the Size property of the Font class"
I believe it is because I have the sheets protected.
I found a post that deals with that problem but I'm unable to implement it correctly:
http://www.mrexcel.com/forum/showth...le+to+set+the+Size+property+of+the+Font+class
This is what I've tried:
Code:
Sheets("your_sheet_name").Unprotect Password:="your_password"
Private Sub Worksheet_Calculate()
	For Each cell In Range("B3:H22")
		Select Case Len(cell.Value)
			Case 0 To 30
				cell.Font.Size = 11
			Case 31 To 40
				cell.Font.Size = 9
			Case 41 To 50
				cell.Font.Size = 8
			Case 51 To 60
				cell.Font.Size = 7
		End Select
	Next cell
End Sub
Sheets("your_sheet_name").Protect Password:="your_password"

When I add the Unprotect/Protect I get a Compile error: Invalid outside procedure.

Am I using this idea Unprotect/Protect correctly or is there another way to solve the first or second error?
 
Jerry,
Like I said... I'm don't completly understand VBA yet :biggrin:

Fortunately I'm getting a new compile error (change is always good)
"Invalid use of Me keyword" It then highlights "Me" in the code.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sorry...that's why I usually don't post "untested code" ;)

Try...
Code:
Sub All_Months()
    Dim i As Long
    Dim Cell As Range
    On Error Resume Next
 
    For i = 1 To 12
        With Worksheets(Format(i & "/1/2011", "Mmmm"))
                 .Unprotect Password:="bob"
                 For Each Cell In Range("B3:H22")
                     Select Case Len(Cell.Value)
                         Case 0 To 30
                             Cell.Font.Size = 11
                         Case 31 To 40
                             Cell.Font.Size = 9
                         Case 41 To 50
                             Cell.Font.Size = 8
                         Case 51 To 60
                             Cell.Font.Size = 7
                     End Select
                 Next Cell
                .Protect Password:="bob"
        End With
    Next i
End Sub
 
Upvote 0
JERRY!

It is working perfectly now. Thank you for hanging in there with me. I really appreciate it.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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