check boxes in Excel - VBA Question
 

I am attempting to have Excel change a formula based on the status of a
check box contained on a sheet. The code I have is the following:

Private Sub CheckBoxFULL_Change()

Application.ScreenUpdating = False

Select Case CheckBoxFULL.Value

Case True

Sheets("CAL-SHEET").Select
Range("F36").Select
ActiveCell.FormulaR1C1 = "='Line Fit FULL CURVE'!RC[-4]"
Worksheets("Line Fit FULL CURVE").Activate
Range("B36").Select

Case False

Sheets("CAL-SHEET").Select
Range("F36").Select
ActiveCell.FormulaR1C1 = "=R[-23]C[24]"
Worksheets("Line Fit FULL CURVE").Activate
Range("B36").Select

End Select

Sheets("CAL-SHEET").Select

Application.ScreenUpdating = True

End Sub

But I keep getting:

Run-time error '1004'
Select method of Range class failed

on the "Range("F36").Select" line. Why is this not functioning
correctly? If I copy the code to a plain macro, it performs everything
correctly, but when running it as I do above, it errors out.

Thanks for any help/guidance!

Ken

check boxes in Excel - VBA Question
 

Private Sub CheckBoxFULL_Change()



Select Case CheckBoxFULL.Value

Case True

With Sheets("CAL-SHEET")
.Range("F36").FormulaR1C1 = _
"='Line Fit FULL CURVE'!RC[-4]"
End With

Case False

With Sheets("CAL-SHEET")
.Range("F36").FormulaR1C1 = _
"=R[-23]C[24]"

End Select

Worksheets("Line Fit FULL CURVE")
Worksheets("Line Fit FULL CURVE").Range("B36").Select



End Sub

--
Regards,
Tom Ogilvy

" wrote:

I am attempting to have Excel change a formula based on the status of a
check box contained on a sheet. The code I have is the following:

Private Sub CheckBoxFULL_Change()

Application.ScreenUpdating = False

Select Case CheckBoxFULL.Value

Case True

Sheets("CAL-SHEET").Select
Range("F36").Select
ActiveCell.FormulaR1C1 = "='Line Fit FULL CURVE'!RC[-4]"
Worksheets("Line Fit FULL CURVE").Activate
Range("B36").Select

Case False

Sheets("CAL-SHEET").Select
Range("F36").Select
ActiveCell.FormulaR1C1 = "=R[-23]C[24]"
Worksheets("Line Fit FULL CURVE").Activate
Range("B36").Select

End Select

Sheets("CAL-SHEET").Select

Application.ScreenUpdating = True

End Sub

But I keep getting:

Run-time error '1004'
Select method of Range class failed

on the "Range("F36").Select" line. Why is this not functioning
correctly? If I copy the code to a plain macro, it performs everything
correctly, but when running it as I do above, it errors out.

Thanks for any help/guidance!

Ken