MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

option button help i think

  Asked By: Mindy    Date: Mar 16    Category: MS Office    Views: 858

I have two sheets, 1) Price 2)product

In the Price sheet col D row 5 down row 300 list retail prices

In the price sheet col E row 5 down row 300 list of COST prices

I have two option buttons on product sheet.

On product sheet i have col D row 14 is were the list of prices
(retail list) and the (cost List) go down to row 314

What i need is when option button 1 click on list from price sheet col
D row 5 to row 300 is placed in

when i click on option button 2 i need the list from price sheet col
E row 5 to row 300 placed in

Could someone help me with this code i can't seem to find even an
example of this.



3 Answers Found

Answer #1    Answered By: Anuja Shah     Answered On: Mar 16

Great handle, by the way.

You can easily do this yourself, as follows:
1) On sheet  Price, define two named ranges, Retail_Prices = D5:D300, and
Cost_Prices = E5:E300

You do not have to work with named ranges, but I find it more elegant.

2) Record a macro, call it Button1Retail
Select sheet Product
Select cell D14

Stop macro recording

3) Your macro should look something like this in the VB Editor:
Sub Button1Retail()
' Macro recorded on today's date by dummyinDOS


End Sub

4) Now you can add in your own code for the copying bit. Try the following, and
see what works for you:
Sub Button1Retail()
' Button1Retail Macro
' Macro recorded 07/08/2006 by dummyinDOS

Application.Goto Reference:="Retail_Prices"
End Sub
There are more elegant ways of doing this, but for your purposes, this will

Now assign this macro to your first button, and it should work.

Copy the code for the second button, modify the range to be copied from, and
assign to the second button.

Answer #2    Answered By: Emma Campbell     Answered On: Mar 16

I had an entirely different approach. I'm confident that you know a
great deal more about this than I do, but perhaps you could tell me
what's wrong with my thinking here. I'm trying to understand the
perspective of a seasoned programmer on the many ways of skinning
Excel cats.

I would have created buttons that set an arbitrary cell to 1 or 2,
then put formulae in each of the target cells on "Products"
referencing this cell and setting a value equal to Retail Price or
Cost Price depending upon whether it is 1 or 2.

This would allow for dynamic changes to the Prices, and more
importantly, would be easier to change next year when a third column
is added and the person who wrote the first spreadsheet has left
town, abandoning the work to someone who knows no VBA.

Whaddya think?

Answer #3    Answered By: Kellie Bishop     Answered On: Mar 16

Very good solution. I like it. And the formulas would have an offset
function in them, with your arbitrary cell containing the column to be
referenced (1 or 2).

Didn't find what you were looking for? Find more on option button help i think Or get search suggestion and latest updates.