code help

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
currently i have this code that runs on workbook startup.... but is there any way that i can disable the code on the file that has been "saved as"??

Private Sub WORKBOOK_OPEN()
Dim i As Long
Application.ScreenUpdating = False
Sheets("flat estimates").Activate
Sheets("flat estimates").ComboBox1.Clear
Sheets("flat estimates").ComboBox1.AddItem "--Choose Estimate Type--"
For i = 1 To Sheets("flat estimates").Scenarios.Count
Sheets("flat estimates").ComboBox1.AddItem Sheets("flat estimates").Scenarios(i).Name
Next
Sheets("flat estimates").ComboBox1.ListIndex = 0
Application.ScreenUpdating = True
Sheets("flat estimates").Range("B10:B21").ClearContents

Application.ScreenUpdating = True
Application.ScreenUpdating = False
Sheets("shingle estimates").Activate
Sheets("shingle estimates").ComboBox2.Clear

Sheets("shingle estimates").ComboBox2.AddItem "-----------------Choose Estimate Type-----------------"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 6/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 7-10/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 10/12 UP INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 6/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 7-10/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 1 LAYER RIP 10/12 UP PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER RIP 6/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER RIP 7-10/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER RIP 10/12 UP INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER 6/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER 7-10/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "SHINGLE 2 LAYER 10/12 UP PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 6/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 7-10/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 10/12 UP INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 6/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 7-10/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION 10/12 UP PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 6/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 7-10/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 10/12 UP INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 6/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 7-10/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 1 10/12 UP PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 6/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 7-10/12 INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 10/12 UP INTO BIN"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 6/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 7-10/12 PACKOUT"
Sheets("shingle estimates").ComboBox2.AddItem "CEDAR CONVERSION + 2 10/12 UP PACKOUT"

For i = 1 To Sheets("flat estimates").Scenarios.Count

Next

Sheets("shingle estimates").ComboBox2.ListIndex = 0
Application.ScreenUpdating = True
Sheets("shingle estimates").Range("B16,B17,b19,b20,b21,b23,b24,b29").ClearContents

Application.ScreenUpdating = True
ARBUTUS.show
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Why don't you just prevent a user from doing a SaveAs? Seems it would be simpler than trying to chase around, check file names, etc. A board search for "SaveAsUI" would probably wield you some good results.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
Hi fire.... its like this..... the estimate sheet that these guys use is the master copy for them..... it should never change with the exception of some custom lists that i allow them to make.... but in the end they have to save each estimate that they do as the address of that job...... but the way the master works is it clears most of the info that they need if they call that estimate back up... follow???
so if it is possible id like the worbook open to not work after the file has been saved as something if that is not possible i guess i will have to live with not clearing cells on open
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I think I know what you mean. You could programmatically delete the Workbook_Open procedure.


In a Standard Module place this code ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> DeleteTheWBMod()
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ThisWBmod <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, LineCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ThisWBmod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
    LineCount = ThisWBmod.CountOfLines
    <SPAN style="color:#00007F">If</SPAN> LineCount > 0 <SPAN style="color:#00007F">Then</SPAN>
        DeleteProcedure ThisWBmod, "Workbook_Open"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> DeleteProcedure(CodeMod <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, ProcDec <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> StartLine <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, NumLines <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    StartLine = CodeMod.ProcStartLine(ProcDec, 0)
    NumLines = CodeMod.ProcCountLines(ProcDec, 0)
    CodeMod.DeleteLines StartLine, NumLines
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



In your ThisWorkbook module place this code ...

<font face=Tahoma New>Option Explicit

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeSave(<SPAN style="color:#00007F">ByVal</SPAN> SaveAsUI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Dim</SPAN> myResp <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult, thisWb <SPAN style="color:#00007F">As</SPAN> Workbook
    <SPAN style="color:#00007F">Dim</SPAN> VBComp As VBComponent
    myResp = MsgBox("Are you sure you want to Save?", vbQuestion + vbYesNo, _
        "Save?")
    <SPAN style="color:#00007F">If</SPAN> myResp = vbNo <SPAN style="color:#00007F">Then</SPAN>
        Cancel = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Call</SPAN> DeleteTheWBMod
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


This will COMPLETELY REMOVE YOUR WORKBOOK_OPEN PROCEDURE! Is this what you're looking for?
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541

ADVERTISEMENT

If the "saved-as" workbook has some kind of predictable difference in filename or location from the "master" copy, you ought to be able to check for this before running your extra code.

Otherwise, the solution provided by firefytr ought to be workable.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
heheh... i think ill make a few copies of my master before i fool around with this.......... fire... i want every saved workbook to erase the workbook open procedure......but i would like the master estimate sheet to keep it.... dunno if thats what your code does but ill fool around with it a bit.... i have a master copy at home that i can try it out on because it is not as complete as the one at work...... and yes the estimates would always be saved to the same folder on a computer estimates05 for this year estimates 06 for next year and so on... the master copy would be on the estimators desktop


WISH ME LUCK!!!!!
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771

ADVERTISEMENT

fire i got this error..... user defined type not defined at this line


Dim VBComp As VBComponent
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Oh, sorry. You must make a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" (the number may vary depending on version), and I'd also reference Scripting Runtime while you're at it. Good to have.

Also, you could put a check in there to see what the workbook name is, if it's the one to check for then skip the procedure. So an If/Then/End If statement.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
sorry fire... that is way beyond me.... I can copy the code and put it in where i am told ... sometimes even make a few small adjustments...... but I have no idea what you just said :(
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Okay, no problem.

• Hit Alt + F11 (goto the VBE)
• Select 'Tools' menu
• Click 'References..'
• Scroll down and select 'Microsoft Visual Basic for Applications Extensibility 5.3
• Click Ok

That should be it! Note that these are workbook specific. So if you select it for this workbook, not all would have the reference. Fyi.
 

Forum statistics

Threads
1,148,056
Messages
5,744,548
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top