Aug 18 2022 10:33 PM
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
Aug 18 2022 10:59 PM
Aug 19 2022 12:31 AM - edited Aug 19 2022 12:31 AM
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?