VBA option button - look up

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
Hi,

I'm struggling with VBA syntax/commands/everything, I'm hoping this is a simple fix for someone with more experience/knowledge than me.

I have a userform with five options buttons and a command button to confirm the selection.
The names and labels of the option buttons appear in a named range on my worksheet. (Named, "Market").
The five items in the named range each have an associated value. (Named, "Price").

For now, I want the user to be able to select one of the five options and for the price of that option to appear in cell I2.

Here's the code I have managed to piece together so far that is not working:
Code:
Private Sub Confirm_Click()

Sheets("Sheet1").Range("I2") = Application.WorksheetFunction.XLookup(Control.Name, Range("Market"), Range("Price"))

End Sub

What is the correct syntax for "the name of whichever option button is selected" that I hope I can replace "Control.Name" with in the formula to make it work?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
Update:
So I figured out a solution.

By listing all the possible outcomes with If... Then... ElseIf... and writing the names of each element in the named range, then it works perfectly.

Out of curiosity, given that each option button has the same name as the elements in the list, would it be possible to write code to the effect of:

Dim c as string
c = 'the name of whichever option button returns TRUE'

Then use c as a variable in any formula/calculations resulting from that?
 
Solution

Forum statistics

Threads
1,176,499
Messages
5,903,413
Members
435,028
Latest member
excelvbanoob420

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top