Add Message box to VBA code

[posfog]

Hi All,
I have some coding that allows me to copy data from one sheet to another, but i want the same coding to look at Cell M4 and display a Message box if Cell M4 contains the wording "Select Variant"


Is this possible and where would it need to be within the coding (shown below sorry for long coding)


Sub ADDRECORD()







Dim src As Worksheet
Dim dst As Worksheet
Dim rw As Long

Application.ScreenUpdating = False

' Set source and destination sheets
Set src = Sheets("TOUCH SCREEN")
Set dst = Sheets("RAW DATA")

' Find next available row on destination sheet
rw = dst.Cells(Rows.count, "A").End(xlUp).Row + 1

' Populate values on destination sheet
dst.Cells(rw, "A") = src.Range("A101")
dst.Cells(rw, "B") = src.Range("B101")

Application.ScreenUpdating = True

End Sub


Regards

 

[Paul Ked]

Please use code tags when posting code, it makes it much easier to read.

Try this:

Sub ADDRECORD()
    Dim src As Worksheet
    Dim dst As Worksheet
    Dim rw As Long
    Application.ScreenUpdating = False
' Set source and destination sheets
    Set src = Sheets("TOUCH SCREEN")
    Set dst = Sheets("RAW DATA")
' Find next available row on destination sheet
    rw = dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
' Check M4
    If src.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & src & "contains 'Select Variant'", vbInformation, "Select Variant found..."
    ElseIf dst.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & dst & "contains 'Select Variant'", vbInformation, "Select Variant found..."
    End If
' Populate values on destination sheet
    dst.Cells(rw, "A") = src.Range("A101")
    dst.Cells(rw, "B") = src.Range("B101")
    Application.ScreenUpdating = True
End Sub

 

[posfog]

Thanks Paul Ked
Sorry about not using code tags.
I just used your coding but getting an Object doesn't support this property or method message??

Regards

Please use code tags when posting code, it makes it much easier to read.

Try this:

Sub ADDRECORD()
    Dim src As Worksheet
    Dim dst As Worksheet
    Dim rw As Long
    Application.ScreenUpdating = False
' Set source and destination sheets
    Set src = Sheets("TOUCH SCREEN")
    Set dst = Sheets("RAW DATA")
' Find next available row on destination sheet
    rw = dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
' Check M4
    If src.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & src & "contains 'Select Variant'", vbInformation, "Select Variant found..."
    ElseIf dst.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & dst & "contains 'Select Variant'", vbInformation, "Select Variant found..."
    End If
' Populate values on destination sheet
    dst.Cells(rw, "A") = src.Range("A101")
    dst.Cells(rw, "B") = src.Range("B101")
    Application.ScreenUpdating = True
End Sub

 

[Paul Ked]

Sorry, missed out the .Name:

Sub ADDRECORD()
    Dim src As Worksheet
    Dim dst As Worksheet
    Dim rw As Long
    Application.ScreenUpdating = False
' Set source and destination sheets
    Set src = Sheets("TOUCH SCREEN")
    Set dst = Sheets("RAW DATA")
' Find next available row on destination sheet
    rw = dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
' Check M4
    If src.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & src.Name & " contains 'Select Variant'", vbInformation, "Select Variant found..."
    ElseIf dst.Range("M4") = "Select Variant" Then
        MsgBox "Tab " & dst.Name & "contains 'Select Variant'", vbInformation, "Select Variant found..."
    End If
' Populate values on destination sheet
    dst.Cells(rw, "A") = src.Range("A101")
    dst.Cells(rw, "B") = src.Range("B101")
    Application.ScreenUpdating = True
End Sub

 

[posfog]

Morning Paul Ked,

That is working with the Msgbox coming up as expected but when i press ok to try and amend the issue it looks to carry on with the coding and adds the data to the destination sheet without me being able to amend the issue that the msgbox had highlighted?

Regards

 

[My Aswer Is This]

I have been following this thread. Your original post said:

look at Cell M4 and display a Message box if Cell M4 contains the wording "Select Variant"​

There was nothing said about stopping the script so you could amend the issue.
So are you saying stop the script. And then you will have to restart the script when your ready.

It's not possible to pause a script while you make changes and then restart when you do something.

 

[posfog]

Hi There,
I must apologise as i didn't mention it in the start of the thread, but i did think it would stop the script if it found a error.

As "My Aswer is This" I would like the script to check that M4 and if it shows the working "Select Variant" i would like the Msgebox to show the error with an ok button to be pressed and nothing else to happen (stop script)
Then once the used has amended their error (M4 no longer contains wording"Select Variant") they select the script to run again and for it now to copy the data to the destination sheet

Regards

I have been following this thread. Your original post said:

look at Cell M4 and display a Message box if Cell M4 contains the wording "Select Variant"​

There was nothing said about stopping the script so you could amend the issue.
So are you saying stop the script. And then you will have to restart the script when your ready.

It's not possible to pause a script while you make changes and then restart when you do something.

 

[My Aswer Is This]

Now we could have the script popup a Inputbox.
You could enter some value like "Bill" into the InPutBox and now the script could enter "Bill" into Range("M14")

 

[posfog]

Sorry you lost me there?

 

[My Aswer Is This]

Would this work?
If "M14" .value="

Select Variant"
Then script Will display a "InputBox"

You enter "Bill" into InputBox and then click OK. Now the script will put "Bill" into Range("M14")

If "M14") does not equal "Select Variant" the script just runs with no Popup.

Or tell me if Range("M14") equals "Select Variant" what will the user need to do?
Maybe the script could change what needs to be changed.
​