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

 

[Herakles]

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

How about disabling Button 1 after running the Button 1 code and only running the Button 2 code if Button 1 is disabled.

Put this in the worksheet code module.

Private Sub CommandButton1_Click()
    
    MsgBox "Run some code here Button 1."
    
    Me.CommandButton1.Enabled = False
    
    Me.CommandButton2.Enabled = True

End Sub

Private Sub CommandButton2_Click()

    If Me.CommandButton1.Enabled = False Then
    
        MsgBox "Run some code here Button 2."
        
        Me.CommandButton1.Enabled = True

    End If
    
    Me.CommandButton2.Enabled = False

End Sub

Private Sub Worksheet_Activate()

    Me.CommandButton1.Enabled = True
    
    Me.CommandButton2.Enabled = False
    
End Sub

 

[Nadine1988]

Hmm not a bad idea but I think it could cause some problems to disable the save button after the first time clicking on it because they might want to make same changes after saving and then they would need to save again. You know what i mean?

Thanks for your reply anyways!

 

[Herakles]

Hmm not a bad idea but I think it could cause some problems to disable the save button after the first time clicking on it because they might want to make same changes after saving ...

The worksheet activate event enables Button 1 and disables Button 2.

You could have another button to do the reset.

 

[Nadine1988]

Okay... but this would be a lot of button clicking and our sales team is ... well, kinda lazy and doesn't understand the easiest things soo if there are any other suggestions making it easier and with less steps it would be highly appreciated! Thanks!

 

[Herakles]

Okay... but this would be a lot of button clicking and our sales team is ... well, kinda lazy and doesn't understand the easiest things soo if there are any other suggestions making it easier and with less steps it would be highly appreciated! Thanks!

Blimey. A lazy sales team need to be fired.

This code will only execute button 2 if button 1 has been pressed.

They can press button 2 more than once but have a choice as to whether they send the email.

Option Explicit

Dim blnChanged As Boolean
Dim blnSentToEmail As Boolean

Private Sub CommandButton1_Click()
        
    MsgBox "Check some fields and then save the file."
    
    ActiveWorkbook.Save

    blnChanged = False
    
    blnSentToEmail = False

End Sub

Private Sub CommandButton2_Click()
Dim strMsg As String

    If blnSentToEmail Then
    
        strMsg = "This workbook has already been attached to an email since it was last saved." & vbCrLf & _
            "Do you want to send the email again?"
        
        If MsgBox(strMsg, vbYesNo, "Warning!") = vbNo Then
        
            Exit Sub
        
        End If
    
    End If

    If ActiveWorkbook.Saved And blnChanged = False Then
        
        MsgBox "Attach file to an email code here."
        
        blnSentToEmail = True
        
    End If

End Sub

Private Sub Worksheet_Activate()
    blnSentToEmail = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    blnChanged = True
End Sub

 

[Nadine1988]

Thank you.

I tried several options now and I'm just not sure where to put your code in my existing code.

'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("A15")) Then
    MsgBox "Please enter a subject line / quote number in cell A15!"
    GoTo ends
    
      
End If
End If
End If
End If
End If

   Dim Name
    Name = Application.GetSaveAsFilename("C:\Testpfad\" & Range("A15") & ".xlsm", fileFilter:="Microsoft Excel-Arbeitsmappe (*.xlsm), *.xlsm")
    If Name <> False Then
        ActiveWorkbook.SaveAs Name, FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If
    
        
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("A15")) Then
    MsgBox "Please enter a subject line / quote number in cell A15!"
    GoTo ends
    
      
End If
End If
End If
End If
End If

   
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.createitem(0)
        Doc.SaveAs2 Filename:=Environ("temp") & "\" & Environ("username"), FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    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 Form_"
        .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

 

[Herakles]

Thank you.

I tried several options now and I'm just not sure where to put your code in my existing code.

'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("A15")) Then
    MsgBox "Please enter a subject line / quote number in cell A15!"
    GoTo ends
   
     
End If
End If
End If
End If
End If

   Dim Name
    Name = Application.GetSaveAsFilename("C:\Testpfad\" & Range("A15") & ".xlsm", fileFilter:="Microsoft Excel-Arbeitsmappe (*.xlsm), *.xlsm")
    If Name <> False Then
        ActiveWorkbook.SaveAs Name, FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If
   
       
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("A15")) Then
    MsgBox "Please enter a subject line / quote number in cell A15!"
    GoTo ends
   
     
End If
End If
End If
End If
End If

  
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.createitem(0)
        Doc.SaveAs2 Filename:=Environ("temp") & "\" & Environ("username"), FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    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 Form_"
        .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

You initialy said:

Command Button 1 does check some fields and then save the file.
Command button 2 does send the saved file attached to an email.

Now Command Button 1 sends the email and Command Button 2 saves the file.

Both check the form for omissions.

 

[Nadine1988]

yeah sorry. that's because i created the command button which sends the email first. didn't think about that. but i's working like it is set up now. i only would need to add the function which doesn't allow to press the button which sends the email before it has been saved

 

[Herakles]

yeah sorry. that's because i created the command button which sends the email first. didn't think about that. but i's working like it is set up now. i only would need to add the function which doesn't allow to press the button which sends the email before it has been saved

I assume that it is just a copy of the form that you want to be saved using the filename in A15 and then attaching this to the email.

You are saving the macro enabled workbook with the buttons under the name in A15. Probably not a good idea as the email recipient can run the code.

Can I suggest that you create a new workbook just containing the form without the buttons and the code and save this using the name in A15.
Maybe even save this as a PDF and attaching the PDF to the email.

You can then attach this saved workbook / PDF to the email.

If not then just add this function to your code.
This function checks to see if the file saved exists and that the workbook has been saved.

Private Function fncCanEmailBeSent(strFileName As String) As Boolean

    fncCanEmailBeSent = False
    
    If ActiveWorkbook.Saved And Dir(strFileName) <> "" Then
    
        fncCanEmailBeSent = True
    
    End If

End Function

and call it from this code :

    If Not fncCanEmailBeSent("C:\Testpfad\" & Range("A15") & ".xlsm") Then
        Exit Sub
    End If

    Set xOutApp = CreateObject("Outlook.Application")

which you place just above the line indicated.