vba code top check before over-writing existing cell

palaeontology

Active Member
Joined
May 12, 2017
Messages
444
Office Version
  1. 2016
Platform
  1. Windows
I have a userform that contains a command button with the following code ...

VBA Code:
Private Sub CommandButton11_Click()
Range("b1").Value = Now()
Range("b2").Value = Now()
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

This code does two things ...
1. it timestamps the time into cell B1
2. it creates a running clock in cell B2, showing the current time.

How would I adjust that code to allow for the following two possibilities ...
1. if cell B1 already has a timestamp, provides a message that queries whether the user really does intend to over-ride the current timestamp. The message might read something like this ... "there is already a Start Time in that cell. Do you wish to over-ride it" and then allows the user to say 'yes' to overwrite it with the new current time timestamp, or 'no' to leave the timestamp as it is
or
2. if cell B1 is currently empty, proceed with the code as I currently have it (as seen above).

Kind regards,

Chris
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Perhaps
VBA Code:
Private Sub CommandButton11_Click()
Dim result As Integer

If Not Range("b1") = "" Then
   result = msgbox("There is already a Start Time in cell B1. Do you wish to over-ride it?",vbYesNo)
   If result = vbYes Then Range("B1") = Now()
End If
Range("b2").Value = Now()
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub
With that, it would not matter if B1 had "dog" in it so it's not dependent upon having a time value in the cell.
EDIT - I may have misunderstood that. You don't want the code to continue regardless? Just if B1 was empty?
 
Upvote 0
Solution
Thankyou, so much, for that excellent help.

It worked perfectly,

Thankyou again !!!

Kindest regards,

Chris
 
Upvote 0
Glad to help, but I thought I misunderstood the need. Thanks for the recognition.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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
Back
Top