Combining 2 VBA codes into 1

Question

Is it possible to combine the two enclosed codes?

Both codes work fine on their own with separate delete icons on the same sheet.

Sub Msgbox_BeforeRunning()

 Dim answer As Integer

  answer = MsgBox("Do you want to delete Your Data In Area A?", vbQuestion + vbYesNo, "Message: Clear Information")

If answer = vbNo Then Exit Sub

  If answer = vbYes Then

  Range("B33:B39,E36:E39").Value = ""

End If

  answer = MsgBox("Last Question! Do you want to delete LINES 9-19?", vbQuestion + vbYesNo, "Message: Clear Information")

  If answer = vbNo Then Exit Sub

   If answer = vbYes Then

  Range("B9,B10,B11,B12,B13,B17,B18,C14,C19,E9,E10,E11,E12,E13,E14,E15,H12,H19").Value = ""

End If

End Sub

Sub terranian()

Dim o As Object

For Each o In ActiveSheet.OLEObjects

If InStr(1, o.Name, "CheckBox") > 0 Then

o.Object.Value = False

End If

Next

End Sub

Thank you

Answer 1

Rand2201, Thanks for the idea I gave it try still coming up with the same error message?

Answer 2

Hi Scott5392

Okay, please use this version of that Sub:

Sub NewSubName()

Dim answer As Integer

answer = MsgBox("Do you want to delete Your Data In Area A?", vbQuestion + vbYesNo, "Message: Clear Information")

If answer = vbNo Then Exit Sub

If answer = vbYes Then

Range("B33:B39,E36:E39").Value = ""

End If

answer = MsgBox("Last Question! Do you want to delete LINES 9-19?", vbQuestion + vbYesNo, "Message: Clear Information")

If answer = vbNo Then Exit Sub

If answer = vbYes Then Range("B9,B10,B11,B12,B13,B17,B18,C14,C19,E9,E10,E11,E12,E13,E14,E15,H12,H19").Value = ""

Dim o As Object

For Each o In ActiveSheet.OLEObjects

If InStr(1, o.Name, "CheckBox") > 0 Then

o.Object.Value = False

End If

Next

End Sub

Answer 3

Dave, thanks for the quick reply!

Showing Compile error: "End If without If"

Error is heighted yellow @ "End If" prior to the start of Dim o As Object code

Answer 4

  Range("B9,B10,B11,B12,B13,B17,B18,C14,C19,E9,E10,E11,E12,E13,E14,E15,H12,H19").Value = ""

Just an idea:

Range("B9:B13, B17:B18, C14, C19, E9:E15, H12, H19").ClearContents

Answer 5

Hi Scott5392

I am Dave, I will help you with this.

Yes you can combine both subs into one as shown below (you can rename that Sub to suit your needs)

Sub NewSubName()
Dim answer As Integer
answer = MsgBox("Do you want to delete Your Data In Area A?", vbQuestion + vbYesNo, "Message: Clear Information")
If answer = vbNo Then Exit Sub
If answer = vbYes Then
Range("B33:B39,E36:E39").Value = ""
End If

answer = MsgBox("Last Question! Do you want to delete LINES 9-19?", vbQuestion + vbYesNo, "Message: Clear Information")
If answer = vbNo Then Exit Sub
If answer = vbYes Then Range("B9,B10,B11,B12,B13,B17,B18,C14,C19,E9,E10,E11,E12,E13,E14,E15,H12,H19").Value = ""
End If

Dim o As Object
For Each o In ActiveSheet.OLEObjects
If InStr(1, o.Name, "CheckBox") > 0 Then
o.Object.Value = False
End If
Next
End Sub