code is missing something

[d0wnt0wn]

I have been tinkering around with this save as code and I have it almost the way i want it.....

it saves where and what i want...
it gives me a message when there is a duplicate file in the folder.....
but one thing it does that I do not like is when it tries to close after a duplicate file is found it brings up the do you want to save workbook2 dialog..... I would like to do one of two things with it.... a) can i stop that dialog box from appearing and the workbook just closes without saving... or can i have the option to save it as something else

Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
MsgBox "There is a file with that name already, File Not Saved!"
ActiveWorkbook.Close
Exit Sub

End Sub

 

[tmischler]

application.displayalerts = false will stop the dialogue coming up. Don't forget to turn them back on afterwards

 

[d0wnt0wn]

i played with that a bit but what happened was the duplicate file got overwritten without any kind of notice... this is not what i want to happen.... i want either the option for a new save as something different or for the workbook to just close without saving and have a little popup saying that it couldnt be saved because of duplicate.. this will make the user change the name of the target cell slightly

 

[d0wnt0wn]

still looking for some help with this if anyone cares to give me some input id appreciate it

 

[Ivan F Moala]

Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
MsgBox "There is a file with that name already, File Not Saved!"
ActiveWorkbook.Close False

End Sub

 

[d0wnt0wn]

so all that was added was the false at the end of the close statement?... what does that do???

 

[Tazguy37]

ActiveWorkbook.Close False

will just close the active workbook without prompting you to save changes.

 

[d0wnt0wn]

so if i want to rename a file based one cell i can do something like this??


Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #2" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

End Sub



I found that this seems to work ok.... but what if i make another estimate can i do this????


Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
On Error GoTo userD
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #2" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

userD:
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #3" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

End Sub


is that proper use of VBA??? or is there a much tidier way to go about this?

 

[Tazguy37]

What I would do is keep a count of how many times the workbook has been saved (probably in an out of the way place in the workbook, hidden if necessary). Then, in a BeforeSave() event, strip the number off the current filename (might want to keep it the same number of digits all the time, 2 or 3), add one and resave it.

I'm sure there's more ways to go about this than you or i can think of.