How to make unclickable Option Button based on value?

YW3

YW3

New member
Joined
Jan 17, 2017
Messages
5
Reaction score
0
Points
0
TLDR: I have 2 columns. Column B contains 15 of my option button (Not sure if this'll affect but I just inserted one button and dragged the cell down to get 15 so the name for each option button is different). Column C contains the text values I have (contains VLOOPUP formula to give Yes, No or Perfect).

I want to make the option buttons unclickable (and preferably grayed out) if the value in column C is "No"

All that help I read that makes an unclickable option buttons uses macro. I've never used macros before, so if your solution is via macros, I would appreciate if you could explain it too.

If you have another suggestion instead of option button, go ahead please!

Thanks.
---

The big picture is something like, there are 10 baskets. Depending on what kind of basket the user wants, which s/he fills in the factors in different sheet, to generate the answer in column A.

Based on column A, column C would give the "Perfect" value. Of course, the user could get other similar baskets, which "Yes" comes in at. Then there are baskets that don't fit at all, so "No". Column D would just have the baskets names beside these answers. Eg. C1, Yes, D1, Basket Red; C2, No, D2, Basket Blue; C3, Perfect, D3, Basket Orange. The values in column C changes, the ones in column D doesn't.

I have the option buttons in column B, next to the values (Yes, No, Perfect) so the user can pick whether they want the perfect basket or other similar ones. It's all side-by-side, option button (column B), Yes/No/Perfect (column C), basket names (column D).

That's not where it ends. In column E, I have a table that shows the details of the basket the user would select in column B (the option buttons). That's why I need option buttons, because there can only be one item selected at a time for the table to work.

What I want is to make the option buttons in columnn B unclickable/unable to select if the value in column C gives "No". Or something similar. Cause column E doesn't care for the value in column C, only what the user pick in column B. And I don't want the user to go "Opps, I didn't know those baskets wasn't my type. I could select it, so it's your fault!"

Basically, I want to reduce human error/mistakes.
 
Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
 
Unable to edit my first post. I've asked this question on another forum before, but have yet to gotten a satisfactory answer. So I hope here would have a better chance at someone knowing the answer see this.

...Aaaaand I can't post links because I'm a new user. :') There isn't any answers there but the website is at msofficeforums(.)com/excel/33811-help-how-make-unclickable-option-button-based.html
 
What seems to be the closest I can get, but still a failure:

"Private Sub OptionButton1_Click()

If Range(C5).Value = "No" Then
OptionButton1.Enabled = False

Else
OptionButton1.Enabled = True

End If

End Sub"

The option button ended up being disabled regardless of the value in column C. I can't tell what's wrong. Is it because of the VLOOPUP in C5? Is it the range? Is C5 not accurate because I got merged cells everywhere? Should I be using Cells instead? I don't understand Cells, is it Cells(5, 3) for C5? I have no idea.
 
Last edited:
Perhaps post a sample sheet?( Click Go advanced - Manage attachments)
 
How detailed should the sample sheet be? Cause nearly every cell has a formula, interlinking to other worksheets, that contains so many steps and more formulas. Or would pure text do?

I tried. View attachment Dummy.xlsm

Weird, I keep getting this Privacy Warning now and the option button is still clickable if you change the value to "No". Well, every other attempt still gets the debug message.

Edit: Ah. I made I mistake with the option button value. My actual document doesn't use ActiveX- Option Button but Form Control- Option Button.
 
Last edited:
The attached uses your last file with activex option buttons.
Each button's top left corner needs to be within the cell it's in.
I didn't notice your comment about activex/forms type option buttons until too late.
Currently there's a button to run the nmacro, instead it could be triggered by a sheet_calculate event or some such but you removed all formulae so no calculation takes place!
If I get time I may look at the same with forms option buttons.
This is the code:
Code: 
Sub Macro5()
For Each ob In ActiveSheet.OLEObjects
  'ob.Select
  If TypeName(ob.Object) = "OptionButton" Then
    If ob.TopLeftCell.Column = 2 Then
      If UCase(ob.TopLeftCell.Offset(, 1).Value) = "NO" Then
        ob.Object.Value = False
        ob.Enabled = False
      Else
        ob.Enabled = True
      End If
    End If
  End If
Next ob
End Sub
 

Attachments

  • ExcelGuru7231Dummy.xlsm
    25.2 KB · Views: 17
The attached has a Sheet2 with forms option buttons.
Code:
Code: 
Sub FormsOptionButtonsSet()
For Each ob In ActiveSheet.OptionButtons
  If ob.TopLeftCell.Column = 2 Then
    'ob.Select
    ob.Display3DShading = False
    If UCase(ob.TopLeftCell.Offset(, 1).Value) = "NO" Then
      ob.Value = False
      ob.Enabled = False
      ob.Visible = False
    Else
      ob.Enabled = True
      ob.Visible = True
    End If
  End If
Next ob
End Sub
 

Attachments

  • ExcelGuru7231Dummy.xlsm
    31.7 KB · Views: 25
Thanks, p45cal for all your help.

Delete as appropriate:
That worked well
That was a waste of time
 
You must log in or register to reply here.
Back
Top