How to change the value of a control from a VBA module?

[General Ledger]

Dear All,

In an Excel 2003 workbook, I have several sheets. On one sheet I have Option Buttons. I am trying to change the value (true or false) of the buttons with code in a VBA module.

I had code in Module1 to set the value of an Option Button:

OptButInternational.Value = False

I get a run-time error '424' object required.

I am thinking you can only change a property of a control using code in the sheet where the control is. Correct?

I put a bit of code in the sheet:

Sub OptButInternationalFalse()
OptButInternational.Value = False
End Sub

When I run this code from the VBE in the sheet, it works fine.

In Module1 I put code to call the sub:

Call OptButInternationalFalse

When I run the code in the Module, I immediately get and error:

Compile error: Sub or Function not definded

Is the problem that code in Module1 can not call a sub in a sheet?

Any ideas or options?

Thanks,

GL

 

[rs2k]

Hi GL,

Have you tried referencing the worksheet;

Sub OptTestFl()
Sheets("Sheet2").OptionButton1 = False
End Sub
Sub OptTestTr()
Sheets("Sheet2").OptionButton1 = True
End Sub

Colin

 

[Leith Ross]

Hello General Ledger,

You almost answered your own question. You need to prefix the object with the worksheet object it belongs to. Since there are 2 types of option buttons and I don't which you are using, here is an example of how to clear both types. Both types can be set using True. However, to clear them requires 2 different values. The Forms type using the constant xlOff and the Control Toolbox type uses False. Place the code in a VBA module. One of these lines will error. Remove that line.

Sub ClearOptionButton()

  With Worksheets("Sheet1")
   'Forms type
    .OptionButtons("OptButInternational").Value = xlOff

   'Control Toolbox type (aka ActiveX)
    .OLEObjects("OptButInternational").Object.Value = False
  End With
  
End Sub

Sincerely,
Leith Ross

 

[General Ledger]

rs2K and Leith,

Both of you got it correct. The trick was referencing the worksheet of the option button. Everything works fine.

Thanks so much,

GL