Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set OptionBox value via VBA
I just though I'd post the results of my experience trying to do something
which I would have thought would be simple. In the end it was simple, but you have to know some somewhat obsure things to do it right, apparently. The task: An (Userforms/MSForms, NOT an ActiveX) Group control and two enclosed Option button controls (one for Yes, one for No) are on a spreadsheet. Set the option button value(s) to Yes or No via VBA code as read from a database field. How would you think this would be done? Set the value of the ONE control which is "Picked"/On to True(=-1 in VBA), right? Wrong. Set it to the constant value xlON (= 1 in VBA). Also, it seems to depend upon whether the control is bound to a cell in a workbook or not. According to my recent experience, if you do this: ActiveWorksheet.Shapes("OptionButton1").ControlFor mat.Value = xlOn This will set an option button to ON. Interestingly, if you wish to set that same option button to OFF you need the xlOFF constant, whose value is NOT 0 (which is what VB programmers might intuitively think it ought to be), it is -4146. go figure. However, this may - or may not - work properly if you are setting the value of a control that IS bound to a worksheet cell (i.e. ActiveWorksheet.Shapes("OptionButton1").LinkedCell ="B19" ). In that circumstance, you may be better off in changing the option button values by changing the cell value directly. But, what values do you use to set the option buttons in the cell? xlON and xlOff, right? wrong. The values you need to set in the cell are 2 or 3 in this example. 2!?? 3!??? Where the HECK did those values come from? I haven't a clue. Ths only way I found out what the values to use was to set up the control, bind them to a common cell, and click them each, observing what values appeared in the cell. Well, at leasst it worked, and hopefully, this should help someone else puzzle through this forms-control-in-worksheet weirdness too. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set OptionBox value via VBA
An (Userforms/MSForms, NOT an ActiveX) Group control Controls from the Controls command bar are Userform/MSForms ActiveX controls. Controls from the Forms command bar are Excel Forms controls, originally designed for Dialog Sheets. The naming of such things is a bit confusing. (And just to add to the confusion, ActiveX controls are in the Windows Forms, not MSForms, family when you look them up by PROGID in RegEdit.) If you go into the Object Browser in VBA, you'll see the ActiveX controls (if you have the MSForms typelib loaded), but you won't see the Excel controls unless you turn on Show Hidden Members (right-click in the Object Browser and choose this from the popup menu). In code, you would reference an ActiveX option button as Dim OptButton As MSForms.OptionButton and you would reference an Excel option button as Dim OptButton As Excel.OptionButton Of you omit the typelib qualifier, e.g., Dim OptButton As OptionButton the object used will be the one found in the typelib with the highest priority, which is typically Excel, not MSForms. The Excel.* controls are considered obsolete but are still supported. When you put multiple Excel option buttons in a group box control, they all have the same linked cell, regardless of what you assign to the LinkedCell property. The LinkedCell used is the most recently assigned LinkedCell value of any Option Button. (This may not be the most recently created OptionButton. Create 4 option buttons in a group box and assign the LinkCell properties to A1, A2, A3, and A4 respectively. You'll see that only A4 changes, and gets a value of 1, 2, 3, or 4, depending on which OptionButton was clicked, the value being the order in which they were created. Now change the LinkedCell property of OptionButton2 to A10. Now, all Option Buttons are linked to A10. Confusing? Yeah.) The value in that cell references the option button in the group box that has a value of xlOn (=1). The number here is the option button in the order in which it was created within the group box. This number is accessible via the Index property: Debug.Print Sheet1.OptionButtons("Opt3").Index You can see these properties and values with code like Sub BBB() Dim R As Range Dim WS As Worksheet Dim OptB As Excel.OptionButton Set WS = Sheet1 Set R = WS.Range("C1") For Each OptB In WS.OptionButtons R(1, 1) = OptB.Name R(1, 2) = OptB.GroupBox.Name R(1, 3) = OptB.Index R(1, 4) = OptB.Value R(1, 5) = OptB.LinkedCell Set R = R(2, 1) Next OptB R.Resize(1, 5).EntireColumn.AutoFit End Sub If you want to programmatically change the value of an OptionButton, change the linked cell to the Index of the OptionButton you want to change: Range("A4").Value = 2 or set the OptionButton's Value property to xlOn or xlOff. E..g, sheet1.OptionButtons("Opt2").Value=xlOn If you want to clear all option buttons (none selected), change the value of the linked cell to a value < N or N where N is the number of option button in the group box. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Mar 2009 07:53:03 -0700, Mark Burns <Grandpa wrote: I just though I'd post the results of my experience trying to do something which I would have thought would be simple. In the end it was simple, but you have to know some somewhat obsure things to do it right, apparently. The task: An (Userforms/MSForms, NOT an ActiveX) Group control and two enclosed Option button controls (one for Yes, one for No) are on a spreadsheet. Set the option button value(s) to Yes or No via VBA code as read from a database field. How would you think this would be done? Set the value of the ONE control which is "Picked"/On to True(=-1 in VBA), right? Wrong. Set it to the constant value xlON (= 1 in VBA). Also, it seems to depend upon whether the control is bound to a cell in a workbook or not. According to my recent experience, if you do this: ActiveWorksheet.Shapes("OptionButton1").ControlFo rmat.Value = xlOn This will set an option button to ON. Interestingly, if you wish to set that same option button to OFF you need the xlOFF constant, whose value is NOT 0 (which is what VB programmers might intuitively think it ought to be), it is -4146. go figure. However, this may - or may not - work properly if you are setting the value of a control that IS bound to a worksheet cell (i.e. ActiveWorksheet.Shapes("OptionButton1").LinkedCel l="B19" ). In that circumstance, you may be better off in changing the option button values by changing the cell value directly. But, what values do you use to set the option buttons in the cell? xlON and xlOff, right? wrong. The values you need to set in the cell are 2 or 3 in this example. 2!?? 3!??? Where the HECK did those values come from? I haven't a clue. Ths only way I found out what the values to use was to set up the control, bind them to a common cell, and click them each, observing what values appeared in the cell. Well, at leasst it worked, and hopefully, this should help someone else puzzle through this forms-control-in-worksheet weirdness too. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set OptionBox value via VBA
Chip,
Thanks for the clarifications. The really frustrating thing is that 1) the ID #s for the controls don't seem to be exposed anywhere in the API (or at least not in the Excel GUI), and 2) the behavior of the controls with/without being linked to a worksheet cell is not clearly documented anywhere that I could find. Of course, the cute excel xlON/xlOFF constants and their nifty, not-exactly-intuitive values doesn't exactly help avoid any confusion any either (it wouldn't be so bad if those constants were enums for the .value property, but apparently that was too obviously self-documenting a situation to hope for). Do you know if anybody's made anything like a comprehensive document/whitepaper/website/guide to "The Proper care and feeding of Excel and ActiveX controls in both Userforms and on Worksheets."? I could also easily imagine the need for a similar set of guides/documents for the multitude of SHAPE objects (_not_ counting the controls). One specific question I have is this: where is there a document that lists all the various valid Adjustment points for the various autoshapes? ....or is there no such animal anywhere? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set Optionbox value from code? | Excel Programming | |||
Code for OptionBox | Excel Programming | |||
Excel Checkboxs, Optionbox | Excel Discussion (Misc queries) | |||
optionbox checkbox | Excel Discussion (Misc queries) | |||
OptionBox error checking | Excel Programming |