combine multiple worksheet_Change events

nobbyclarke

New Member
Joined
Nov 25, 2015
Messages
24
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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
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

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
I wonder if you tested the Target Value for numeric vs. not if it would work....

Maybe something like this:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,862
Office Version
365
Platform
Windows
Another option
Code:
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
Code:
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,540
Messages
5,511,930
Members
408,868
Latest member
AndrewLeonard

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top