Help with For/If/Msgbox

W R

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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);
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
If want an input box replace suggested line of code with following & see if helps

VBA Code:
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

W R

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Dave, I have tried inserting this below the

VBA Code:
 If IsDate(checkdate) Then

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
This line

VBA Code:
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

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,484
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
'cancel pressed
            If StrPtr(getdate) = 0 Then Exit Sub

Dave
 

W R

New Member
Joined
Jan 13, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
sorry, I have a typo in my code.
Replace with the line shown below

Rich (BB code):
'cancel pressed
            If StrPtr(getdate) = 0 Then Exit Sub

Dave
Perfect. Thanks Dave.

This works like a dream!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,626
Messages
5,625,950
Members
416,144
Latest member
JohnMacDonald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top