VBA Run-Time Error 1004

[EBoudreau]

I am having a Run Time Error Code 1004 pop up after making changes in the bolded line of code on the "Stage Times" sheet from:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim xHour As String
    Dim xMinute As String
    Dim xWord As String
    Dim rngMonitored As Range, rng As Range
    
    Set rngMonitored = Intersect(Target, Range("B4:C63, E4:F63, L4:M63, O4:P63, V4:W63, Y4:Z63, AF4:AG63, AI4:AJ63, B68:C127, E68:F127, L68:M127, O68:P127, V68:W127, Y68:Z127, AF68:AG127, AI68:AJ127, B132:C191, E132:F191, L132:M191, O132:P191, V132:W191, Y132:Z191, AF132:AG191, AI132:AJ191"))
    If rngMonitored Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each rng In rngMonitored
        xWord = Format(rng.Value, "0000")
        xHour = Left(xWord, 2)
        xMinute = Right(xWord, 2)
        On Error Resume Next
        rng.Value = TimeValue(xHour & ":" & xMinute)
    Next rng
    
    Application.EnableEvents = True


End Sub

TO

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim xHour As String
    Dim xMinute As String
    Dim xWord As String
    Dim rngMonitored As Range, rng As Range
    
    Set rngMonitored = Intersect(Target, Range("B4:C103, E4:F103, L4:M103, O4:P103, V4:W103, Y4:Z103, AF4:AG103, AI4:AJ103, B108:C207, E108:F1207, L108:M1207, O108:P207, V108:W207, Y108:Z207, AF108:AG207, AI108:AJ207, B212:C311, E212:F311, L212:M311, O212:P311, V212:W311, Y212:Z311, AF212:AG311, AI212:AJ311"))
    If rngMonitored Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each rng In rngMonitored
        xWord = Format(rng.Value, "0000")
        xHour = Left(xWord, 2)
        xMinute = Right(xWord, 2)
        On Error Resume Next
        rng.Value = TimeValue(xHour & ":" & xMinute)
    Next rng
    
    Application.EnableEvents = True


End Sub

I Added rows in the sheet under each of the ranges in the bolded code so i needed to change the ranges in the code to work on the added rows in the sheet.

Did i miss something? I thought all I had to do was change the ranges in the code when I added the rows in the sheet.

I have the faulty spreadsheet posted at the following link for download to troubleshoot:

 

[EBoudreau]

File available at :
https://drive.google.com/open?id=1mRI2xTc8HNJAHijr8gvQv-JJhxM0OQMIiQ03tqrgLOA

 

[RoryA]

You can only pass a 255 character string as the argument to Range. As it happens, all you need to do here is remove the spaces after the commas.

 

[EBoudreau]

You can only pass a 255 character string as the argument to Range. As it happens, all you need to do here is remove the spaces after the commas.

That worked perfectly! Thank you very much! Such a simple fix.... yet easy to miss!

Thanks again!

 

[EBoudreau]

RoryA,

Would you happen to know how to adjust password protection that is written into the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim rng As Range
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="WellingtonFrac"
    
    Set rng = Intersect(Target, Range("E8:CQ19, E22:CQ33, E36:CQ47"))
    If Not rng Is Nothing Then Call Capitalise(rng)
        
    Set rng = Intersect(Target, Range("D55:D1585"))
    If Not rng Is Nothing Then Call ConvertToTime(rng)
        
    ActiveSheet.Protect Password:="WellingtonFrac"
    Application.EnableEvents = True


End Sub

It is requiring that I unlock the sheet after every change I make to the format of the sheet.

For example:

I unlock the sheet, add a row, then the sheet auto locks. I need to unlock it again to add another row or change the color of cells, and the like. I need to make a lot of changes to the sheet that is very difficult with the auto locking code. And honestly, I've lost track of why that is in there to begin with.

 

[RoryA]

If you don't want the sheet protected, just remove the Unprotect and Protect lines.

 

[EBoudreau]

Interesting... yet again, so simple. lol.

That worked. Thank you!