Unable to change NumberFormat Property

[Geoff]

I have a sheet where I would like to adjust the format of the cell where a
user enters data, based on the entry type they have selected. The sheet is
protected, and the following If statement is contained within a macro for the
Change event of a combo box, and also nested within a pair of statements to
first unprotect, then protect the sheet.

If Stop_Loss_Limit_Type.Text = "$ Amount" Then

Sheets("Entry").Protect AllowFormattingCells:=True
Range("Min_entry").NumberFormat = "Currency"
Sheets("Entry").Protect AllowFormattingCells:=False

End If

When this is run, I get a run-time error 1004, with error message "Unable to
set the NumberFormat property of the Range class". Any advice on why this
might be welcomed - as far as I am aware setting allowformattingcells
property to true should allow me to change the number format.

Thanks
Geoff

 

[JLGWhiz]

I think you might have to be specific:

NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

 

[Geoff]

Exactly - thanks very much for that, I thought I was going mad

--
There are 10 types of people in the world - those who understand binary and
those who don''t.

I think you might have to be specific:

NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

I have a sheet where I would like to adjust the format of the cell where a
user enters data, based on the entry type they have selected. The sheet is
protected, and the following If statement is contained within a macro for the
Change event of a combo box, and also nested within a pair of statements to
first unprotect, then protect the sheet.

If Stop_Loss_Limit_Type.Text = "$ Amount" Then

Sheets("Entry").Protect AllowFormattingCells:=True
Range("Min_entry").NumberFormat = "Currency"
Sheets("Entry").Protect AllowFormattingCells:=False

End If

When this is run, I get a run-time error 1004, with error message "Unable to
set the NumberFormat property of the Range class". Any advice on why this
might be welcomed - as far as I am aware setting allowformattingcells
property to true should allow me to change the number format.

Thanks
Geoff