Forum Discussion
force to save as before printing/MSG Box when saving as with multiple outcoms?
Hey, I have a little question.
Is it possible to force a workbook to save as before it is printing?
And is it also possible to show a MSG Box when saving as that asks if the workbook is saved as "finished" or "work in progress" version?
Also is it possible to hide one specivic worksheet before workbook is sent in E-Mail and is it also possible to delete that worksheet when the workbook is saved as "finished" version?
All in VBA would be nice
2 Replies
- I already have a code that maybe can do some part of this:
Sub speichern_unter()
Dim lw_pfad As String
lw_pfad = ActiveSheet.Range("DC12").Value
lw_pfad = InputBox("Geben Sie hier das Laufwerk und den Pfad an, wo die Datei gespeichert werden soll." & Chr(13) & Chr(13) & "(Ihre Eingabe wird als neuer Default-Wert gespeichert.)", "Datei speichern unter...", lw_pfad)
If lw_pfad = "" Then
MsgBox "Die Datei wird nicht gespeichert, da Sie [Abbrechen] gedrückt oder nichts eingegeben haben.", , "Abbruch"
Exit Sub
Else
If Right(lw_pfad, 1) <> "\" Then lw_pfad = lw_pfad & "\"
ActiveSheet.Range("DC12").Value = lw_pfad
Rem MsgBox lw_pfad
ActiveWorkbook.SaveAs lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & "Blatt" & ActiveSheet.Range("GE12").Value & ".xls"
MsgBox "Die Datei wurde unter " & lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & "Blatt" & ActiveSheet.Range("GE12").Value & ".xls gespeichert.", , "OK"
End If
End Sub
I want this also to be triggered before any kind of saving, not just when "saving as" as I do not want the original file to be overwritten.
So hopefully you can help me to complete the code...well ok, I want to change this and use a UserForm to combine these functions, so atm it looks like this:
Private Sub cancel_Click()
End Sub
Private Sub finished_Click()
Dim lw_pfad As String
lw_pfad = ActiveSheet.Range("DC12").Value
If lw_pfad = "" Then
MsgBox "Die Datei wird nicht gespeichert, da Sie [Abbrechen] gedrückt oder nichts eingegeben haben.", , "Abbruch"
Exit Sub
Else
If Right(lw_pfad, 1) <> "\" Then lw_pfad = lw_pfad & "\"
ActiveSheet.Range("DC12").Value = lw_pfad
Rem MsgBox lw_pfad
ActiveWorkbook.SaveAs lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & ".xlsx"
MsgBox "Die Datei wurde unter " & lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & ".xlsx gespeichert.", , "OK"
End If
End Sub
Private Sub name_Change()
End Sub
Private Sub path_Change()
End Sub
Private Sub work_in_progress_Click()
Dim lw_pfad As String
lw_pfad = ActiveSheet.Range("DC12").Value
If lw_pfad = "" Then
MsgBox "Die Datei wird nicht gespeichert, da Sie [Abbrechen] gedrückt oder nichts eingegeben haben.", , "Abbruch"
Exit Sub
Else
If Right(lw_pfad, 1) <> "\" Then lw_pfad = lw_pfad & "\"
ActiveSheet.Range("DC12").Value = lw_pfad
Rem MsgBox lw_pfad
ActiveWorkbook.SaveAs lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & ".xlsm"
MsgBox "Die Datei wurde unter " & lw_pfad & "Schaltprogramm " & ActiveSheet.Range("C12").Value & ActiveSheet.Range("I12").Value & ActiveSheet.Range("O12").Value & ActiveSheet.Range("U12").Value & ActiveSheet.Range("AA12").Value & ActiveSheet.Range("AG12").Value & ActiveSheet.Range("AM12").Value & ActiveSheet.Range("AS12").Value & ActiveSheet.Range("AY12").Value & ActiveSheet.Range("BE12").Value & ".xlsm gespeichert.", , "OK"
End If
End Sub
Question now is, how do I show the name in the first textbox (name_Change) and use what maybe change there as new name of the workbook when saving as?
Question two is, how do I do same with the second textbox (path_Change), use its outcome as path the file gets saved to and change the path written in cell DC12 on all sheets?
Question three, what do I have to write down in cancle_Click to close the UserForm and abort whatever had triggered it (printing, saving and so on)?
Well and the last Question is, how do I trigger the UserForm by printing, saving, saving as and so on?
