Forum Discussion

The_Draco's avatar
The_Draco
Copper Contributor
Aug 18, 2022

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 "f...
excel
Macros and VBA
The_Draco's avatar
The_Draco
Copper Contributor
Aug 18, 2022
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...
  • The_Draco's avatar
    The_Draco
    Copper Contributor
    Aug 19, 2022

    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?

Resources