Solved: Need help with combining 2 VBA Worksheet_Change Codes

[Toonies]

Hi I would like help to combine the following two Worksheet_Change Codes into One but when I try I get the following error message.

Compile error:

Ambiguous name detected:Worksheet_Change

hopefully it can be done I would welcome any help or suggestions.

here is the 1st Code


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrTimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrTimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrTimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrTimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrTimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub


Here is the 2nd Code



Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput
Application.EnableEvents = False
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "00")
Else
UserInput = Format(Target.Value, "00")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
Application.EnableEvents = True
End If
End Sub


Many thanks

Toonies

[CharlesH]

Toonies,


FIY, you can not have the "Same" named macro. That's why you received the error.

[Toonies]

Hi thank you for pointing that out, but would still like to combine the 2 Vba's into 1 is that possible.

[CharlesH]

Toonies,

You can merge the 2 codes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrTimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrTimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrTimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrTimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrTimeStrText)
End If
End with
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "00")
Else
UserInput = Format(Target.Value, "00")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
End Sub


I didn't add this line not sure how or where it would work.




Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True

[Toonies]

Hi when I try the code I get the following error

Complier Error:

Block If without End If


With there final End Sub highlighted

[CharlesH]

Hi,

I amended the code and added the "End Sub"
Sorry for the error

[Toonies]

Hi still cannot quite fix the error

[CharlesH]

Hi,

I copied the code too my workbook and am looking at it.

[CharlesH]

Hi,


Here's the code and when in my workbook I did a compile and rec'd no errors




Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrTimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrTimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrTimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrTimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrTimeStrText)
End If
End With
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
End If
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "00")
Else
UserInput = Format(Target.Value, "00")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

[Toonies]

hi now we're starting to make progress.

That sorted out the compile error message.

The 1st part of the vba works but the 2nd part doesn't

[CharlesH]

Hi,


Could you attach a copy of your workbook?

[CharlesH]

Hi,


The second part of your code requires a Dim for "UserInput". I did not see that. My bad.
Not sure if that's causing you the problem.

[Toonies]

here is the 1st vba workbook

http://www.mediafire.com/?byywwatmtd8adtc

here is the 2nd vba workbook

http://www.mediafire.com/?2xc55x9rrfvknte

many thanks

[CharlesH]

HI,

I'll look at the files, but can you give a brief description what you do to activate the code and what it is you expect too see. It's getting late here, but will look for a while. May not be able to get back until Tuesday. Hopefully some one else may pick up on this. If not I'll do what I can

[Toonies]

hi when you enter a time ie 1700 into a cell in the Target Range the vba should change it to 17:00 automatically when you press enter or move onto any other cell on the worksheet.

The 1st vba works fine both on its own and when it is combined with the 2nd vba.

the 2nd vba works fine on its own but not when it is combined with the 1st

[CharlesH]

Toonies,

Your 2nd code work fine because your entering dat in the required fields. However, I do not see in the "Main" worksheet where you would use or enter data in the range required to activate the 2nd code.

[Toonies]

In the Worksheet Testx in the following range

E116, K116, Q116, W116, AC116, AI116, AO116

each has Data Validation list in them.

I wish to replace that and use the 2nd VBA to key in Time within the same range of cells

but as yet no joy

[Toonies]

Hi I have found a way to do it without combining the 2 VBA's, still keeping the 1st VBA and using a helper cell for the Range

using the following format ##":"##

E116, K116, Q116, W116, AC116, AI116, AO116

with the following formula copied and pasted across

=SUM(E115/100)*60/1440

but if you can come up with a VBA solution then let me know

[CharlesH]

Toonies,



Hi, see if this is what you want. I tested with changing the values in the first range and for the second range. I removed the dat validation in the second range.

[Toonies]

CharlesH "YOU ARE THE MAN"

that works perfect

I am only starting to get into VBA is there any pointers that you may recommend.

A BIG THANK YOU :thumb

[CharlesH]

Toonies,

Thanks for letting me know that it works. As for pointers, even though I did not name the variables in your workbook. You should "Name" all variables. This will allow excel to run a little bit faster. Thats why you have the "Option Explicit" at the top or should be at the top of your modules. Pleas mark this thread as solved if you are done with it.

[Toonies]

Here is the Combined VBA the full credit goes to CharlesH


Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
Dim UserInput
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) Is Nothing Then
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
' Application.Undo
Application.EnableEvents = False
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "00")
Else
UserInput = Format(Target.Value, "00")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
Application.EnableEvents = True
Exit Sub
End If
End If
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
If Target.Column = "1" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrTimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrTimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrTimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrTimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrTimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

[CharlesH]

Updated code per user. Previous code did not format row 17 correctly.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
Dim UserInput
On Error GoTo EndMacro
If Not Application.Intersect(Target, Range("C9:C39, D9:D39, I9:I39, J9:J39, O9:O39, P9:P39, U9:U39, V9:V39, AA9:AA39, AB9:AB39, AG9:AG39, AH9:AH39, AM9:AM39, N9:AN39")) Is Nothing Then
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
If Target.Column = "1" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrTimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrTimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrTimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrTimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrTimeStrText)
End If
End With
End If
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
' Application.Undo
Application.EnableEvents = False
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "00")
Else
UserInput = Format(Target.Value, "00")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
Application.EnableEvents = True
Exit Sub
End If
End If
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

[Toonies]

Hi CharlesH

Your amended code works Great.

I did have a tinker with previous solution and I came up with this (I think more luck than knowledge from myself) but I am starting to pick it up

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStrText As String
Dim UserInput
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39, D9:D39, E9:E39, I9:I39, J9:J39, K9:K39, O9:O39, P9:P39, Q9:Q39, U9:U39, V9:V39, W9:W39, AA9:AA39, AB9:AB39, AC9:AC39, AG9:AG39, AH9:AH39, AI9:AI39, AM9:AM39, AN9:AN39, AO9:AO39")) Is Nothing Then
If Not Intersect(Target, [E116, K116, Q116, W116, AC116, AI116, AO116]) Is Nothing Then
If Not Selection.Cells.Count = 1 Then
' Application.Undo
Application.EnableEvents = False
If Target.Value < 1 Then
UserInput = Format(Int(Target.Value * 24) * 100 + ((Target.Value * 24) - Int(Target.Value * 24)) * 60, "000")
Else
UserInput = Format(Target.Value, "000")
End If
If Len(UserInput) > 1 Then
Target = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
End If
Application.EnableEvents = True
Exit Sub
End If
Exit Sub
End If
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
If Target.Column = "1" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStrText = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStrText = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStrText = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStrText = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStrText)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub

Toonies