Option Button/Focus Problem on Worksheet

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

Resume Next wrote:
> 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

--
HTH,

okaizawa

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!

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

Thanks!

David

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

--
HTH,

okaizawa


Resume Next wrote:
> I thought I would send this to the top one more time now that the
> weekend is over. I could still use some ideas if anyone has any.
>
> Thanks!
>
> David