Moving VBA code to module

Question

I have the code shown below in VBA on Sheet 1, which allows me to enter time as numbers, (without the colon), and it then converts the numbers to time format. I use it on more than one sheet, but the cells using the code are different from one sheet to the next. The only problem is that the code is confined to the sheet, so I have to copy it to each sheet and change the cells referenced. I would like to move the code to a module, and then be able to call it for each sheet. Any suggestions? Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim TimeStr As String

On Error GoTo EndMacro

If Application.Intersect(Target, Range("A1:A5")) Is Nothing Then

   Exit Sub

End If

End Sub

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 AM

(More ‘Select Case’ code)

       Case Else

           Err.Raise 0

   End Select

   .Value = TimeValue(TimeStr)

End If

End With

Application.EnableEvents = True

Exit Sub

EndMacro:

MsgBox "You did not enter a valid time"

Application.EnableEvents = True

End Sub

Answer 1

Thanks to you also, Steve.  Like I told Rick, those of us who need help rely on you guys a lot.  Thanks for all the help.

Howard

Answer 2

Move your code to ThisWorkbook and change the first line to

Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As Range)

Regards

 Steve

Answer 3

Put your code in the SheetChange event procedure for the ThisWorkbook module. The ThisWorkbook entry is located in the Project Explorer window within the VB editor. You should not need to change your code, but to be safe, you might want to change your Range("A1:A5") call to Sh.Range("A1:A5").

Answer 4

Since the ranges on each sheet will be diffent maybe something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim TimeStr As String

    Dim mySheet As String    mySheet = ActiveSheet.Name    On Error GoTo EndMacro

    Select Case mySheet        Case "Sheet1"            If Application.Intersect(Target, Range("A1:A5")) Is Nothing Then                Exit Sub            End If        Case "Sheet2"            If Application.Intersect(Target, Range("B10:B50")) Is Nothing Then                Exit Sub            End If      'More case statementsIf 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 AM

(More ‘Select Case’ code)

       Case Else

           Err.Raise 0

   End Select

   .Value = TimeValue(TimeStr)

End If

End With

Application.EnableEvents = True

Exit Sub

EndMacro:

MsgBox "You did not enter a valid time"

Application.EnableEvents = True

End Sub

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 5

Thanks Rick;

I'm sure glad you folks are still around.  You've been more help to me in the past than you can imagine.

Howard