Dealing with "Option Buttons" on the forms toolbar...

[TonyU]

[tt]
I have two option buttons with cell links to A70 and I'm trying to shade the backgrounds of cells F57 & H57 if the options button with value of 2 is pressed.

I guess my questions is, would it be easier to do it thru conditional formatting or vba?

I tried conditional formatting but it did not work.

thanks

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[RobBroekhuis]

Conditional formatting only works on the contents of the cells you're trying to format. If you are trying to format unrelated cells, you'll need to use VBA. Exactly how depends on how you've configured your option buttons, but it would probably be some formatting code inside the optionbutton_click event handler.
Rob

 

[schase]

[tt]
How about some pseudo code to better understand "Never underestimate the power of determination"

Stuart

 

[TonyU]

I can assign an even handler to a form option button?

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[schase]

Please note - the earlier post was not by me.

Someone signed in about the same time I did - and they captured my username - and proceeded to post with it. "Never underestimate the power of determination"

Stuart

 

[RobBroekhuis]

Yes, any control on a form can have all sorts of event handlers. Double-click on the control in the VBE environment to automatically generate the default event handler (for an option-button, the click event).
Rob

 

[TonyU]

You're losing me Rob, can you speak english to a non-VBA rookie.

I'm a Web/VBScript/ASP developer. VBA is not my thing and you're assistance tho great is not making sense. Thanks

As in the past I've been able to trouble-shoot thru vba code to grasp what it's doing but I don't know it's syntax per say. ;-)

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[RobBroekhuis]

You say you have a userform. You created this userform (I assume) using the Visual Basic Editor, VBE. When you open the form in VBE (i.e., the visual image of the form, where you can add controls etc), you can double-click on any control. This will take you to the code page for that userform, and automatically add an event handler sub, such as

private sub optionbutton1_click()
end sub

You can put VBA code inside that sub that you want to execute whenever the button is clicked, for example

private sub optionbutton1_click()
if optionbutton1 then
sheets("data&quot.range("F57&quot.interior.color=vbRed
end if
end sub

Does that help?
Rob

 

[TonyU]

See, that makes more sense to me know. Thanks

But...
I don't have a userform, I simply dragged two option buttons to a cell in excel and assinged a reference cell so when I click on one the reference cell displays 1 and 2 when the other button is selected.

I wanted to know how to make a different cell's background a different color based on the value of 1 or 2

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[RobBroekhuis]

Ah, that's a bit tricky. The optionbutton on the forms toolbar is linked to a control cell, and when the option button is clicked, the worksheet_change event is not triggered. I would change the option button to the kind you find on the "Control toolbox" toolbar. It's a different kind of control altogether, which has its own event handlers, residing on the worksheet's code page.
If you want to go that route, try the following:

Select View-Toolbars-Control toolbox
Use the toolbar to put option buttons on your worksheet.
Double-click on the option buttons to automatically generate the click event handler, and put code like what I suggested above in the handler (whatever you need to do the formatting change)
Back at your worksheet, click the "Design Mode" button on the control toolbox (top left), to get out of design mode. This will allow the buttons to be used, so you can test your code.
Rob

 

[TonyU]

I modified your suggestion with this

Private Sub optionbutton1_click()
If OptionButton1 Then
Error Line: Sheets("sheet1&quot.Range("B2&quot.Interior.Color = vbRed
End If
End Sub


-----------

And get a this

Got ASP Error(s)? =

http://www.aspfaq.com

​

 

[RobBroekhuis]

You'll have to substitute the name of your worksheet in the sheets() bit, and similarly select whichever cell you want to have the color changed (this code will change cell B2).
Rob

 

[TonyU]

I thought I did by replacing "Data" to "sheet1" and as a Test, I'm coloring cel b2 just to see if it works.

------
But now I get this

perhaps this could give you a better idea as to what I'm doing wrong.

​

​

 

[RobBroekhuis]

Your Excel version may deal with colors just slightly differently (it works for me) - I suggest you use the macro recorder while you set the exact formatting you'd like the cells to receive, and then paste that code into your event handler.
Rob