PDA

View Full Version : Solved: Adjust Macro Range To 2 Columns.



Barryj
04-26-2012, 08:39 AM
I have been trying to adjust this macro to only work on 2 columns, E10:E24 and H10:H24, I have tried altering the range but it always bugs out.

any help much appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

UserInput = Target.Value
If UserInput > 1 Then
NewInput = " " & Left(UserInput, Len(UserInput) - 2) & " :" & Right(UserInput, 2)

Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If

End Sub

Tinbendr
04-26-2012, 10:24 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E10:E24", "H10:H24")) Is Nothing Then
UserInput = Target.Value
If UserInput > 1 Then
MsgBox "works"
NewInput = " " & Left(UserInput, Len(UserInput) - 2) & " :" & Right(UserInput, 2)

Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

Bob Phillips
04-26-2012, 10:26 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UserInput, NewInput

If Not Intersect(Target, Me.Range("E10:E24,H10:H24")) Is Nothing Then

UserInput = Target.Value
If UserInput > 1 Then
NewInput = " " & Left(UserInput, Len(UserInput) - 2) & " :" & Right(UserInput, 2)

Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
End If
End Sub

Barryj
04-26-2012, 11:16 AM
Thanks Tinbendr & Xld, both solutions worked, much appreciated.