vbYesNo msgbox, Yes to run macro, No to Unload UserForm and End Sub

[dversloot1]

Good morning Users,

I'm trying to add in a "Are you sure you want to overwrite the data?" msgbox in my code. Users have the option to create a form or update a preexisting form. If they choose to update a preexisting form (Product ID cell on form tab = a match in Product ID column in "Database" tab) I would like to have a msg box as the user if they are sure they would like to overwrite the data in the "database".

Here is my code:

Public Sub UpdateDataTab()
Dim ID As Long
Dim Row As Long
Dim Cnt As Long
Dim SearchTermsStr As String
Dim CompetitorOne As String
Dim CompetitorTwo As String
Dim CompetitorThree As String
Dim CompetitorFour As String
Dim StarTechProdOne As String
Dim StarTechProdTwo As String
Dim StarTechProdThree As String
Dim Comments As String
Dim ProdID As String
Dim ProdDesc As String
Dim Category As String
Dim Question As String


ID = Sheets("InputORAdjustNewProduct").Range("C3").Value
Question = "Data already exists. Are you sure you want to update the data sheet?"


If Not IsError(Application.Match(ID, Sheets("Data").Range("B1:B2000"), 0)) Then
If MsgBox(Question, vbYesNo, "Double Checking...") = vbNo Then
Unload LoadForm
Else


If IsError(Application.Match(ID, Sheets("Data").Range("B1:B2000"), 0)) Then
Row = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row + 1
Else
On Error Resume Next
Row = Application.WorksheetFunction.Match(ID, Sheets("Data").Range("B1:B2000"), 0)
On Error GoTo 0
End If

...the rest of the macro updates the data tab.....
Application.CutCopyMode = False


Sheets("InputORAdjustNewProduct").Select

End IF


Range("B2").Select


Exit Sub

The vba doesn't make it past the first line "Public Sub UpdateDataTab()" <-- Highlighted

Any ideas why it is doing this?

 

[Wookiee]

Where did you save the macro? I suspect that maybe you saved it in a User Form, and I don't think you can store a Public macro unless it's in a module.

 

[Norie]

That code should run fine how are you calling it?

Do you get any error messages?

Have you tried stepping through it with F8?

 

[dversloot1]

Turns out the coded needed some "End If"s and then it worked fine.

 

[Norie]

So you were receiving a compile error?

 

[dversloot1]

Correct.

"Compile error:

Block IF without End If"

All good now.