Multiple Worksheet_change

[bedsy]

Hi All,

Another day..... another issue.

I have a worksheet that I need to run multiple Worksheet_changes with.

The first part of the code to change range to uppercase works but the second portion of the code, meant to automatically change a cell from text to time format HH:MM does not..... keeps returning 0:00. for example if I enter 1330 it should change to 13:30.

Any thoughts??

Bedsy

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A4:K27")) Is Nothing Then
        For Each rng In Intersect(Range("A4:K27"), Target)
            Target.Value = UCase(Target.Value)
        Next rng
    End If
   Set rng = Target.Parent.Range("H:H, I:I, K:K, L:L")
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Row < 2 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then
    
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Undo
    End With
    
    Target.Value = x / 1440
    Target.NumberFormat = "[h]:mm"
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
ExitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

 

[kevin9999]

Could you post your sheet using the XL2BB - Excel Range to BBCode?

 

[bedsy]

Note: slight change to above.

The Uppercase code works when run separately and the text to time returns 0:00 but once I have combined in the one workbook_change neither are functioning properly.

Master workbook.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ARRIVALS								Departures
2
3	Operator	Commodity	Facility	Incoming ID	Planned Unit	Planned Arr Time	Planned Unload Time	Completed Time	Outgoing ID	Planned Unit	Planned Dep Time
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Days Trains

 

[kevin9999]

Thank you for the XL2BB See if the following comes close to what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4:K25"), Target) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target) And Target <> "" Then
            Target = Format(Target, "00\:00")
        Else
            Target = UCase(Target)
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[bedsy]

Thank you for the XL2BB See if the following comes close to what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4:K25"), Target) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target) And Target <> "" Then
            Target = Format(Target, "00\:00")
        Else
            Target = UCase(Target)
        End If
        Application.EnableEvents = True
    End If
End Sub

OMG.... Looks so simple but I have been looking at this for days and just could not work it out. Over thinking it again.

The only issue is that column's D, G, I and J also have fields that contain just numbers and I do not require these as 00:00.

the time format only needs to apply to F, G, H, K

 

[kevin9999]

Try this variation:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4:K25"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Not Intersect(Target, Range("F:H,K:K")) Is Nothing And IsNumeric(Target) And Target <> "" Then
            Target = Format(Target, "00\:00")
        Else
            Target = UCase(Target)
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[bedsy]

that works perfect...... Thank you so much.

 

[kevin9999]

You're welcome Bedsy, and thanks for the feedback