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

dversloot1

Board Regular
Joined
Apr 3, 2013
Messages
113
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

Active Member
Joined
Nov 27, 2012
Messages
309
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
That code should run fine how are you calling it?

Do you get any error messages?

Have you tried stepping through it with F8?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
So you were receiving a compile error?
 

Forum statistics

Threads
1,082,335
Messages
5,364,694
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top