Conflicting VB Code

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

I have one private sub that prevents the Master file from being saved
over. It is as follows...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If SaveAsUI Then Exit Sub
If ThisWorkbook.Name <> "061 Master.xls" Then Exit Sub
MsgBox "Press 'Ctrl s' to save."
Cancel = True

End Sub


Then I have a macro that uses "ctrl s" as a shortcut. This macro
automatically saves the file based on the contents of a cell. The
problem is that "ctrl s" never works. I always get the msg saying to
Press ctrl s to save. This is the code for saving

Sub dateSave()
ChDrive "C:\"
ChDir "C:\Documents and Settings\tsmith\desktop\reports"
ActiveWorkbook.SaveAs Filename:=Range("B60").Value
End Sub

I checked cell B60 and it does not contain the label "061 Master".



What's going on??!
 
Hi
try
Sub dateSave()
ChDrive "C:\"
ChDir "C:\Documents and Settings\tsmith\desktop\reports"
application.enableevents=false
ActiveWorkbook.SaveAs Filename:=Range("B60").Value
application.enableevents=true
End Sub
 
what do you mean by breakpoint....place this in the auto-save macro o
the macro that prevents saving?

Breakpoint meaning...pick a spot and End the sub prematurely
 
Hi
o.k. maybe a little bit difficult to explain but I'll give it a try
- open the VBA editor
- locate your code and goto the first line of your save macro
- hit F9 (this inserts a breakpoint)
- now in Excel start the macro manually. The VBA editor should be
opened with the cursor placed in the line of your breakpoint.
- Now step line by line through your macro (hit F8 for this).
See what happens with your macro, check the values, etc.
 
Frank...


...I feel like a fool


Left this line out: application.enableevents=false

It does now. Sorry 'bout that.


Can you explain to a hopeless sole, -why- that worked?



Thanks, Frank
 
Hi
without this line you alsways go to the BeforeSave event (even if you
start your own macro) and within this event procedure you cancel the
saving
 
Back
Top