[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Form Control - set value of Option Button

Folks,
In my worksheet I have two option buttons as form controls. Each is assigned to a different macro.
When the workbook is activated I would like to set the "Format Control - Control - Value to be 'Unchecked' " for both form controls.
I've even tried changing the named in the "Name Box" from "Option Button 1" to "EvalPos" but that didn't work. If it was an ActiveX control - no problem. But it is a form control.
0
Frank Freese
Asked:
Frank Freese
  • 5
  • 4
  • 2
1 Solution
 
Rory ArchibaldCommented:
You should be able to use a loop like:

Dim ob as OptionButton
For each on in sheets("sheet name").OptionButtons
   ob.Value = -4146
Next ob

Open in new window

0
 
Frank FreeseAuthor Commented:
Is the code from above line 2 correct?

For each on in sheets("sheet name").OptionButtons
0
 
Frank FreeseAuthor Commented:
I get an error:
Object does not support this property or method at line 2.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
Sorry - typo - it should read
for each ob in sheets("Sheet name").optionbuttons

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Only one option button on a sheet can be checked (or unchecked) at the same time.
0
 
Martin LissRetired ProgrammerCommented:
You could use checkboxes instead.
0
 
Martin LissRetired ProgrammerCommented:
Another way you could do it would be to put each option button inside it's own 'Group Box' control.
0
 
Frank FreeseAuthor Commented:
I'm still making myself clear and I apologize.
Regardless of which form control I elect to use - radio or check mark, when the worksheet is activated I would like to clear any checks marks. Now when I open the worksheet the option button and /or check box retains the last selection and is not cleared. I do know when I right click on a form control select Format Control then the tab labeled Control there are two possible values available "Unchecked" and "Checked". I do not know how to set that value to "Unchecked" when the worksheet is activated for the two form controls I am using.After worksheet activatedWhen I tried Rory's suggestion at  40273265 and corrected the typo I received the message "Object does not support this property or method."
0
 
Martin LissRetired ProgrammerCommented:
Worksheets("Sheet1").Shapes("Name of checkbox").OLEFormat.Object.Value = Checked  'or Unchecked
0
 
Frank FreeseAuthor Commented:
There you go my good man - you nailed it
thanks to all
0
 
Frank FreeseAuthor Commented:
thank you very much
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now