Option Button/Focus Problem on Worksheet

[Resume Next]

Overview: My worksheet option buttons w/linked cells behave as expected
when clicked manually. They do not work as expected when the value of
their linked cells are changed via VBA.

Details: On the spreadsheet I have a series of grouped pairs of option
buttons created using the Forms toolbar. Each OB in a pair is labeled
Yes or No and each is linked to an individual cell. The user clicks on
the Yes button and the linked cell says TRUE and the other linked group
cell says FALSE. Click on the No button and it's linked cell says TRUE
and the Yes buttons linked cell says False. Works great.

Pressing the option buttons runs the ..._Click() routine for the
buttons. Those routines call a subroutine that changes the contents and
formatting of a different cell on the spreadsheet. For example if the
user presses "Yes" then the ..._Click() routine runs another subroutine
that protects a user input cell and replaces the cell's value with "N/A"
and modifies the cell formatting. Works fine. If the user presses "No"
the cell is unprotected, the default input value is entered in the cell
and the formatting is modified. Also works fine.

Everything works properly when I manually press the option buttons.

Problem:

I have a button on the worksheet that says "Reset Values to Defaults"
whose job is to reset the worksheet back to the default values. After
unprotecting the worksheet part of the routine copies and pastes
(replacing) the default values for the linked cells of the option
buttons. When that happens the linked cells are changed and it
initiates the ..._Click() routine for the option button. The routine
runs, calls the subroutine and tries to modify the user input cells
just like I want it to do. It can replace a cell's input but it breaks
when it tries to change the cell protection or the cell formatting.

I have tried using ".Select" to select the cell before the changes are
made to it but Excel does not seem to select the cell even though the
line of code works. It's like the focus is locked somewhere else,
probably on the option button or maybe the "Reset Values to Defaults"
button.

here is an example of where the code breaks in the
LockTheProcessorCostsCell() routine:

With Intersect(oColumnName, Range("rowProcessorCosts"))
.Value = "N/A" 'This works fine.
.Borders(xlEdgeLeft).LineStyle = xlNone 'I get a runtime error
'1004': Unable to set the LineStyle property of the border class.

Any thoughts?

Thanks for taking the time!

David

 

[okaizawa]

With Intersect(oColumnName, Range("rowProcessorCosts"))
.Value = "N/A" 'This works fine.
.Borders(xlEdgeLeft).LineStyle = xlNone 'I get a runtime error
'1004': Unable to set the LineStyle property of the border class.

Hi,

setting to Borders(xlEdgeLeft).LineStyle also changes the right border
in the left cell. if the sheet is protected, this may work:

.Borders(xlLeft).LineStyle = xlNone

 

[Resume Next]

The worksheet is unprotected as soon as the subroutine starts. Also
during my testing I don't have sheet protection turned on at all so I
don't think the problem is related to that.

Any other ideas?

Thanks!

 

[Resume Next]

I thought I would send this to the top one more time now that th
weekend is over. I could still use some ideas if anyone has any.

Thanks!

Davi

 

[okaizawa]

Hi,

I can't think of the reason now. try to test on another workbook, and
the manual calculation mode, for example,

With Intersect(oColumnName, Range("rowProcessorCosts"))
MsgBox "Address: " & .Address(External:=True)
MsgBox "Protection: " & .Worksheet.ProtectContents
MsgBox "Calculation: " & Application.Calculation

.Copy
Workbooks.Add(xlWorksheet).Worksheets(1).Cells(1).PasteSpecial
Selection.Value = "N/A"
Selection.Borders(xlLeft).LineStyle = xlNone

Application.Calculation = xlCalculationManual

.Value = "N/A"
.Borders(xlLeft).LineStyle = xlNone

Application.Calculation = xlCalculationAutomatic