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.

 

[StephenCrump]

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

In principle, YES, no problem, and I'm sure we can help.

Can you please give us more detail about what you're trying to do?

And if you have two pieces of code, can you post these?

 

[EBoudreau]

I am away from the computer for the night. I will get back with you when I return to work. Thank you very much for your patience!

 

[EBoudreau]

In principle, YES, no problem, and I'm sure we can help.

Can you please give us more detail about what you're trying to do?

And if you have two pieces of code, can you post these?

Here is the code i am using:
Excel 2010 on Windows 7

What I'm trying to do is keep the top code where I am auto-capitalizing all letters entered into a few ranges of cells, and had code integrated to it where as errors were ignored when i would deleted or clear contents that was breaking the code.

The bottom code is where I want to add control to make a different range cells format time so that a ":" is automatically inserted to the time value for the cell. I would like them to work together but cannot paste the code into the sheet so they will both work.

Private Sub Worksheet_Change(ByVal Target As Range)




   On Error GoTo myerror
    If Not (Application.Intersect(Target, Range("E9:CQ19, E22:CQ32, E35:C45")) Is Nothing) Then
        Application.EnableEvents = False
        With Target
            If (Not .HasFormula) And (.Count = 1) Then Target = UCase(Target.Cells(1))
        End With
    End If




myerror:
Application.EnableEvents = True
End Sub

and

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D52:D1582")) 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

Is there a way for me to just run one code into the next with a line break or something, or do they need to be merged and re-written?
The last time I had assistance on this, the codes were combined. Which is fine if it needs to be done that way. I was just hoping there was an easier way for a Novice VBA user like myself to add code to existing.

 

[EBoudreau]

And to add to the above problem I'm having from above, when I clear contents if I need to reuse a range of cells, I end up with a run-time error.
The line of code with the issue is in bold italic.

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:D191, 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
[I][B]xWord = Format(Target.Value, "0000")[/B][/I]
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]

You can't have two Worksheet_Change Subs in the same sheet module.

Probably your best solution here is to copy and paste your code into one Worksheet_Change Sub. You'll just need to rearrange slightly, e.g. it's good practice to have all your Dim statements together at the start of the Sub.

Please post back if you have any problems combining these codes.

The alternative is to do something like this:

'In Sheet module
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngToChange As Range
    
    Application.EnableEvents = False
    
    Set rngToChange = Intersect(Target, Range("E9:CQ19, E22:CQ32, E35:C45"))
    If Not rngToChange Is Nothing Then _
        Call Capitalise(rngToChange)

    Set rngToChange = 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:D191, E132:F191, L132:M191, O132:P191, V132:W191, Y132:Z191, AF132:AG191, AI132:AJ191"))
    If Not rngToChange Is Nothing Then _
        Call ConvertToTime(rngToChange)

    Application.EnableEvents = True

End Sub
'In a 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

And to add to the above problem I'm having from above, when I clear contents if I need to reuse a range of cells, I end up with a run-time error.

Note the loop structures in my two Subs that allow for more than one cell in rngToConvert (i.e. in Target in the Worksheet_Change event). Your code is trying to evaluating Target.Value when Target contains more than one cell, leading to a run-time error.

 

[EBoudreau]

How do I make this so that only cells range D55:D1585 are formatted as 24 hour time with a : and no AM/PM.

I have other cell ranges that I would like to remain untouched so I can just type text in them. Everything else is wonderful!!

 

[StephenCrump]

Assuming you're using code similar to Post#6, then:

'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"

 

[EBoudreau]

I am getting a Compile Error in the line:

[COLOR=#333333]Set rngToChange = Intersect(Target, Range("D55:D1585"))[/COLOR]

from the code in the previous reply.

Set is highlighted when i return to the code screen.

 

[StephenCrump]

Can you please post the complete code you're using?