VBA Time Stamp Stack Error

[sassriverrat]

Thanks for helping

Teaching myself VBA and I keep getting a run time stack error on one of my sheets and it always highlights the same line. I can't seem to get a screenshot to post so I've copied and pasted the entire code below for the sheet. Most of the sheet's mathematics are purely formulas in the page- starting to convert stuff to VBA.

Thanks for the Help

Error Line: cells(4, 6) = Format(Now(), "dd-mmm-yyy")

VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(6, 23) <> "" Then
Cells(4, 6) = Cells(6, 23)
End If


If Cells(8, 18) <> "" And Cells(6, 23) = "" Then
Cells(4, 6) = Format(Now(), "dd-mmm-yy")


Application.EnableEvents = True
End If
End Sub

 

[sassriverrat]

Ah ok. So I did run it but I didn't see anything happen- putting the sub ddd portion in gave me the "Compile Error: Expected End Sub" piece. Otherwise I tried using your string of code in place of mine and didn't see a change there. It won't put a time stamp in or anything.

 

[RoryA]

Your code could have disabled events, which would prevent it running again. The ddd routine was just to reenable events so your change event code would work again; you wouldn't see it do anything.

 

[MARK858]

File with my code in. Enter a date in W6, see what happens to F6 then put a date in R8 and then go back to W6, clear the contents, press enter and see what happens to F6.

https://app.box.com/s/rcbggblyw0uddp2qwb8w3p46aaxeylwh

 

[sassriverrat]

So I loaded

Private Sub Worksheet_Change(ByVal Target As Range)    Application.EnableEvents = False
    If Not Intersect(Target, Cells(6, 23)) Is Nothing Then


        If Target <> "" Then
            Cells(4, 6) = Target
        End If




        If Cells(8, 18) <> "" And Target = "" Then
            Cells(4, 6) = Format(Now(), "dd-mmm-yy")
        End If
    End If




    If Cells(10, 26) = "'Yes" Then
        Cells(9, 18) = "'Exact"
    End If


    Application.EnableEvents = True
End Sub[code/]


No go. Didn't do anything in the box. Further, I inserted a sub ddd() code right before the End Sub and didn't get anything. Finally, just so there's no confusion, the F4 Cell is meant to show a timestamp date. The W6 cell is meant to be an "override" in case a different date needs to go in the F4 cell (F4 cell is in a printable area, W6 is not), and the R8 cell is supposed to contain a number and when a number is input into that cell it shows up on the sheet AND puts a time stamp in F4. 

Thanks for all the help thus far!

 

[MARK858]

First of all why do you persist with

Further, I inserted a sub ddd() code right before the End Sub and didn't get anything

Both Rory and myself have stated that the ddd code is to reset the Events to True in case your code finished prematurely and left it in a False state.

The code needs to be run in a SEPARATE sub from a regular module BEFORE the other sub is run to do the reset.

R8 cell is supposed to contain a number and when a number is input into that cell it shows up on the sheet AND puts a time stamp in F4.

This wouldn't have been coded for as your previous description implied the code was to run based on W6.
Try the code below AFTER you have run ddd separately.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    If Not Intersect(Target, Range("R8,W6")) Is Nothing Then

        If Target <> "" Then
            Cells(4, 6) = Target
        End If


        If Cells(8, 18) <> "" And Target = "" Then
            Cells(4, 6) = Format(Now(), "dd-mmm-yy")
        End If
    End If

    If Cells(10, 26) = "'Yes" Then
        Cells(9, 18) = "'Exact"
    End If

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

 

[sassriverrat]

Ok so I just copied and pasted your code. Further, I inserted

Sub ddd()    Application.EnableEvents = True
End Sub

Ok- It's halfway working. So, if I put a date in the W box (override), it spits the date in F4 as it should. If I put a number in the R8 box, it tries to put that as a date in F4 as well. So it needs to be that the R8 is a number, irrelevant of today's date (say it could be a measurement) and the F4 is the timestamp (date) for when that number was put in. R8 itself is not a date. Secondly, if anything is put into the override (W), it should override F4 automatically- say the time stamp is wrong and I want to correct, I put it in the Override (W) box. Finally, the sub ddd worked- I will create a button to reset it because it produces an error if you delete the F4 to run the module again.

 

[MARK858]

If you cell is formatted as a date then a number will appear as a date.

If you want it to appear as a number based on one cell and a date based on another then you need to put in code at each stage to tell Excel what to format the cell as.

 

[MARK858]

Also...

Finally, the sub ddd worked- I will create a button to reset it because it produces an error if you delete the F4 to run the module again.

I don't get an error if F4 is cleared (why would you delete rather than clear the cell?) with the code I posted.
What error are you getting?

 

[sassriverrat]

So I think with the date formatting there may be a bit of confusion here-
F4 always shows a date. It's the timestamp or target cell. It is within a printable area. Once this sheet is fixed, it will be a locked cell so the clearing contents makes sense.
R8 is a number cell. The number going into it is not a date- it is a recorded measurement. The purpose of F4 is to show a date of when the measurement in R8 was taken.
W6 is an "override" for F4. Let's say that the R8 measurement was incorrectly input and the problem is discovered days later, the purpose of W6 is to allow a new date to be input into F4 that isn't necessarily today (maybe I took the measurements in R8 today but I want to backdate F4, make sense?)

So the rules- If a number is input into R8, a timestamp should be put into F4. F4 does NOT equal R8, it's an independent number. And if W6 is NOT blank, F4 equals W6.

As for the error- I will put arrows below in the code that shows error. Sheet works now- error only comes if a date is input into W6 and then deleted. Sheet basically has to be fool-proof since the people using it don't even know how to input formulas into excel, much less VBA code (I know, I'm basic here as well!).

Private Sub Worksheet_Change(ByVal Target As Range)    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    If Not Intersect(Target, Range("R8,W6")) Is Nothing Then


   ---->If Target <> "" Then
            Cells(4, 6) = Target
        End If




        If Cells(8, 18) <> "" And Target = "" Then
            Cells(4, 6) = Format(Now(), "dd-mmm-yy")
        End If
    End If


    If Cells(10, 26) = "'Yes" Then
        Cells(9, 18) = "'Exact"
    End If


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


End Sub

 

[sassriverrat]

So I think there might be a little confusion here.

F4 is a timestamp date. It is a date record. It can be changed by inputting data into W6.

R8 is a separate number- it is a measurement and is not a date. When measurements are taken and input into R8, the purpose of F4 is to record the date of when R8 had data input.

W6 is the "override" to change the date in F4 in case it's wrong (say I need to backdate a measurement).


As for the error code- makes sense to clear contents and not delete. I only get the error code (I put an error point at it below) when I input a date into W6 and then delete or clear the contents of W6.

Private Sub Worksheet_Change(ByVal Target As Range)    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    If Not Intersect(Target, Range("R8,W6")) Is Nothing Then


   -----> If Target <> "" Then
            Cells(4, 6) = Target
        End If




        If Cells(8, 18) <> "" And Target = "" Then
            Cells(4, 6) = Format(Now(), "dd-mmm-yy")
        End If
    End If


    If Cells(10, 26) = "'Yes" Then
        Cells(9, 18) = "'Exact"
    End If


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


End Sub