Excel 2000, VBA, Option Button Error 1004... Not grouped

[m6]

Here is the scenerio...

3 option buttons on an a sheet. Not grouped! No forms. Nothing fancy.

Being green on the XL VBA code, I recorded the action as a macro, then converted it to the option button.

Upon the click of the button, it simply copy's and pastes on another sheet. The code is below.

I get the good old 1004 error (Select Method of Range class failed).

After some digging I found the links to XL97 kba 177527, but this was suppose to be taken care of fo XL2000.

I tried the "activecell.activate" as suggested in the kba, but no luck.

Private Sub optFourPager_Click()
Sheets("Math Page&quot.Activate
Range("D18&quot.Select
Range("D18&quot.Activate 'the choking starts here!
Selection.Copy
Range("C16&quot.Select
ActiveSheet.Paste
Sheets("Report Page&quot.Select
Range("C16&quot.Select
returnbutton = Msgbox("If this is the estimate, you need to push the Reset PVDS button next!", vbOKOnly, "NOTICE&quot
optFourPager.Activate
End Sub

I get the feeling that the ActiveX use of the option button is colliding with a focus. I saw suggestions to move the focus, but this didn't work either (or I am just not doing it write (pun intended).

Any help or pointing to help would be appreciated?

Sincerely banging head on keyboard...

m6 (Mike)

 

[Zathras]

These modifications will stop the error, but it is difficult for me to see what you are really trying to do. The way it is written you are copying from D18 to C16 on the same sheet ("Math Page&quot. If you want to copy from D18 on "Report Page" to C16 on "Math Page" (or vice versa) the code needs revision. If you need more help, please provide more details on what it is you are doing.

Private Sub optFourPager_Click()
Sheets("Math Page").Activate

  With ActiveSheet
    .Range("D18").Select
    .Range("D18").Activate

    Selection.Copy

    .Range("C16").Select
  End With

    ActiveSheet.Paste
    Sheets("Report Page").Select
    Range("C16").Select
    returnbutton = MsgBox("If this is the estimate, you need to push the Reset PVDS button next!", vbOKOnly, "NOTICE")
    optFourPager.Activate
End Sub

 

[m6]

Dear Zanthras,

I learned the point was to Activate the worksheet where the work is being done. I couldn't find this anywhere.

Thanks for the help, it worked like plug and play.

I was trying use to option button from the report page to copy/paste on the Math Page then return focus back to the report page.

If anyone is searching for this problem. Here was my final code.

Private Sub optFourPager_Click()
Sheets("Math Page&quot.Activate
With ActiveSheet
.Range("D17&quot.Select
Selection.Copy
.Range("C16&quot.Select
.Paste
End With
Sheets("Report Page&quot.Activate
Range("C16&quot.Select
returnbutton = MsgBox("If this is the estimate, you need to push the Reset PVDS button next!", vbOKOnly, "NOTICE&quot
End Sub