Code for Command Button - error message when other button not yet clicked

[Nadine1988]

Hello,

i do have a file with two command buttons.
Command Button 1 does check some fields and then save the file.
Command button 2 does send the saved file attached to an email.

I would now need a code that doesn't allow command button 2 to be pressed if the file wasn't saved (so if command button 1 wasn't pressed yet)
So the user has to save the file BEFORE submitting the form.

any ideas on how to set this up correctly? Thanks!
Nadine

 

[Skyybot]

How does this work? This is just two codes - one after an other (this is actually how it's set up now). This will still allow to press button 2 before button 1 which leads to sending the email without saving it. Or do i get something wrong here?

Button1 code will run before Button2 code thereby saving before emailing. This doesn't prevent Button2 from being pressed, it just insures that Save code in Button1 is run first.

 

[Skyybot]

Private Sub CommandButton1_Click()

MsgBox "Button 1 pressed."

End Sub

Private Sub CommandButton2_Click()

CommandButton1_Click      ' This line calls the CommandButton1_Click sub

MsgBox "Button 2 pressed."

End Sub

 

[Nadine1988]

Private Sub CommandButton1_Click()

MsgBox "Button 1 pressed."

End Sub

Private Sub CommandButton2_Click()

CommandButton1_Click      ' This line calls the CommandButton1_Click sub

MsgBox "Button 2 pressed."

End Sub

I can't believe it was that easy - it's actually working - Thank you!

but i still have an other issue - so it's opening an folder to save the file with the correct file name (comes from cell A18) but when clicking on save it's saving under the current file name. this is very strange.

this is the code to save - anything wrong here?

 Name = Application.GetSaveAsFilename("C:\Testpfad\" & Range("A18") & ".pdf", fileFilter:="Microsoft Excel-Arbeitsmappe (*.xlsm), *.xlsm")
    If Name <> False Then
        ActiveWorkbook.SaveAs Name, FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If

 

[Nadine1988]

I've got to go out now but Iwill look at this later.

Meanwhile can you tell me what is happening when you run it?

Thank you - as you can see we've figured out an easy way to solve this issue

but i still have an other issue - so it's opening an folder to save the file with the correct file name (comes from cell A18) but when clicking on save it's saving under the current file name. this is very strange.

this is the code to save - anything wrong here?

 Name = Application.GetSaveAsFilename("C:\Testpfad\" & Range("A18") & ".pdf", fileFilter:="Microsoft Excel-Arbeitsmappe (*.xlsm), *.xlsm")
    If Name <> False Then
        ActiveWorkbook.SaveAs Name, FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If

 

[Skyybot]

I can't believe it was that easy - it's actually working - Thank you!

but i still have an other issue - so it's opening an folder to save the file with the correct file name (comes from cell A18) but when clicking on save it's saving under the current file name. this is very strange.

this is the code to save - anything wrong here?

 Name = Application.GetSaveAsFilename("C:\Testpfad\" & Range("A18") & ".pdf", fileFilter:="Microsoft Excel-Arbeitsmappe (*.xlsm), *.xlsm")
    If Name <> False Then
        ActiveWorkbook.SaveAs Name, FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If

fPath = "C:\Testpfad\"
fName = ActiveSheet.Range("A18").Value
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        fPath & fName & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

 

[Nadine1988]

hmm... it's not saving at all with this code. I will try some other options.

 

[Herakles]

hmm... it's not saving at all with this code. I will try some other options.

hmm... it's not saving at all with this code. I will try some other options.

What is in cell A18?

Should it be taking the filename from cell A15?

 

[Nadine1988]

'command Button 1 - speichern
Private Sub CommandButton2_Click()

Dim xOutlookObj As Object
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim rng As Range
    Dim strmsg As String
    Dim arrymsg()
    Dim i As Long
    
    Dim strDesktopPath As String
    
           
    On Error Resume Next
    
    If IsEmpty(Range("A7")) Then
    MsgBox "Enter date"
    GoTo ends
    
    Else
    If IsEmpty(Range("D7")) Then
    MsgBox "Enter Vizrt sales peson"
    GoTo ends
        
    Else
    If IsEmpty(Range("C9")) Then
    MsgBox "Enter the start date of your rental"
    GoTo ends
  
    Else
    If IsEmpty(Range("C11")) Then
    MsgBox "Enter the end date of your rental"
    GoTo ends
    
    Else
    If IsEmpty(Range("A18")) Then
    MsgBox "Please enter a subject line / quote number in cell A18!"
    GoTo ends
    
      
End If
End If
End If
End If
End If

fPath = "C:\Testpfad\"
fName = ActiveSheet.Range("A18").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        
ends:
    
    End Sub
   
  

'Command Button

Private Sub CommandButton1_Click()
Dim xOutlookObj As Object
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim rng As Range
    Dim strmsg As String
    Dim arrymsg()
    Dim i As Long
    
    Dim strDesktopPath As String
           
    On Error Resume Next
    
    If IsEmpty(Range("A7")) Then
    MsgBox "Enter date"
    GoTo ends
    
    Else
    If IsEmpty(Range("D7")) Then
    MsgBox "Enter Vizrt sales peson"
    GoTo ends
        
    Else
    If IsEmpty(Range("C9")) Then
    MsgBox "Enter the START DATE of your rental"
    GoTo ends
  
    Else
    If IsEmpty(Range("C11")) Then
    MsgBox "Enter the END DATE of your rental"
    GoTo ends
    
    Else
    If IsEmpty(Range("A18")) Then
    MsgBox "Please enter a subject line / quote number in cell A18!"
    GoTo ends
    
      
End If
End If
End If
End If
End If

CommandButton2_Click
   
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.createitem(0)
    xMailBody = "Please add any special requirements hereFor FOC rentals - please attach Vanessa's approval to your email." & vbNewLine & vbNewLine & _
              "" & vbNewLine & _
              ""
                  On Error Resume Next
    With xOutMail
        .To = "logisticaustria@vizrt.com"
        .CC = ""
        .BCC = ""
        .Subject = "Demopool Request_"
        .Body = xMailBody
        .Attachments.Add ActiveWorkbook.FullName
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    
ends:
      
      
End Sub

 

[Nadine1988]

What is in cell A18?

Should it be taking the filename from cell A15?

The filename is supposed to come for cell A18 - but with your code it's not saving at all. and with my code it's saving with the wrong name.

 

[Herakles]

The filename is supposed to come for cell A18 - but with your code it's not saving at all. and with my code it's saving with the wrong name.

So what value is your code using for the file name and where does this value come from?

Cell A15 was quoted in a previous post as where the file name comes from.