Creating a list of buttons to make selection

[modytrane]

I haven't used buttons much.
I would like to create a list of projects in column A.
Then the user would select one of the projects. This can be done by clicking on the corresponding cell in column B. So I would lkie to see buttons in column B for this. OR if someone can suggest another way, e.g. highlighting the project name in column A or putting an x in corresponding cell in column B.
I can make this last scenerio work, but I don't know how to assure that only one project can be selected at a time.
In other words if I have selected project in ROW 12 and then I want to see project in ROW 17, I should be able to select 17. And 12 should be deselected automatically.
Hope its clear enough to understand what I want.
Thanks,
modytrane

[modytrane]

Let me give an example.

Column A will have a list of projects [up to 150].
I would like the user to be able to select a project somehow.
I have other sheets which will pick up the selected project name and display some summary information for that project in sheets 2 and 3.
The use should be able to scroll down to another project and select that.
But when he selects a new project, the previous one should be deselected.
Thanks,
modytrane

[modytrane]

I figured out that I can put a number of Options Buttons in a Group Box and it allows you to select only one at a time. That's what I was looking for. Now How do I read the value of the button that is selected or use that in a formula. I will have a column full of buttons, all in a group box. Next to each button, there would be a project name. I simply want to know which button has been selected and copy the name of the corresponding project name to another cell. Then I will take off from there.
Thanks,
modytrane

[Leith Ross]

Hello modytrane,

If you want to know the name of the option button, and it is from the Forms toolbar, you can use the following code in a macro to get the name of the option button. With the option button's name, you can retrieve the caption. You would need to attach this macro to the button.

Sub GetProjectName()

  Dim Btn As String
  Dim OptBtn As Object

    Btn = Application.Caller
    Set OptBtn = ActiveSheet.OptionButtons(Btn)

    'Save the name in a cell

End Sub

Sincerely,
Leith Ross

[modytrane]

I am attaching an example of what I am looking for.

In the attached sample file I need help witha few items.

How do I get rid of the descriptor "Group Box 1"?

I also need to have over 75 buttons in the box. Is there an easier way to add them and label each one? [the label can be a simple as "1,2,3,4...."].
Next to each button there would be a list of project in the next column.
When a button is clicked [or selected], the project name next to it would be copied to cell I5.

Or if someone has a different idea to achieve same result, I would like to look in to it.


Thanks for any help,
modytrane

[SOS]

Hi modytrane,

I've created a sample workbook (somewhat crude) but it's all I can do and I want to help.

I created a DropDown from the Forms Toobar and filled it with values from O1:O4 of Sheet 1.

Then added the Sub DropDown1_OnChange macro that populated cell I5 with the value from that DropDown.

Perhaps this could get you going.

I couldn't figure out how to reset the DropDown afterwards though.

Workbook attached