code help

D

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!!!!!
 
Upvote 0
fire i got this error..... user defined type not defined at this line


Dim VBComp As VBComponent
 
Upvote 0
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.
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
158
Candyman8019
C
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
396
XLfriend777
XLfriend777
A
  • Question Question
Cannot enter text in textbox on form
Replies
3
Views
270
Anthony47
A
I
  • Question Question
Listbox not being populate when code is run
Replies
4
Views
626
ipbr21054
I
E
  • Question Question
Best Solution for returning data - index?
Replies
5
Views
181
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,222,415
Messages
6,165,896
Members
451,993
Latest member
rowebca

Share this page

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
Back
Top