excel vba - spreadsheet code "apply to all" problems

C

chief

I will try to explain this the best I can. I have set up a spreadshee
which is used to order items. I have set it up so that upon openin
the excel file, the userform "Shingles" will pop up causing someone t
pick an item from one of two comboboxes. The pop-up is set up as:

Private Sub Workbook_Open()
Shingles.Show

End Sub

Then I have set up some codes in order to fulfill the rest of what I a
doing:

Private Sub ComboBox1_Change()
ComboBox1.DropDown
Range("D10, D60, D110").Value = ComboBox1.Text
If Range("D10").Value = "Autumn Brown" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Charcoal" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Coffee Brown" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Cocoa Brown" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Forest Green" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Green"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Golden Cedar" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Tan"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Nickel Grey" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Satin Black" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Silver Lining" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Walnut Brown" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3
ElseIf Range("D10").Value = "Weathered Grey" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Renaissance"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 75 * 3

End If

End Sub

Private Sub ComboBox2_Change()
ComboBox2.DropDown
Range("D10, D60, D110").Value = ComboBox2.Text
If Range("D10").Value = "Charcoal Blend" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Timberline"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 87.67 * 3
ElseIf Range("D10").Value = "Heather Blend" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Black"
Range("G111").Value = "Timberline"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 87.67 * 3
ElseIf Range("D10").Value = "Mission Brown Blend" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Timberline"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 87.67 * 3
ElseIf Range("D10").Value = "Pewter Grey Blend" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Tan"
Range("G111").Value = "Timberline"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 87.67 * 3
ElseIf Range("D10").Value = "Weatherwood Blend" Then
Range("D11, D12, D15, D61, D62, D65, D113").Value = "Brown"
Range("G111").Value = "Timberline"
Range("G119").Value = "lbs."
Range("F119").Value = Range("A10").Value * 87.67 * 3

End If

End Sub

Private Sub UserForm_Initialize()
ComboBox1.AddItem "Autumn Brown"
ComboBox1.AddItem "Charcoal"
ComboBox1.AddItem "Coffee Brown"
ComboBox1.AddItem "Cocoa Brown"
ComboBox1.AddItem "Forest Green"
ComboBox1.AddItem "Golden Cedar"
ComboBox1.AddItem "Nickel Grey"
ComboBox1.AddItem "Satin Black"
ComboBox1.AddItem "Silver Lining"
ComboBox1.AddItem "Walnut Brown"
ComboBox1.AddItem "Weathered Grey"
ComboBox2.AddItem "Charcoal Blend"
ComboBox2.AddItem "Heather Blend"
ComboBox2.AddItem "Mission Brown Blend"
ComboBox2.AddItem "Pewter Grey Blend"
ComboBox2.AddItem "Weatherwood Blend"

End Sub


Now my problem is that I have set these codes up in an old excel
template when they should be set up in the new template that is being
used...however, there are about 200 templates which differ slightly and
rather than going into each template and copy/pasting the code, is
there a way that I can take my code and apply to all files So that when
the specific excel file opens it will run my codes? I can try to
explain more if that doesn't make sense.

Sorry for writing a novel but I would very much appreciate some
feedback if possible.

Thanks
 
B

Bob Phillips

Put your code in an add-in, and run from there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

DNF Karran

Maybe look to run this as a addin or activate the macro sheet via a
shortcut on the toolbar.

This way the user calls the mcro through xl not through the individual
templates

Duncan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to big! 3

Top