Help with For/If/Msgbox

[W R]

Hi all,

I have used answers on this site to help my Excel sheets for about 10 years now but finally need to post my specific problem as I cannot seem to tweak the things I see to work for me.

What I have is an workbased activity tracker eg. Calls made, calls answered, emails etc.

At the end of the day I have a macro that I click and this data is pasted in to a table on another sheet, I also have a failsafe to run the macro when I close excel in the event that I haven't copied the data over.

Currently the code is as follows (which I think I got from here);

Sub CopyData()
'
' CopyData Macro
'

'
    Sheets("Sheet1").Select
    Range("C21:L21").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

This works great to look for the first empty cell at the bottom of the date column and insert the data.

Here is what I would like it to do:

1. IF there is already an entry for [date in cell F2 in sheet1] in Column1 (in sheet2) THEN pop up msgbox to ask if you would like to Overwrite the Data, [YES] - overwrites row with same date then exit the sub, [No] - writes data in new row, [Cancel] - Do nothing and Exit Sub.
2. ELSE Continue to have the macro insert the data in the first blank row.

Ideally the buttons on the msgbox would be "Overwrite Data" - "New entry" - "Cancel" but I think that would need a lot more code which would make it more complicated for my little brain to understand!

Thanks all.

 

[dmt32]

If want an input box replace suggested line of code with following & see if helps

Dim getdate     As Variant
    If DateValue(checkdate) <> Date Then
        Do
            getdate = InputBox("Please Enter The Current Date", "Check Date", Date)
            'cancel pressed
            If StrPtr(checkdate) = 0 Then Exit Sub
        Loop Until IsDate(getdate)
        checkdate = DateValue(getdate)
    End If

Dave

 

[W R]

Dave, I have tried inserting this below the

 If IsDate(checkdate) Then

Line but it doesn't seem to do anything...just runs the normal script.

 

[dmt32]

This line

If DateValue(checkdate) <> Date Then

will only display the InputBox if CheckDate value in F2 does not match todays date. If You want the InputBox to display at all times, delete the if statement

Dave

 

[W R]

Brilliant again Dave, one question, what can I put into this to exit the whole process if I press cancel when entering the date in the input box?

 

[dmt32]

Brilliant again Dave, one question, what can I put into this to exit the whole process if I press cancel when entering the date in the input box?

sorry, I have a typo in my code.
Replace with the line shown below

'cancel pressed
            If StrPtr(getdate) = 0 Then Exit Sub

Dave

 

[W R]

sorry, I have a typo in my code.
Replace with the line shown below

'cancel pressed
            If StrPtr(getdate) = 0 Then Exit Sub

Dave

Perfect. Thanks Dave.

This works like a dream!!