L
Les
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 where:
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,
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 where:
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,