combine multiple worksheet_Change events

[nobbyclarke]

Hi there can someone help.

I have a sheet where I want on a worksheet change event to.....
1) convert a range of cells to uppercase
2) allow the entry in a different range of cells of time in the format hhmm (without the colon)

this is what I have now but here is the problem

I can only get one bit of the code to work at a time. ie. if i comment out the upper case bit then the time bit works and visa versa

bit of a VBA newby so I hope I have fulfilled the posting criteria and my question doesnt make me look like a complete wally.

Appreciate any help please
Rob

Private Sub Worksheet_Change(ByVal Target As Range)


'This bit allows you to enter time in 24hr clock without the colon
Application.EnableEvents = True
If Intersect(Target, Range("e15:e30,f14,g14:g29, o16, o19,o23,o25")) 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


'this bit below makes all cells capital in the range


Application.EnableEvents = True
If Intersect(Target, Range("e7,e8,j9:j11,n5:n7,n9, o18,o29,c14:c30")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True


End Sub


'PROBLEM - I can only get one bit to run either the time bit or the uppercase bit but not both side at the same time

 

[jproffer]

If the first part changes the Target to TimeValue "0000", and the second part makes the Target uppercase, then that's a problem. Both parts will never work in the same cell. The TiemeValue "0000" will inherently return numbers...and you can't make numbers uppercase.

 

[jproffer]

I wonder if you tested the Target Value for numeric vs. not if it would work....

Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xHour As String
Dim xMinute As String
Dim xWord As String

Application.EnableEvents = False

Select Case Target.Value
    Case IsNumeric
        If Intersect(Target, Range("e15:e30,f14,g14:g29, o16, o19,o23,o25")) Is Nothing Then 
            Exit Sub
        Else
            xWord = Format(Target.Value, "0000")
            xHour = Left(xWord, 2)
            xMinute = Right(xWord, 2)
        End If
            Target.Value = TimeValue(xHour & ":" & xMinute)

    Case Else

        If Intersect(Target, Range("e7,e8,j9:j11,n5:n7,n9, o18,o29,c14:c30")) Is Nothing Then 
            Exit Sub
        Else
            Application.EnableEvents = False
                Target.Value = UCase(Target.Value)
            Application.EnableEvents = True
        End If

End Select

End Sub

I'm not sure about the "IsNumeric" part of the Case. I'm not in a position to test it until tomorrow morning, but something along those lines might do what you want.

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
'This bit allows you to enter time in 24hr clock without the colon
If Not Intersect(Target, Range("e15:e30,f14,g14:g29, o16, o19,o23,o25")) Is Nothing Then
   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 GoTo 0
   Application.EnableEvents = True
End If
'this bit below makes all cells capital in the range
If Not Intersect(Target, Range("e7,e8,j9:j11,n5:n7,n9, o18,o29,c14:c30")) Is Nothing Then
   Application.EnableEvents = False
   Target.Value = UCase(Target.Value)
   Application.EnableEvents = True
End If
End Sub

At the moment if you change cell E7 this line

If Intersect(Target, Range("e15:e30,f14,g14:g29, o16, o19,o23,o25")) Is Nothing Then Exit Sub

will cause the event to Exit, so you never get to the second part of the code.