Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108263

Re: Change_Event macro dismiss table1 copy reinstate Table1

Show all headers | View raw

Howard,

Nothing to add to Claus' suggestion, but FWIW...

I suggest you put the 'task' part of your code in a standard module and 
just call it from the event. You can pass any refs it needs from the 
event itself. This approach keeps your event code maintenance minimal, 
and keeps your 'task' code central so it can be used by all sheets.

I see you're using the Workbook_SheetChange event so it fires whenever 
any sheet change occurs. This may not always be desireable when the 
process run is dependant on conditional criteria specific to some 
sheets and not others.

Example:
In the change event behind several sheets...
  Option Explicit

  Private Sub WorkSheet_Change(ByVal Target As Range)
    Call MySub(Target)
  End Sub

In a standard module...
  Option Explicit

  Sub MySub(Rng As Range, Optional Wks As Worksheet)
    'In case of sheet event, Wks can be omitted
    'In case of workbook_sheet event, this may be required
    If Wks Is Nothing Then Set Wks = ActiveSheet

    'task code follows

  End Sub

..now you have a universal procedure that can be called from a sheet or 
workbook module as you do here. The call from Workbook code would be...

  Private Sub Workbook_SheetChange(ByVal Sh As Object, _
                                   ByVal Target As Range)
    If InStr(gsDetailShts, Sh.Name) > 0 Then Call MySub(Target, Sh)
    If InStr(gsOtherShts, Sh.Name) > 0 Then Call MyOtherSub(Target, Sh)
  End Sub

..where gsDetailShts/gsOtherShts are global scope constants containing 
a delimited list of sheets that use the called task procedures. These 
lists could also be stored in a global variable that you load from a 
hidden sheet in a project where users add worksheets as needed. 
(Assumes your project has a built-in mechanism for adding new sheets) 
Normally, the global variables get loaded at startup and updated when 
sheets are added/deleted.

This may seem a bit too complex for your current project, but adopting 
good 'structured programming' habits goes a long way towards building 
more robust solutions very quickly!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Change_Event macro dismiss table1 copy reinstate Table1 "L. Howard" <lhkittle@comcast.net> - 2015-11-28 03:11 -0800
  Re: Change_Event macro dismiss table1 copy reinstate Table1 Claus Busch <claus_busch@t-online.de> - 2015-11-28 13:20 +0100
    Re: Change_Event macro dismiss table1 copy reinstate Table1 "L. Howard" <lhkittle@comcast.net> - 2015-11-28 05:18 -0800
      Re: Change_Event macro dismiss table1 copy reinstate Table1 Claus Busch <claus_busch@t-online.de> - 2015-11-28 14:26 +0100
        Re: Change_Event macro dismiss table1 copy reinstate Table1 "L. Howard" <lhkittle@comcast.net> - 2015-11-28 06:07 -0800
          Re: Change_Event macro dismiss table1 copy reinstate Table1 "L. Howard" <lhkittle@comcast.net> - 2015-11-28 06:14 -0800
  Re: Change_Event macro dismiss table1 copy reinstate Table1 GS <gs@v.invalid> - 2015-11-28 11:25 -0500
    Re: Change_Event macro dismiss table1 copy reinstate Table1 "L. Howard" <lhkittle@comcast.net> - 2015-11-28 14:23 -0800
      Re: Change_Event macro dismiss table1 copy reinstate Table1 GS <gs@v.invalid> - 2015-11-28 17:26 -0500

csiph-web