Option Buttons/Refresh

[sneakyzeal]

Ok this is a little hard to explain. I have a sheet with calculations on
it, and three radio buttons to click in order to change the values in
the cells (and result in a different answer "Selling Price"). Problem
is, on another sheet I have a list of "Products", with a row for each
type of product. For example:

First sheet is for Product A, made of wood, metal and plastic (radio
buttons 1, 2 and 3). Second sheet has a list of prices for Product A
wood, Product A metal and Product A plastic. Then there's a third sheet
for Product B wood, metal, plastic and so on..

I can't find a way to link the 3 different selling prices in the first
sheet to the list in the second sheet properly, as they end up in the
same cell and are only changed by the radio buttons. I've sort of been
able to separate them out, except the issue is that if you click on
option 1, then change a variable, it doesn't update the result in the
second sheet until you click on option 1 again.

I've spent forever on this and can't find a solution.. this is the last
resort!

Anyone have any ideas? Hopefully I've explained it clearly enough..

Thanks so much.

 

[gearoi]

Can't conceptualise this sorry - maybe bung up a cut-down version of th
spreadsheet?

It sounds like there's a small error somewhere to me.

First try an event sub though - make the sheet recalculate afte
changing the radio buttons

 

[sneakyzeal]

Hi Gearoi

I've done a very brief dummy file up with a couple of notes.. hopefully
you'll understand what I mean. It's a bit of a messed up problem due to
bad design!

Any ideas are hugely appreciated.

Kind regards


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3781 |
+-------------------------------------------------------------------+

 

[Jon]

Your entries in H10:H12 are entered by the macro's associated with the
option buttons. If you change anything on the sheet the macro doesn't fire.
Really an easier way would be to have each option button when clicked, throw
a 1, 2 or 3 (or some meaningful number, cost or whatever) into one cell on
the sheet, then have formulas in H10:H12 calculate everything for you based
on profit margin, wood type etc. Otherwise, you will have to create
variables for everything and then use a change event to do all this for you.

Jon

 

[sneakyzeal]

Hmm.. I'm not entirely sure what you mean Jon, it sounds like a lot o
redoing what I've already done. I've found an alternative which isn'
quite perfect, but hopefully my lecturers won't catch me out

Thanks anyway though for your help

 

[Jon]

Actually I missed the H28 entry. This is exactly what I was talking about in
the last post. So, what you need to do is not paste the Value of B6 into the
h11:h13 which is what you are doing, but paste the formula itself so that it
evaluates

for your Macro BCBeechTotal use:
Worksheets("Bookcase - BC940").Range("H11").Formula = "=B6"

Follow the same idea for the other 2 macros

This seems kind of redundant though, Why not just have the formulas already
in those cells? (that is what I meant in my previous post)

How are you putting the 1,2, and 3 into H28? I can't seem to find it.

Jon

 

[sneakyzeal]

I tried what you suggested.. if you paste the formula itself and not the
value, the numbers in all three cells become the same each time you
click a different option (when I want the different result from
clicking each buttons showing in H11,12,13).

H28 is a reference point for those option buttons (Format Control >
Control), so 1 is returned if the first one is selected, 2 for the
second.. etc. Other cells in the sheet (which I didn't include in that
dummy file) use it in an If statement to decide what values should be
used in calculations that reach the value in B6. For example: A column
has multiple rows with "if beech (H28=1) is selected then cell x should
be $5, if rimu (H28=3), x should be $7" and so on. Then B1 links to the
total of that column, which in turn affects B6.

Hope that makes sense!

 

[Jon]

I think you will then need 3 columns of data to calculate for each wood
type, profit margin etc. The way you have it now all the calculations are
done with the result in B6. Every time that button a button is clicked that
column varies.

Do you really need the total for each type in a separate row or could you
have one cell that changes the wood type depending on the button chosen,
with the appropriate price in the cell to the right of it?

Jon

 

[sneakyzeal]

Hi Jon

Doesn't need to be a separate row, no.. I just need the different
amounts to show up in the Price List sheet at the same time. I tried it
out and figured that having them in 3 separate cells was the only way to
have them all showing at the same time (rather than one cell showing the
beech amount when beech was selected and the other two showing nothing
because the other buttons aren't selected).

The problem is not so much that the amounts don't change when each
option is selected, it's that the amount doesn't change for the
selected option if any other cells (which affect the selling price) are
changed.

So frustrating! I'm really appreciating your help though, thanks

 

[Jon]

Ok, Try this, Paste this into the Worksheet_Change section for Sheet 1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B5") Then
Select Case True
Case Range("H28").Value = 1
Range("H11").Value = Range("B6").Value
Case Range("H28").Value = 2
Range("H12").Value = Range("B6").Value
Case Range("H28").Value = 3
Range("H13").Value = Range("B6").Value
End Select
End If
End Sub

I think this will do what you want.

Jon

 

[Jon]

Actually, this one will also work with Overheads, Labour and Profit Margin
( the last post only did Profit Margin)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Or Target = Range("B3") Or Target = Range("B5")
Then
Select Case True
Case Range("H28").Value = 1
Range("H11").Value = Range("B6").Value
Case Range("H28").Value = 2
Range("H12").Value = Range("B6").Value
Case Range("H28").Value = 3
Range("H13").Value = Range("B6").Value
End Select
End If
End Sub

Jon

 

[Jon]

In my news reader, The word "Then" wordwrapped to a new line. It should be
after "Or Target = Range("B5")" (in the same line)

 

[Jon]

This exposes the problem with your layout. Because there is only one column
for getting your info into H11:H13, When you change anything except the wood
type, only that wood type changes. This is true because B1 can only display
one type of wood cost at a time. What you really need is 3 columns with
entries in each column for Materials, Overheads, Labour, Profit margin,.so
you can change them independantly. As such, you don't really need any Wood
type buttons at all. Just formulas. You could fool around with cell
validaton for a series of values for Overheads, labour, and Profit Margin.
This would appear as Drop down boxes in the appropriate cells, and would
allow instant calculation, with a great flexibility.

Jon

 

[sneakyzeal]

I tried that first lot of code (profit margin is the only changeable
variable, labour etc are created by formulas in the real version). I
assumed I was keeping the macros that were attached to each button or
are they superfluous?

If Beech is selected and profit margin is changed to say, 80, the value
in H11 changes (which it didn't do before), but the other two (H12 and
13) don't change until you click the buttons again, and then 80 is
applied. Now that I'm typing this I guess I need the ability to
remember one profit margin for each option but that seems like way too
much thinking than this assignment is worth. I always find that when
you fix one issue, another pops up out of nowhere!

It's still come out much better than it was before, I really appreciate
your help on this Jon

 

[sneakyzeal]

I know what you mean. My problem is that this project is for a paper
where the lecturer expects us to know a lot of excel without actually
teaching us any, so I'm as good as an amateur. I just tried to work
with what I knew and I didn't plan on getting into any VB at all. So
that can explain (though probably not excuse) the bad design!