Are you using ActiveX buttons to launch the form? If so make sure you set
the TakeFocus property to false.

I'm thinking the protection thing could just be a different manifestation of
this well known bug. I've had the bug you mention where I get unable to set
interior class... when code is launched by the user via a button.

My money is on it being something to do with this. I'd try doing some
things like launching the form in different ways (via the immediate window
(frmxxx.show)).




--

Regards,


Bill Lunney
www.billlunney.com

"Les" wrote in message
...
Hi

On workbook open, a procedure runs through all sheets and sets
following:

Worksheets(i).Protect password:="***", userinterfaceonly:=True

This worked fine, until I started developing a userform which
calculates some stuff and lets the user click a button to enter
the result into a cell.

What happens now is that any changes to any sheet (triggering
WorkSheet_Change event) works fine, *except* when the data is
entered via the userform. When this happens, the WorkSheet_Change
procedure runs correctly up to a point whe

Worksheets("summary").Range("i" & h & ":j" &
h).Interior.ColorIndex = 34

always fails. (*only* and always at this line. Any normal user
entry into a cell triggers this procedure without fault.
(including this line every time). The error generated is: Run time
error '1004' Unable to set the ColorIndex property of the Interior
class

A bodge to get around this problem is that I wrap the line which
places the userform result into the cell with a sheet
unprotect/protect. Although this solves the problem, I can't
figure out why. I shouldn't need to make this bodge.

This is part of the code in the button click event procedure to
illustrate the bodge.

Worksheets("summary").UnProtect password:="***"
ActiveSheet.Cells(ActiveCell.Row, 11) = r ' only from this
trigger does the above failure occur
Worksheets("summary").Protect password:="***",
userinterfaceonly:=True


Can anyone point me in a direction that may lead to a solution for
this problem. I could post whatever code I have if needed, but
have limited to above in the first instance.

Any help would be very much appreciated.

regards,
--
Les Hay, Livingston. Scotland
leshay at leshay dot freeserve dot co dot uk