PDA

View Full Version : Solved: workbook vs worksheet



cmpgeek
12-22-2004, 02:13 PM
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 :help ?

Ken Puls
12-22-2004, 02:23 PM
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
12-22-2004, 02:30 PM
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
12-22-2004, 02:39 PM
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
12-23-2004, 06:08 AM
looks great kpuls! thanks so much!

byundt
12-25-2004, 07:43 AM
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