I Need Help!!!

[mgk086]

I am working on a project and need some help. I am creating a template used for quoting at my company.

I want to create a standard template used for all commodities. To do this I have several columns that need to be on every quote and then depending on the commodity, a few other columns that need to be added in. I want to automate this so that I have a heading "Commodity" at the top and then a drop down menu with the different options (plastic, glass, etc.). Then once the commodity is selected the necessary columns will be inserted into the template. Is this at all possible using macros or anything else?

Thank You!

 

[Smitty]

Welcome to the Board!

Most quote/invoice issues like this can be solved with VLOOKUP to return the selected item's relevant information.

Debra Dalgleish has a good VLOOKUP example here.

Feel free to post back with more specifics if that's not what you're after.

Hope that helps,

 

[BigJ]

Just create your spreadsheet with every conceivable column and hide some depending on the value in your dropdown box. You'll need to use a ListBox change event for this.

This code is for hiding/showing rows, but the same principles will apply

Sub ListBox1_Change()

Rows("96:105").Hidden = False
Rows("133:134").Hidden = False
If Range("E17") = "Advisory board" Then
Rows("98:105").Hidden = True
ElseIf Range("E17") = "Satellite symposium" Then
Rows("96:97").Hidden = True
Rows("133:134").Hidden = True
End If

End Sub

 

[mgk086]

I created a code like the one above and it seems to work a little. I have not used macros too often and I am running across two problems. First, it did not automaticaly hide the columns when I picked the commodity from the drop down menu I had to pick the item and then run th emacro and it worked. Can I have it automatically run teh macro and hide the column?

Also, can I make it reset if I go back to a blank drop down list?

Thank you for your help so far, I feel a lot closer to solving my problem.

 

[BigJ]

How have you created your dropdown list?

 

[mgk086]

I have created the lists and gotten almost everything to work now. The only thing I would like to happen now is have the macro automatically run when I choose something new in the drop down list. Currently I have to choose something from my dropdoen list and than remember to run the macro. Since many users will use this template I would like it to update as soon as something is selected on the list.

Thanks

 

[lenze]

I have created the lists and gotten almost everything to work now. The only thing I would like to happen now is have the macro automatically run when I choose something new in the drop down list. Currently I have to choose something from my dropdoen list and than remember to run the macro. Since many users will use this template I would like it to update as soon as something is selected on the list.

Thanks

Assuming your dropdowns are regular Data Validation type, move your code to the Worksheet module and use the WorkSheet_Change event!!!

lenze

 

[BigJ]

I have created the lists and gotten almost everything to work now. The only thing I would like to happen now is have the macro automatically run when I choose something new in the drop down list. Currently I have to choose something from my dropdoen list and than remember to run the macro. Since many users will use this template I would like it to update as soon as something is selected on the list.

Thanks

If you're using a listbox, then make the macro a listbox change event, like the original code.

 

[mgk086]

The following is my code and I am still gettting an error saying "Compile Errir expected end sub" I am sure I am missing something do you know what it is?

Private Sub Worksheet_Change(ByVal Target As Range)
Sub dropdown()
If Range("F9") = "Aluminum" Then
Columns("P:Y").Hidden = True
Columns("K:O").Hidden = False
ElseIf Range("F9") = "Plastic" Then
Columns("K:O").Hidden = True
Columns("T:Y").Hidden = True
Columns("P:S").Hidden = False
ElseIf Range("F9") = "Other" Then
Columns("K:S").Hidden = True
Columns("W:Y").Hidden = True
Columns("T:V").Hidden = False
End If
End Sub

 

[Smitty]

Remove this line:

Sub dropdown()

You have two Subs but only one end Sub, regardless, you can't nest subs like that.

See if this does what you want:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("F9")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Text<br>                <SPAN style="color:#00007F">Case</SPAN> "Aluminum"<br>                    Columns("P:Y").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                    Columns("K:O").Hidden = <SPAN style="color:#00007F">False</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> "Plastic"<br>                    Columns("K:O").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                    Columns("T:Y").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                    Columns("P:S").Hidden = <SPAN style="color:#00007F">False</SPAN><br>                <SPAN style="color:#00007F">Case</SPAN> "Other"<br>                    Columns("K:S").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                    Columns("W:Y").Hidden = <SPAN style="color:#00007F">True</SPAN><br>                    Columns("T:V").Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It sets the target range so the code's not always running and to me a Select Case structure is easier to read.