unable to set the numberformat of the range class

[TSW632]

I was graciously given some code to format some cells for different
currencies (yen, euro, dollar) based upon selecting the currency in
one cell (k5) in my requisition. I got it to work, ready to launch the
new form for use by my engineers, so I add protection.

Now it seems I cannot protect those cells I want to format, in order
for the code to work. Instead I get the error in the subject line of
this post.

Anyone know the fix? Below is the code, and btw, "worksheet" and
"calculate" are the selections made at the top of the code window.

Troy

Private Sub Worksheet_Calculate()
Dim cell As Range
If Range("K5").Text = "GBP£" Then
Range("k15:k30").NumberFormat = "£ #,##0.00"
Range("k37").NumberFormat = "£ #,##0.00"
Range("d11").NumberFormat = "£ #,##0.00"
ElseIf Range("K5").Text = "JPY¥" Then
Range("k15:k30").NumberFormat = "¥ #,##0.00"
Range("k37").NumberFormat = "¥ #,##0.00"
Range("d11").NumberFormat = "¥ #,##0.00"
ElseIf Range("K5").Text = "EUR€" Then
Range("k15:k30").NumberFormat = "€ #,##0.00"
Range("k37").NumberFormat = "€ #,##0.00"
Range("d11").NumberFormat = "€ #,##0.00"
ElseIf Range("K5").Text = "USD$" Then
Range("K15:K30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "CAD$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "MEX$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "BRL$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
End If
End Sub

 

[Per Jessen]

Hi

You have to unprotect the sheet to add the NumberFormat and then protect it
again:

Private Sub Worksheet_Calculate()
Activeworksheet.Unprotect Password:="JustMe"
Dim cell As Range
If Range("K5").Text = "GBP£" Then
Range("k15:k30").NumberFormat = "£ #,##0.00"
........
Range("d11").NumberFormat = "$ #,##0.00"
End If
Activeworksheet.Protect Password:="JustMe"
End Sub

Regards,
Per

"TSW632" <[email protected]> skrev i meddelelsen
I was graciously given some code to format some cells for different
currencies (yen, euro, dollar) based upon selecting the currency in
one cell (k5) in my requisition. I got it to work, ready to launch the
new form for use by my engineers, so I add protection.

Now it seems I cannot protect those cells I want to format, in order
for the code to work. Instead I get the error in the subject line of
this post.

Anyone know the fix? Below is the code, and btw, "worksheet" and
"calculate" are the selections made at the top of the code window.

Troy

Private Sub Worksheet_Calculate()
Dim cell As Range
If Range("K5").Text = "GBP£" Then
Range("k15:k30").NumberFormat = "£ #,##0.00"
Range("k37").NumberFormat = "£ #,##0.00"
Range("d11").NumberFormat = "£ #,##0.00"
ElseIf Range("K5").Text = "JPY¥" Then
Range("k15:k30").NumberFormat = "¥ #,##0.00"
Range("k37").NumberFormat = "¥ #,##0.00"
Range("d11").NumberFormat = "¥ #,##0.00"
ElseIf Range("K5").Text = "EUR€" Then
Range("k15:k30").NumberFormat = "€ #,##0.00"
Range("k37").NumberFormat = "€ #,##0.00"
Range("d11").NumberFormat = "€ #,##0.00"
ElseIf Range("K5").Text = "USD$" Then
Range("K15:K30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "CAD$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "MEX$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
ElseIf Range("K5").Text = "BRL$" Then
Range("k15:k30").NumberFormat = "$ #,##0.00"
Range("k37").NumberFormat = "$ #,##0.00"
Range("d11").NumberFormat = "$ #,##0.00"
End If
End Sub

 

[TSW632]

Got it. Thanks Per!