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

 

[FormR]

Hi, welcome to the forum!

When your code changes cells on the worksheet the worksheet_change event is called again, and again, and again..etc - so you need to disable events at the start and re-enable at the end.

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


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")
End If


Application.EnableEvents = True


End Sub

 

[sassriverrat]

So it should appear now that my date isn’t going in as it should and I have a new stack error message (1008). Can I send the excel workbook and let you see if anything is obvious. It’s super basic and there’s not much code to it. Thanks for helping me! Learning a lot quickly!

 

[FormR]

Can I send the excel workbook and let you see if anything is obvious.

Hi, the best thing to do would be to post your code here as you have it now, let us know the exact error description and the line that is highlighted when you click debug after the error message.

 

[sassriverrat]

Ok here goes.

Cell F4 should read "No Data Input"
When data is input into R8, the cell should change to today's date in the dd-mmm-yy format and it should save like a time stamp. All of this should happen UNLESS data is input into W6, in which case, W6 should override all (date override to override the time stamp).

Thanks for the help.

Each time I'm getting a stack error. It was 28 I believe, now it's 1008 OR I just don't get a date input at all.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
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]

Private Sub Worksheet_Change(ByVal Target As Range)Application.EnableEvents = False
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




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


End Sub

 

[FormR]

It was 28 I believe, now it's 1008

Hi, what is the exact error message and what line of code causes it?

OR I just don't get a date input at all.

Look at where you are re-enabling events in the code in post 6 versus the suggested code.

 

[MARK858]

Run sub ddd then try the worksheet change code below

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

Sub ddd()
    Application.EnableEvents = True
End Sub

 

[sassriverrat]

So I tried adding the "Sub ddd()" code above after the end of my code, but before "End Sub" This gives me a "Compile Error: Expected End Sub"

Going back on my regular code, putting data into the 8,18 (R8) cell doesn't give me a datenow anymore in F4 like it is supposed to. I have attached the original code. @MARK858 - the code you gave me didn't give me anything. It didn't give me any errors and I don't have any errors running at all now, I just don't have anything happening- no date, no errors, no changes.

Thanks for looking.

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


If Cells(10, 26) = "'Yes" Then
Cells(9, 18) = "'Exact"
End If
    Application.EnableEvents = True
End Sub

 

[MARK858]

I didn't say add it to any code, I said run it first!