Product Configurator Using dynamic Option buttons.

[N_K]

Hi All,

I'm new to this group and felt exited by going thru various posts. I hope someone could help me with the problem that i'm trying to resolve.
I'm currently designing an excel based product configurator. I've completed most of it by linking the back end data and now what i'm really looking for is programatically assigning Option buttons for the items under a group so that the end user can select one of the options available under each group.
please refer to the table below as an example.

Col-A............Col-B............... Col-C ..............................Col-D........... Col-E
Group..........Item.................Option Button............T / F(selection)........Price
-------------------------------------------------------------------------------------------------------------------
Fruits.............Apple........... Option Button-Y.........True...........Look up based on Col D
......................Orange.........Option Button-N.........False
......................Grapes.........Option Button-N.........False
Vegetables....Carrot
......................LadiesFinger
......................Potatoes
Beverages.....Pepsi
......................CocaCola................................................Grand Tot of Price based on selection.
---------------------------------------------------------------------------------------------------------------------
I got this working by creating Active X option buttons from the control tool box manually. However, the issue is for different products the number of Group's and Items under them may vary. Hence, need for a dynamic logic that can loop thru the data and create Groups and option buttons based on number of cells populated.
I can re-arrange the data as suggested if that reduces the coding complexity. I'm fairly new to coding so any help with a VBA code that could bring out the desired results would be highly appreciated. Many Thanks !

[N_K]

Attaching sample file.

[JBeaucaire]

Along with being hard to align, radio buttons are more plumbing than you really need. Here is your sheet back with a "doubleclick" function in column C. Doubleclick to add/remove checkmarks.

[N_K]

Thanks for your reply ! Apparently, I need to implement a functionality where in only one Item under a group should be checked.at a time or I may end up having wrong total price summed up due to duplication of Items under a group. the example I quoted may not necessarily illustrate that but may be if you consider configuring a PC computer you'll probably understand what I'm talking about better. So under each category I need to select one component to build my system and the total should sum up the price of my configuration.
Thanks !

[JBeaucaire]

As long as your layout doesn't change, this will work. When you doubleclick in C, it will check column A to determine the First Row (FR) of the "section" and the Bottom Row (BR) of the section. Then it will clear any existing checkmarks in that section prior to putting in a new one.

[N_K]

I tried selling this solution to my audience but unfortunately they are more inclined to have option buttons instead. ( used with the legacy system that was managed manually ).
you've been very helpful in providing a solution but if you could suggest me something that I could work up that'll be really great.
Thanks again !

[JBeaucaire]

Oh, I did that. The use of form objects ON the sheet I believe to be clumsy and results in an endless amount of maintenance (by you... or us...) anytime you make the smallest of changes to your data.

I would do it the way I showed you proudly and insistently.

[MarvinP]

Hi N_K,

I agree with Jerry. Putting radio buttons on an excel sheet just complicates the problems. His method of double clicking in column C to get some data in the cells is much cleaner when working with Excel. We see problems like this frequently, where a background color or font changes and the user wants to do things based on this cell format. It is VERY hard to capture cell attributes compared to data in a cell.

I'd suggest you imply that the "legacy" products are old and dying because of overhead like radio buttons. Tell them that two experts on the Excel Help Forum guided you to this new and MUCH BETTER method.

[JBeaucaire]

In the spirit of giving them the "appearance" of radio buttons without having to actually have them, this version of the same method uses the WINGDINGS font instead to fake radio buttons being selected.

[snb]

If you want to format a worksheet like a userform, don't use a worksheet but a userform instead.
Cfr. the attachment.

[N_K]

snb - Thank you for your help. unfortunately, the original version of my problem has many permutations and combinations of Group and associated items under them and managing a form would not be feasable. However, I have other related tasks for which I can definetely make use of this. Appreciate it !.

Jerry - I made use of your code and edited it to have radio buttons included which the users are liking. I'll run the show with this as long as I dont end up in trouble. I've saved the file you provided with the method of double clicking and will surely make use of it as an " Enhancement " for the future :-)
btw, the updated appearance to resemble a radio button is cool...
Thanks again !

Here's my scribble making use of what you had provided originally... let me know what you feel about it.. ( File Attached aswell )

Option Explicit
Sub Add_Option_Button()
Dim cell As Range, MyRNG As Range
Dim BR As Long, FR As Long, LR As Long
Dim Target As Range
Dim group_name As String
Dim counter As Integer
counter = 1
If ActiveSheet.OLEObjects.Count > 0 Then
    ActiveSheet.OLEObjects.Delete
End If
Range("D:D").ClearContents
Range("A1").End(xlDown).Select
While ActiveCell.Text <> ""
    group_name = ActiveCell.Text
    Set Target = Range(ActiveCell.Address)
    FR = Target.Row
    LR = Range("B" & Rows.Count).End(xlUp).Row
    BR = WorksheetFunction.Min(LR, Range("A" & Target.Row).End(xlDown).Row - 1)
    Set MyRNG = Range("C" & FR, "C" & BR)
    For Each cell In MyRNG
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
    DisplayAsIcon:=False, Left:=cell.Left + 5, Top:=cell.Top + 2, Width:=cell.Width - 6, Height:=cell.Height - 3)
        .Name = group_name & counter
        .LinkedCell = cell.Offset(0, 1).Address
        .Object.Caption = ""
        .Object.SpecialEffect = 0
        .Object.GroupName = group_name
        
    End With
    counter = counter + 1
    Next cell
    ActiveCell.End(xlDown).Select
Wend
Range("A1").Select
End Sub

[snb]

the original version of my problem has many permutations and combinations of Group and associated items under them and managing a form would not be feasable

I very seriously doubt your assertion a form would not be feasable.