Edit my VBA code please?

  • Thread starter Thread starter Gerard Sanchez
  • Start date Start date
G

Gerard Sanchez

Hi,



I was wondering if there's a way to make this code for the workbook itself
not the worksheet.

The code worked perfectly on the worksheet, but when I tried to copy it into
the workbook VBA, and change the word "Worksheet" into "Workbook," it didn't
work :(



From: Private Sub Worksheet_Change(ByVal Target As Range)

Into: Private Sub Workbook_Change(ByVal Target As Range)



The workbook I'm working on contains about 30 worksheets and having the VBA
code copied onto each worksheet has considerably slowed down the file.

I was thinking that it will be faster that the code resides on the workbook
and not the worksheet,



Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range

Set myCell = Range("B36")



If Not Intersect(Target, myCell) Is Nothing Then

Range("E3").Activate



Dim myCell As Range

Set myCell = Range("E36")



If Not Intersect(Target, myCell) Is Nothing Then

Range("H3").Activate



Dim myCell As Range

Set myCell = Range("H34")



If Not Intersect(Target, myCell) Is Nothing Then

Range("B55").Activate



' . . . and on and on copied and pasted many times (its a large worsheet) ,
changing only the "myCell" address and "Activate" cell address '



End If



End Sub
 
Hi

Use this event instead:

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

Above the modules is two dropdowns. Those will help you display/choose the
available events and macros.

HTH. Best wishes Harald
 
Also this code would be easier to work with as well,

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

If Target.Address = "$B$36" Then Range("E3").Select
If Target.Address = "$E$36" Then Range("H3").Select
If Target.Address = "$H$34" Then Range("B55").Select


End Sub

the code goes into the WorkBook Module check out this site if you do
not know where it is
http://www.contextures.com/xlvba01.html#Workbook
 
Back
Top