Stop macro if cell is blank

[lhtqasonline]

Hi,

I have a Active X Command button that calls three small macros
There is a Master Template that gets filled in with details.
When the button is pressed it copies the Master and pastes to new worksheet,
then it renames the new worksheet with values in two cells but if either of the cells are empty I need all the macros to stop without creating the new worksheet

Here is what i have so far

Sub copySheetSameWorkbook()
Dim sheetToCopy As Worksheet
Dim ws As Worksheet
Set sheetToCopy = Worksheets("Master")
sheetToCopy.Copy After:=sheetToCopy
End Sub
    
Sub sheetRename()
If Cells(1, 9) = "" Then
MsgBox "Please enter Quote Number"
Else
ActiveSheet.Name = [H10 & "-" & I1]
End If
End Sub

Sub deleteShape()
ActiveSheet.Shapes("CommandButton1").Delete
End Sub

The command button is just

Private Sub CommandButton1_Click()
Call copySheetSameWorkbook
Call sheetRename
Call deleteShape
End Sub

[mancubus]

you are not a new member and you are not using the code tags. please care to use them.



remove CommandButton1 from Worksheets("Master"), delete CommandButton1_Click event code from its code module and insert them into another sheet.

assign following code to that button.

Sub vbax_53625_CopyRenameWsOnCondition()

    With Worksheets("Master")
        If .Range("I1") = "" Or .Range("H10") = "" Then
            MsgBox "Please enter Quote Number and 'H10 Value whatever it is'"
            Exit Sub
        End If
    End With
    
    Worksheets("Master").Copy After:=Worksheets("Master")
    With ActiveSheet
        .Name = .Range("H10").Value & "-" & .Range("I1").Value
    End With
    
End Sub

Private Sub CommandButton1_Click()
    vbax_53625_CopyRenameWsOnCondition
End Sub

[snb]

Only for illustration purpose:

Sub M_snb()
    With Sheets("Master")
        If .Range("I1") <> "" And .Range("H10") <> "" Then
           .Copy , Sheets("Master")
           ActiveSheet.Name = [H10] & "-" & [I1]
        End If
    End With
                
    If ActiveSheet.Name = "Master" Then MsgBox "Please enter Quote Number and 'H10 Value whatever it is"
End Sub

[lhtqasonline]

Apologies to mancubus for not using the Code Tags (thanks to the person who added it) I haven't been using VBA and this site for years and simply forgot.

Thanks for the reply mancubus I'm a bit confused with your instruction

remove CommandButton1 from Worksheets("Master"), delete CommandButton1_Click event code from its code module and insert them into another sheet.

assign following code to that button.

I only have one sheet called "Master" that get's completed by the user and then "saved" to a new worksheet when they click the button.
Where does the other sheet fit in or am i missing the plot?

[lhtqasonline]

I'm have to still change it but I'm attaching my original excel file for reference.

[mancubus]

ok. lets keep it simple for easiness...


delete all macros in Module1.

replace CommandButton1_Click event code with below.

Private Sub CommandButton1_Click()
    With Worksheets("Master")
        If .Range("I1") = "" Then
            MsgBox "Please insert Quote Number into cell I1", vbOKOnly, "Quote Number Missing"
            Exit Sub
        End If
        
        If .Range("H10") = "" Then
            MsgBox "Please insert Reg. No into cell H10", vbOKOnly, "Reg. No Missing"
            Exit Sub
        End If
        
        .Copy After:=Worksheets("Master")
    End With
     
    With ActiveSheet
        .Name = .Range("H10").Value & "-" & .Range("I1").Value
        .Shapes("CommandButton1").Delete
    End With
End Sub

[lhtqasonline]

Thank you mancubus, you've taught me a lot with this simple example

[mancubus]

you are welcome.

keep in mind that when you copy a worksheet with event code, the code will be copied too..