Workbook vs worksheet

[cmpgeek]

i have an Excel workbook with 80+ sheets within it. I have some VBA code that is set up to look at specific columns on each sheet. As i have gone back and modified the spreadsheet i have added columns within those specifially listed in the coding, i have to go back and change the range that the code looks at. Thus far i have been doing this over and over again.

What i am wondering is if i could place this code once in the "Thisworkbook" section and then i would only have to update the code once instead of 80+ times. I have an article that kpuls sent me dealing with where to place codes (thanx again kpuls!) but i am still unsure of what to do...

Can yall ?

[Ken Puls]

Hi Nomi,

Can you post up a sample of your code?

Generally, I prefer to put all my code in a standard module and call it from wherever, rather than use the ThisWorkbook or Sheet modules. But let's have a look at what you've got (if possible) and see what might be best for this situation.

Cheers,

[cmpgeek]

Hi Nomi,

Can you post up a sample of your code?

Generally, I prefer to put all my code in a standard module and call it from wherever, rather than use the ThisWorkbook or Sheet modules. But let's have a look at what you've got (if possible) and see what might be best for this situation.

Cheers,

here is the code that i am using on each page. it is exactly the same for each worksheet:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
'Define the range where you want the code to work (our example is "C:G").
     'Change within the " marks
    If Intersect(Target, Range("H:Q")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
        If IsNumeric(.Value) Then
            Application.EnableEvents = False
            Select Case .Value
            Case 0
                .NumberFormat = "[h]:mm"
            Case 1 To 99
                .Value = TimeSerial(0, .Value, 0)
                .NumberFormat = "[h]:mm"
            Case 100 To 9999
                .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
                .NumberFormat = "[h]:mm"
            Case Else
            End Select
        End If
    End With
errHandler:
    Application.EnableEvents = True
End Sub

thank you for taking the time to look at this for me!

[Ken Puls]

Okay, let's give this a shot!

First, you'll need to change the following sub in ALL of the sheets where it currently exists. It sucks, but this should be the last time. Just test it on one for now though to make sure it'll work right!

Private Sub Worksheet_Change(ByVal Target As Range)
     Call MyWsChange(Target)
 End Sub

And then make a new module, and paste this code in there:

Option Explicit
 
 Sub MyWsChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
'Define the range where you want the code to work (our example is "C:G").
      'Change within the " marks
     If Intersect(Target, Range("H:Q")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
         If IsNumeric(.Value) Then
             Application.EnableEvents = False
             Select Case .Value
             Case 0
                 .NumberFormat = "[h]:mm"
             Case 1 To 99
                 .Value = TimeSerial(0, .Value, 0)
                 .NumberFormat = "[h]:mm"
             Case 100 To 9999
                 .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
                 .NumberFormat = "[h]:mm"
             Case Else
             End Select
         End If
     End With
 errHandler:
     Application.EnableEvents = True
 End Sub

That should be it! No change to your original procedure (except the name and location!)

Cheers,

[cmpgeek]

looks great kpuls! thanks so much!

[byundt]

If the code is the same on all 80 sheets in the workbook, it could have been placed in a Workbook_SheetChange sub in the ThisWorkbook code pane. You then would not need to have any subs at all on the code panes for the individual worksheets. Following Ken's lead:

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

Given that the code is installed in just one place, I would have not bothered with the MyWsChange sub in a module sheet. If so, the Workbook_SheetChange sub becomes:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
'Define the range where you want the code to work (our example is "H:Q").
     'Change within the " marks
    If Intersect(Target, Range("H:Q")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
With Target
        If IsNumeric(.Value) Then
            Application.EnableEvents = False
            Select Case .Value
            Case 0
                .NumberFormat = "[h]:mm"
            Case 1 To 99
                .Value = TimeSerial(0, .Value, 0)
                .NumberFormat = "[h]:mm"
            Case 100 To 9999
                .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
                .NumberFormat = "[h]:mm"
            Case Else
            End Select
        End If
    End With
errHandler:
    Application.EnableEvents = True
End Sub