Simple radio button form help

[Scot B]

Greetings,

We are trying to create a simple Excel form that a field engineer could use.
We're having trouble with two concepts...

In one section we'd like him to choose from 2 options, "a" or "b". If he
chooses "a", then it would pull a price from cell A1. if he chooses option
"b", it will choose the price from cell B1.

In a second section, we'd like him to choose 1 option from about 10
possibilities. I assume that will be very similar to the first section.

Can someone teach me how to create a simple form with the 2 choices, then
pull from a different cell depending on which choice is made?

Thanks for your help!!!

Cheers,

Scot B.

 

[Greg Glynn]

Scot,

Hit ALT-F11 and get into the editor

Click INSERT / USERFORM

Create a form with four items:

(1) A 'Frame' (the icon with the box and xyz on it). Inside that put
two radio buttons (because they are inside the frame they are
logically linked to each other)

(2) A ComboBox below the frame.

(3) A BUTTON - Change the Caption to CANCEL (this will default being
called 'CommandButton1')

(4) A BUTTON - Change the caption to OK (this will default being
called 'CommandButton2')


The code to display the Form is:
USERFORM.1 SHOW

The items on the form are then referred to as:
USERFORM1.OptionButton1
USERFORM1.OptionButton2
USERFORM1.ComboBox1
USERFORM1.CommandButton1
USERFORM1.CommandButton2

Check this forum for how to load up a ComboBox, especially
the .additem keyword (search for "combobox .additem")

You can then code for things like:
If USERFORM1.OptionButton1 = True
and also things like:
If USERFORM1.Combobox1.value = "Bill Blogs" or MyCustomer =
USERFORM1.Combobox1.value

Hope this gets you started.

 

[Earl Kiosterud]

Scot,

The easiest, presuming you want only one pair of option buttons for the whole sheet for a or
b, is to create two options buttons with the Forms Toolbar. Right click them and assign
them (cell link) to a common cell. You'll get a 1 or a 2 in the cell depending on which
button is engaged, which you can test in your formulas where you pull data from.

If you want a pair of buttons for each row, it doesn't really work, as all the option
buttons are part of the same group (click one, and the others go off). A similar solution
using option buttons from the Controls Toolbox can be used. This pretty much only works for
a fixed number of rows, known in advance.