[Les[_4_]]

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

[Bill Lunney]

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

[Les[_4_]]

Hi

Further to this post. The problem was of my own making - how often
that is the case! I had a couple of variables declared as wrong
Type. Changing to correct type (Double) seems to have eliminated
the problems.

As to why the problems appeared in the manner that they did, and
as to why the above fixed them, still beats me - but again,
that's not exactly hard to do :=)

I am now happy in as much as I have managed to eliminate one area
(the bodges) that annoyed me.

Thanks for your help Bill

regards,
--
Les Hay, Livingston. Scotland
"Bill Lunney" wrote in message
...
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