Run Multiple VBA codes on the same sheet

[EBoudreau]

I've researched how to run more than one VBA code in the same sheet and I'm extremely confused as to how to do so.

I don't necessarily want to combine two or three tasks, where as I am more interested in pasting one code command, and then just paste another below it.

Is there a way to do so if I find a code I want to use with other code already in place?

All the descriptions I've read are pretty confusing to me.

 

[EBoudreau]

The Code i'm using is:

'In Sub Worksheet_Change, you'll need:
Set rngToChange = Intersect(Target, Range("D55:D1585"))
If Not rngToChange Is Nothing Then _
    Call ConvertToTime(rngToChange)

'In Sub ConvertToTime
'Change:
rngToConvert.NumberFormat = "HH:MM AM/PM"
'To:
rngToConvert.NumberFormat = "HH:MM"

The second line is said to be causing the error.

I want this code to be active on the "Frac Report" tab where there is already existing code running.

Here's a link to the file so you can download it and play with it if need be.

https://drive.google.com/file/d/0B0oQEry29JNjenNKSGhaeGJRTEE/view?usp=sharing

 

[djreiswig]

Do you have the range variable dimmed?

    Dim rngToChange As Range

 

[EBoudreau]

Would that code just go into a module?

 

[StephenCrump]

Try this:

'In Sheet module for "Frac Report"
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
    Application.EnableEvents = False
    
    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)
        
    Application.EnableEvents = True

End Sub

'In Module1 code module
Sub Capitalise(rngToConvert As Range)

    Dim rng As Range
    
    For Each rng In rngToConvert
        With rng
            If Not .HasFormula Then .Value = UCase(.Value)
        End With
    Next rng

End Sub
Sub ConvertToTime(rngToConvert As Range)

    Dim rng As Range
    
    For Each rng In rngToConvert
        rng.Value = Format(rng.Value, "00\:00")
    Next rng
    
    rngToConvert.NumberFormat = "HH:MM AM/PM"

End Sub

 

[djreiswig]

Would that code just go into a module?

Should go at the top of the sub somewhere before the Set = line

 

[EBoudreau]

Perfect. It looks like that worked beautifully!!! Thank you very much!

 

[EBoudreau]

I just tried running some test numbers and when i went to "Clear Contents" of any number of cells or even of just a single cell, i ended up with a run time error 13 message. Do you have any idea as to why?

I have this code in the sheet code section:

Private Sub Worksheet_Change(ByVal Target As Range)
If 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")) Is Nothing Then Exit Sub
Dim xHour As String
Dim xMinute As String
Dim xWord As String
Application.EnableEvents = False
xWord = Format(Target.Value, "0000")
xHour = Left(xWord, 2)
xMinute = Right(xWord, 2)
On Error Resume Next
Target.Value = TimeValue(xHour & ":" & xMinute)
On Error Resume Next
Application.EnableEvents = True
End Sub

 

[StephenCrump]

I just tried running some test numbers and when i went to "Clear Contents" of any number of cells or even of just a single cell, i ended up with a run time error 13 message. Do you have any idea as to why?

Any Worksheet-Change event code should allow for the possibility of Target containing more than one cell. Otherwise, any reference to Target.Value will cause an error.

See in Post #6 how my Subs ConvertToTime and Capitalise loop through each cell in the Intersect of Target and the range you want to monitor?

 

[EBoudreau]

Ok. I understand.... i think.
However i can not see where I would change the code in Post #17 to accommodate what you described above.
I've gone through so many revisions of this that it's getting tough to remember what I did in the past and how I ended up where i am now.

 

[StephenCrump]

Try:

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