help create a quote tool

[timmi18]

hi, im trying to create a quote tool and im having a lot of difficulty, the first thing i need to do is choose a product and have certain prices appear accorfin to the product chosen. Thereafter there are 4 conditions for the prices to appear

Supplier
area
profile
mtc

i have entered all the data in another sheet but have absolutely no idea how to link it or waht formulas to use or even waht macro.

can someone please let me know how to attach my files thanks

 

[timmi18]

anyone have a suggestion?

 

[Hamburgler]

The second post in this thread has some info on how to upload files.

http://www.mrexcel.com/forum/showthread.php?t=569774

 

[timmi18]

This is the link, thanks for the help

http://www.mediafire.com/?3958nw0p633kvig

 

[Hamburgler]

Hi Timmi,

It sounds like you want to populate some of the ComboBoxes according values selected in other comboboxes. The only good way that I am aware of for doing this requires macros.

To do this you wil need to save the workbook as an XLSM first.

Then, if you double click on any combobox (in design mode) Excel will create a subroutine for that particular control - which is run any time that the value of it changes.

Here is an example that prints (to the VBE immediate window) a few properties of the combobox

Sub ComboBox1_Change()
    Debug.Print ComboBox1.Value
    Debug.Print ComboBox1.Name
    Debug.Print ComboBox1.List(0)
    Debug.Print ComboBox1.ListCount
End Sub

You can also get and set properties of the other comboboxes. For example, this code conditionally sets the available values of ComboBox2 according to the selection made in combobox1

Sub ComboBox1_Change()
    ComboBox2.Clear
    If ComboBox1.Value = "A" Then
        ComboBox2.AddItem "A1"
        ComboBox2.AddItem "A2"
    ElseIf ComboBox1.Value = "B" Then
        ComboBox2.AddItem "B1"
        ComboBox2.AddItem "B2"
    End If
End Sub

I think that you have to remove any ListFillRange and/or LinkedCell values before you can use the .AddItem Method.

This is just a simple example. You would probably want to loop through ranges to populate your controls rather than manually adding values.

You can do some brilliant stuff using VBA and Worksheet Form/ActiveX controls, but to get it polished and beautiful takes a fair bit of work. You will need to familiarise yourself with all of the main properties/methods of the Combox Controls for starters.

VBA is generally better than using worksheet formulas for something like this, but formulas may be easier, in which case you may want to look at functions such as VLOOKUP, INDEX, IF, AND, OR, SUMIFS, COUNTIFS etc. Also conditional formatting and validation works well with Excel Apps like this...

Hope this helped...