Solved how to add a message box to a vba

June 13, 2013 at 13:43:09
Specs: Windows 7
Hi,
This is something I have not done before and need some help.
I have the following VBA which I recorded. It does the job perfectly.
What I would like to do is to make sure that the user confirms YES or NO that the date is correct.
Ideally the message box needs to show the date that has been entered for a YES/No confirmation

Sub datepaste()
'
' datepaste Macro
'
    Range("K6").Select
    Selection.Copy
    Range("D5:E5").Select
    ActiveSheet.Paste
 nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
  Sheets("SCARD").Range("K6").Copy _
    Destination:=Sheets("SCARD").Range("R" & nxtRow)
End Sub


("K6") is the date that is entered.

How do I write my requirement, and how do I apply it to the above VBA

Your help would be much appreciated


See More: how to add a message box to a vba

Report •


#1
June 13, 2013 at 16:13:32
✔ Best Answer
I am not that familiar with VBA but this is how it would work in VB6

If MsgBox("Is date Correct?", vbYesNo Or vbInformation, "Date") = vbYes Then
Selection.Copy
Range("D5:E5").Select
ActiveSheet.Paste
nxtRow = Sheets("SCARD").Range("R" & Rows.Count).End(xlUp).Row + 1
Sheets("SCARD").Range("K6").Copy _
Destination:=Sheets("SCARD").Range("R" & nxtRow)

Else
MsgBox "WrongDate", vbCritical Or vbOKOnly, "Date"
End If

It should work.


Stuart


Report •

#2
June 14, 2013 at 23:20:07
Hi there, your solution works very well. To take this a step further, I need to also be able to stop the 'Date' being entered if it already exist in the Range "R"

You cant see if the date has been previously entered as there are certain columns that are hidden for aesthetic reasons.

I have had a look in various forums, but cant really get the answer I am looking for

Any ideas


Report •

#3
June 15, 2013 at 03:37:46
Another if/Then block.

If Range("R" & nxtRow) = 0 then
Destination:=Sheets("SCARD").Range("R" & nxtRow)
end if

Stuart


Report •

Related Solutions


Ask Question