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..