Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet change event (2003 SP3)

    Good afternoon

    I have 12 worksheets and all have the same code for a worksheet change event, if I need to change anything I have to go through an change all 12, I am guessing that all I can add a line at the start of the code to ensure that it applies to all of the sheets so that I need only change one. Because of my inexperience I am loathe to dive in and change it without first checking here but I guess something like

    set Worksheets as ws ("sheet1","sheet2" etc.)

    Is that something like it?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Worksheet change event (2003 SP3)

    You can write code in the Workbook_SheetChange event procedure in the ThisWorkbook module instead of writing 12 separate Worksheet_Change procedures. This will run in all worksheets.

    Or you can write a procedure in a standard module and call it from each of the Worksheet_Change event procedures.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    Hi Hans

    Thanks for the response, I am as usual being a bit of a dumbo and can't get my head around it

    1. I have deleted all of the code that I have put in the 12 worksheets that are the same
    2. I have placed the code into the Workbook_SheetChange event

    My piece of code starts as follows

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    For Each cel In Range("B5:AQ104").Cells

    Can you please tell me how I now tell it to only apply to the specific sheets (sheet2 and then sheet4:sheet14) that I need it for

    Sorry to be a pain

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    Since you aren't using the code on all of the sheets, you can't use it in the workbook module. You'll have to use HansV's second suggestion of writing "a procedure in a standard module and call it from each of the Worksheet_Change event procedures".

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet change event (2003 SP3)

    How about something like:

    <pre>Option Explicit
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cel As Range
    Dim sArray As Variant
    Dim x As Integer
    sArray = Array("Sheet2", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", _
    "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14")
    x = 0
    On Error Resume Next
    x = Application.WorksheetFunction.Match(Sh.Name, sArray, 0)
    On Error GoTo 0
    If x <> 0 Then
    For Each cel In Sh.Range("B5:AQ104").Cells
    'Your code here
    Next
    End If
    End Sub</pre>


    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    Hi Steve

    Thanks very much for your response and my apologies for my tawdry response but I was 'internetless' for Friday and the weekend

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    >my apologies for my tawdry response.....

    What's so low and mean (a.k.a. tawdry) about your response? Or do you mean that it was late (a.k.a tardy)? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Gre

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Worksheet change event (2003 SP3)

    I presumed that since he was "internetless" that there was much more time available for doing other activities (eg tawdry things with the fairer sex) and thus he was tardy with his response. Due to the reason for the tardy response, he considered it a "tawdry response": a tardy response due to his own tawdrieness... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    Oops <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I off course blame it on being 'internetless' and as an afterthought on the MS Thesaurus which shows tawdry as below

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Worksheet change event (2003 SP3)

    Fortunately, you are on course again... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet change event (2003 SP3)

    Hi Hans

    No comment, as my late old dad used to say, "when you reach the bottom son, stop digging"

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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