Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > excel vba - spreadsheet code "apply to all" problems
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default excel vba - spreadsheet code "apply to all" problems
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


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default excel vba - spreadsheet code "apply to all" problems
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)

"chief " wrote in message
...
I will try to explain this the best I can. I have set up a spreadsheet
which is used to order items. I have set it up so that upon opening
the excel file, the userform "Shingles" will pop up causing someone to
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 am
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


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default excel vba - spreadsheet code "apply to all" problems
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


---
Message posted from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
Does "Too many different cell formats" apply on excel 2007 Bob Ng Kai Sin Excel Discussion (Misc queries) 6 August 29th 07 01:03 AM
When I type "13" the spreadsheet shows "14." Possibilities? Absolutely Stumped Excel Discussion (Misc queries) 7 May 30th 06 10:19 PM
Looking for VB code to test for "RING" , "BUSY" disconnects or other signals BruceJ[_2_] Excel Programming 3 November 20th 03 01:55 AM
"Problems obtaining data" error refreshing Excel pivottable Dan[_19_] Excel Programming 0 August 4th 03 03:17 PM


All times are GMT +1. The time now is 09:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement