I have a piece of code as follows :

Dim myCell as Range
For Each myCell in Selection
MyCell.NumberFormat = "@"
Next myCell

When I select a range of cells manually and run the macro from the <Tools
menu in Excel '97, it works fine but the problem is that if I put this code
in a button_click Sub, I get the error message 'Unable to change the
NumberFormat property of the Range Object' for the code 'myCell.NumberFormat
= "@".

Could someone suggest a reason(s) as to why this happens, I am completely
dumbfounded.

Rightclick on the commandbutton
choose properties
Change the .takefocusonclick property to false

This bug was fixed in xl2k.

ps.
why not get all the cells at once and drop the loop:
Selection.numberformat = "@"



Ade wrote:

I have a piece of code as follows :

Dim myCell as Range
For Each myCell in Selection
MyCell.NumberFormat = "@"
Next myCell

When I select a range of cells manually and run the macro from the <Tools
menu in Excel '97, it works fine but the problem is that if I put this code
in a button_click Sub, I get the error message 'Unable to change the
NumberFormat property of the Range Object' for the code 'myCell.NumberFormat
= "@".

Could someone suggest a reason(s) as to why this happens, I am completely
dumbfounded.

--

Dave Peterson

Cheers Dave, I'll give that a go later and if it works I'll be a happy man.

I have tried theSelection.NumberFormat method you suggested as well but the
same thing was happening.

Thanks again.

Change that .takefocusonclick property--no matter which version you use.

Ade wrote:

Cheers Dave, I'll give that a go later and if it works I'll be a happy man.

I have tried theSelection.NumberFormat method you suggested as well but the
same thing was happening.

Thanks again.

--

Dave Peterson