Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Before Save event problems

  Asked By: Carlton    Date: Aug 28    Category: MS Office    Views: 1431
  

I'm trying to create code that will check the filename and path of a
workbook, and if it's a certain thing, force the user to use the "save as"
function.

I'm running into some issues here though. At first I had:

*****************************************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
Dim name As String

Application.EnableEvents = False

name = ActiveWorkbook.FullName

If name = "C:\MRS\excel\rolltop50.xls" Then
Application.Dialogs(xlDialogSaveAs).Show
cancel = True
End If


Application.EnableEvents = True

End Sub
*****************

But that wouldn't show the "save as" dialog box at all. I could see that it
performed that step, but the dialog box never showed up.

Then I changed it to:

***************************************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
Dim name As String

Application.EnableEvents = False

name = ActiveWorkbook.FullName

If saveui = True Then Exit Sub

If name = "C:\MRS\excel\rolltop50.xls" Then
MsgBox "You must save this file using Save As.", vbOKOnly, "Can not save
this way"
cancel = True
End If


Application.EnableEvents = True

End Sub
*************************************

But that caused the message box to appear even if the "save as" option was
chosen. Even though I set an out using the SaveAsUI variable (according to
help that meant that the user used the save as option).

Does anyone have any ideas?

Share: 

 

10 Answers Found

 
Answer #1    Answered By: Elizabeth Hughes     Answered On: Aug 28

Have you tried using the GetSaveAsFilename Method? I think this would be
a more straightforward way to go.

 
Answer #2    Answered By: Arlene Harvey     Answered On: Aug 28

I hadn't thought of that one. That works fine in a module, but when I put
it in the Before save  event it prompts to save it twice even though I turn
off alerts before I save.

Any ideas  on that one?

 
Answer #3    Answered By: Caroline Bowman     Answered On: Aug 28

Never seen that. Why not put it in a module then call the sub from
the event? Would that work?

 
Answer #4    Answered By: Karla Ortiz     Answered On: Aug 28

Tried that. Still does the same thing.

 
Answer #5    Answered By: Anu K     Answered On: Aug 28

Want to post the whole code  you're using including this new
method?

 
Answer #6    Answered By: Fjodor Bonkob     Answered On: Aug 28

Sure....this is way one:

************************
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim weekending As Date, weektext As String
Dim filename  As String


weekending = Sheets("Corporate").Range("g5").Value2
weektext = Replace(weekending, "/", "")

filename = Application.GetSaveAsFilename("C:\Reports\Marketing\Top 50\top 50
w-class 1 " & weektext & ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename, xlNormal

Application.DisplayAlerts = True

End Sub

This is way two - using the event  to call a sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub


and this is the sub in a module:


Private Sub saveit()
Dim weekending As Date, weektext As String
Dim filename As String


weekending = Sheets("Corporate").Range("g5").Value2
weektext = Replace(weekending, "/", "")

filename = Application.GetSaveAsFilename("C:\Reports\Marketing\Top 50\top 50
w-class 1 " & weektext & ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename, xlNormal

Application.DisplayAlerts = True

End Sub

Any ideas? I'm pulling my hair out on this one.

 
Answer #7    Answered By: Faizah Khan     Answered On: Aug 28

Just a thought, but this event  triggers before the save, and doesn't
stop the save  which triggered it in the first place. Don't you need a
'Cancel' in there to stop the requested Save?

 
Answer #8    Answered By: Kawkab Mansour     Answered On: Aug 28


I just tried adding a cancel = true  line to each of the different ways I've
tried this (with and without calling a sub), and both of them still made me
save twice.

*sigh* At this point, I'm thinking I'll just add a line to the documentation
that says to click save  both times the "save as" box  pops up.

 
Answer #9    Answered By: Christina Ramirez     Answered On: Aug 28

Not followed this thread really so fprgive me if it's off base but...
If it's a timing thing  a well placed DoEvents may solve it.

 
Answer #10    Answered By: Charlie Smith     Answered On: Aug 28

but I can't see that a Doevents wouldn't help here.
The problem is I have code  in an event  (beforesave), but it's prompting the
user with the saveas dialog  twice. If you have specifics on how the
DoEvents might work, I'd be happy to hear them.

While at this point I've basically given up trying to solve it before
turning over the project, I'm still open to ideas  anyone has on what's
causing that.

 
Didn't find what you were looking for? Find more on Before Save event problems Or get search suggestion and latest updates.




Tagged: