Target Range Activation
in [Programming]
|
Prev: Marco to protect / unprotect worksheet
Next: ActiveWorkbook.SaveAs Function - Can I specify the user account to save the file with
From: Philosophaie on 24 Jan 2010 16:13 Trying to get Wooksheet_Change to work: Public rng As Range Private Sub Workbook_Open() Dim range1 As Range Set range1 = Sheet4.Range("D4:F500") Set rng = range1 'I need a way to access the subroutine Worksheet_Change 'to fire the object Target range. I tried: 'Worksheet_Change(rng) that didn't work. End Sub Private Sub Worksheet_Change(ByVal Target As Range) End Sub
From: Don Guillett on 24 Jan 2010 16:22 Detail on what you are trying to do.?? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Philosophaie" <Philosophaie(a)discussions.microsoft.com> wrote in message news:29A5613F-6BA9-4256-AD55-099235798EC1(a)microsoft.com... > Trying to get Wooksheet_Change to work: > > Public rng As Range > Private Sub Workbook_Open() > Dim range1 As Range > Set range1 = Sheet4.Range("D4:F500") > Set rng = range1 > > 'I need a way to access the subroutine Worksheet_Change > 'to fire the object Target range. I tried: > 'Worksheet_Change(rng) that didn't work. > > End Sub > Private Sub Worksheet_Change(ByVal Target As Range) > > End Sub
From: Bernard Liengme on 24 Jan 2010 16:33 This code works Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D4:F500")) Is Nothing Then MsgBox "Cell in D4:F500 has be changed" End If End Sub Remember it must be added to the Sheet model not the Genaeal module One way is to right click the sheet's tab and select View Code best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Philosophaie" <Philosophaie(a)discussions.microsoft.com> wrote in message news:29A5613F-6BA9-4256-AD55-099235798EC1(a)microsoft.com... > Trying to get Wooksheet_Change to work: > > Public rng As Range > Private Sub Workbook_Open() > Dim range1 As Range > Set range1 = Sheet4.Range("D4:F500") > Set rng = range1 > > 'I need a way to access the subroutine Worksheet_Change > 'to fire the object Target range. I tried: > 'Worksheet_Change(rng) that didn't work. > > End Sub > Private Sub Worksheet_Change(ByVal Target As Range) > > End Sub
From: Chip Pearson on 24 Jan 2010 17:23 The Worksheet_Change procedure MUST be located in the code module for the worksheet whose changes you want to trap. Each worksheet whose changes need to be trapped will have its own Change event procedure in its own code module. If you have Worksheet_Change in the ThisWorkbook module, VBA does not recognize it as an event procedure and the procedure will not be called automatically. VBA sees is as just another ordinary procedure. If you want to trap changes for all sheets in the ThisWorkbook module, use the workbook's SheetChange event: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' your code here End Sub In this procedure, Sh references the worksheet on which the change occurred, and Target references the cells on Sh that were changed. This will trap changes on any sheet in the workbook (but not in other workbooks). For much more information about event procedures, see http://www.cpearson.com/excel/Events.aspx . Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Sun, 24 Jan 2010 13:13:01 -0800, Philosophaie <Philosophaie(a)discussions.microsoft.com> wrote: >Trying to get Wooksheet_Change to work: > >Public rng As Range >Private Sub Workbook_Open() > Dim range1 As Range > Set range1 = Sheet4.Range("D4:F500") > Set rng = range1 > >'I need a way to access the subroutine Worksheet_Change >'to fire the object Target range. I tried: >'Worksheet_Change(rng) that didn't work. > >End Sub >Private Sub Worksheet_Change(ByVal Target As Range) > >End Sub
From: Philosophaie on 24 Jan 2010 17:36
I am trying to set the Target from the Workbook_Open subroutine so the Worksheet_Change will fire when the cell in that range has been changed. I need a way to get the object in Workbook_Open subroutine to set the object in Worksheet_Change to initiallize Target range. |