Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Consolidating ... (WinNT4, XL97)

    I am working on a workbook with some 40 odd sheets, each of whcih has a SelectionChange and an Activate sub (excluding 'summary' and 'index'). These subs are identical on each sheet, but because there were only half a dozen sheets before the users got hold of it, updating each individually was not a problem. Now it is.

    What I'm trying to do is move the code from the individual sheets to a separate module, then refer to this code from each sheet - that way I can make updates to the code without having to copy it to 39 other locations each time - but I can't get it to work! The original code on each sheet was like this:

    Private Sub Worksheet_Activate()
    ActiveSheet.Range("A1").Select
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.AddressLocal = "$I$1" Then
    Worksheets("Index").Activate
    ActiveSheet.Range("B2").Activate
    End If
    If Target.AddressLocal = "$H$1" Then
    Worksheets("Summary").Activate
    Worksheets(2).SumAct
    ActiveSheet.Range("A1").Activate
    End If
    End Sub

    The trouble is I can't seem to get the 'Target' variable to work across the sheet/module!! I tried a simple move of the subs, rename at the module and replace in the sheet with the new name, but every time I try I get a selection of errors depending on what I've tried - 'variable not defined', 'not optional', 'object not defined', I think 'you name it, I've had it' (error messages, that is).

    What should I be doing, please?!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consolidating ... (WinNT4, XL97)

    Create the following procedure in a standard module (the kind you create by selecting Insert | Module):

    Public Sub HandleSelectionChange(ByVal Target As Range)
    If Target.AddressLocal = "$I$1" Then
    Worksheets("Index").Activate
    ActiveSheet.Range("B2").Activate
    End If
    If Target.AddressLocal = "$H$1" Then
    Worksheets("Summary").Activate
    Worksheets(2).SumAct
    ActiveSheet.Range("A1").Activate
    End If
    End Sub

    In the code modules for the individual sheets, change the event procedure to

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    HandleSelectionChange Target
    End Sub

    As you see, the Target argument is passed to the procedure in the standard module. This procedure is Public so that it can be called from other modules.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Consolidating ... (WinNT4, XL97)

    How come you make it seem so easy, Hans?! But just so I understand (rather than copy your code verbatim, which I must admit I'm tempted to do!) ...

    Why is the ByVal an 'excel.range' in the sheet and a 'range' in the module? Apart from that, I had tried your method and my version didn't work, so it must be important!

    Thanks again
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Consolidating ... (WinNT4, XL97)

    I'm afraid it isn't relevant, both As Range and As Excel.Range (should) work, since they are equivalent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •