Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Worksheet Change Events; multiple instances in a single worksheet

    I have a worksheet where I keep track of certain events that I have scheduled. The worksheet covers scheduling of events in 13 locations, and allows me to schedule 11 items per day. It is easier to put all 13 locations on a single worksheet, than to create 13 separate worksheets.

    I have a worksheet-change code (courtesy of Don Wells, a big contributor to these forums) that I took from another workbook I made a couple of years ago. This is how it works: I have dates (Jan 1-Dec 31st) in col C and data in 6 adjacent columns in row 5 (eg: H5:M5). In col N5, I can enter a date and it will 'move' the data from H5:M5 to another row (eg: H27:M27)and erase the data from the 'old' date (row 5). There are 2 parts to the code and I am attaching a sample xls file to illustrate things, including the code. It works fine when I only have a single location ("Location #1).

    However, I have 13 locations, with 11 line items/day. "Location #2" has its data in columns S:X and I want to use col Y to move Location #2 data from one date (eg: Jan 4) to another date (eg: Jan 24). I copied the code for Location #1 and changed the ranges accordingly. Then, I went onto Location #3, which has its data in columns AD:AI, and I want to use col AJ to move the data for Location #3.......and so on and so on....for all 13 locations. As with Location #2, I copied the same code for Location #1 and changed the ranges to correspond with the proper ranges found in Location #3.

    Here is the problem: I have named the functions as Worksheet_Change, and you will see reference to a function called "Postpone"..........when I copy the code I am using for Location #1 and use it for Location #2 (and changing the columns appropriately), or do the same for Location #3, nothing works anymore.

    I get an error message about an ambiguous name and I think it is because the functions have the same 'name' (even though the cell ranges are different for each location and event).....my question is whether it is possible to give these worksheet change events different names? I have tried to call them Worksheet_Change1, Worksheet_Change2...or Worksheet_Change_1 and Worksheet_Change_2, but I think that putting a 1 or a 2 into the name prevents the function from running.

    If need be, I suppose I can make 13 worksheets (one for each location) and use the worksheet_change event code in each, but it is much more convenient to users to be able to have all 13 locations on a single worksheet. Any ideas, suggestions??..thank you.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi dmcnab,

    There shouldn't be any need to replicate the 'Sub Postpone(target As Range)' code for each range: simply pass it the appropriate values for the range in question. You're already doing that for the 'target', so at most it's just a matter of adding whatever extra parameters are required (I suspect maybe a column offset related to the target cell's address) or otherwise modifying the 'Sub Postpone(target As Range)' code to calculate the offsets for itself.

    PS: I think your 'Sheet1' code could be reduced to:
    Code:
    Option Explicit
    Dim EntryVal As Variant
    
    Private Sub Worksheet_SelectionChange(ByVal target As Range)
      EntryVal = ActiveCell
    End Sub
    
    Public Sub Worksheet_Activate()
      EntryVal = ActiveCell
    End Sub
    
    Private Sub Worksheet_Change(ByVal target As Range)
      If target = EntryVal Or target.Row < 12 Then Exit Sub
      EntryVal = target
      If Not Intersect(target, Range("n:n")) Is Nothing Then
        Call Postpone(target)
      End If
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi macropod, and others.....I am making some progress.....I am attaching an updated sample spreadsheet...I have amended the code on Sheet 1 and in the module....I am now able to move data in Location #1 from, say, H4:M4 to H10:M10...and, in Location #2 from, say, S5:X5 to S11:X11.....but there is still a glitch....if I move data on location 1, row 5 to another row, it also moves any data in location 2, row 5....in other words, it doesn't discriminate between location 1 or location 2, even though the 'move to' columns are different (h:m is location 1, and s:x is location 2)............any suggestions as to how I can get the VBA to run so that if I am want to move data in location 1, it limits the move to location 1...and if I ask it to move data in location 2, it limits the movement to location 2?.....thanks in advance....
    Attached Files Attached Files

  4. #4
    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
    A cursory examination of the code indicates that you move both sections since your "postpone" procedure explicitly moves both sections.

    I would think that you would want the code to move only 1 section and that section should be determined by the column of the TARGET. Loop through the cols preceeding the target instead of explictly moving each one with the MoveData routine called multiple times. You can put those lines from that routine into your code in a loop....

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve...that is one of the many things that I have tried...for example, I altered the MoveData code to read Call(MoveData(target.row("h:h"),NewDateRow("h") and so on but that didn't do it...in fact it stopped everything from working.....part of the problem for me is that I don't know that much about the different VBA functions etc...and I can't fully understand how the code that I am using defines the target row in the first place....and so, I can't figure how how to 'limit' that definition.....any explanations would really help...thanks.

  6. #6
    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
    Try these. The worksheet_change event I changed since I added a new parameter for postpone, since the 2 locations are not set up the same. Col B is 12 cols from Col N and col O is only 10 cols from Y so this needed a separate parameter.

    Thus the change event becomes:
    Code:
    Private Sub Worksheet_Change(ByVal target As Range)
      If target = EntryVal Or target.Row < 5 Then Exit Sub
      EntryVal = target
      If Not Intersect(target, Range("n:n")) Is Nothing Then
        Call Postpone(target, 12)
      End If
      If Not Intersect(target, Range("y:y")) Is Nothing Then
        Call Postpone(target, 10)
      End If
    End Sub
    As mentioned I eliminated the MoveData sub you call and built it into the procedure. Based on the call from the event handler defines the range copied and cleared...

    I've kept most of your code just adding the "generalization" of determining the columns to work with based on the target.column. Step through it and see if it does what you want...

    Steve

    Code:
    Option Explicit
    Public Sub Postpone(target As Range, iOffset As Integer)
      Const MsgTitle = "Rescheduling"
      Dim NewDateRow As Long
      Dim Test As Variant
      Dim i As Long
      Dim iTargetCol As Integer
      Dim iCol As Integer
      
      Application.EnableEvents = False
      iTargetCol = target.Column
      'Locate the first row of the requested new date
      On Error GoTo BadDate
      NewDateRow = Application.WorksheetFunction.Match(target, Range("C:C"), 0)
      On Error GoTo 0
      
      If Cells(NewDateRow, iTargetCol - iOffset) = "Closed" Then GoTo BadDate
      
      'Populate the "Rescheduled from" cell
      Cells(target.Row, iTargetCol) = Cells(target.Row, iTargetCol - iOffset + 1)
      'Locate the first available row
      For i = 0 To 10
        If Cells(NewDateRow + i, iTargetCol - 6) = "" Then
          Exit For
        End If
      Next i
      If i = 11 Then
        GoTo BadDate
      Else
        NewDateRow = NewDateRow + i
      End If
      'Move data to new row for columns 1-6 before target
      For iCol = 1 To 6
        Cells(NewDateRow, iTargetCol - iCol) = _
          Cells(target.Row, iTargetCol - iCol)
        Cells(target.Row, iTargetCol - iCol).ClearContents
      Next
      ActiveSheet.Unprotect Password:="open"
      ActiveSheet.Protect AllowFiltering:=True, AllowSorting:=True
      GoTo ExitHandler
      
    BadDate:
      On Error GoTo 0
      MsgBox """" & Format(target, "mmm d, yyyy") & """ is not a work day.", _
      vbCritical, MsgTitle
      target = ""
      
    ExitHandler:
      Application.EnableEvents = True
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....thank you for this...I am doing some testing and so far, so good....one little glitch has appeared -- you may recall that I said that I have 13 locations.....in the attached file, I have used your code and I have added a 3rd location and I removed a column or two so that the parameters are the same (ie: 10, rather than 10 for Loca #1 and 12 for Loca #2)....(once I have everything all set, I will add 10 more locations)

    Anyway, you will see location #3 on the worksheet and in the code.....here is the glitch -- each date-row has 11 slots....in col C,N, Y show the 'day' and col D, O, X show the 'date'....the glitch relates to the calling the ClearContents.....if I move data out of or into the 1st row for that day (ie: row 27 for Jan 5), it leaves the 'move to date' showing in col L, W, or AH (depending on which location I am working in).....it doesn't do this if the date is moved from or to 'slot' 2-10, but if it is the #1 slot, it doesn't clear the contents...and then when I go to the cell to delete the 'left over' data (the move to date), it runs the """ is not a work day" message.

    I thought it had something to do with the code that refers to iTargetCol as being -6, or, where it says "For iCol = 1 To 6" and I changed that to 7 but then nothing worked....any ideas why it isn't clearing the cell contents (for those #1 slots in a day) ??
    Last edited by dmcnab; 2011-04-09 at 23:03.

  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
    1) If you rework the workbook so it is always 10 columns for the offset, you can if desired, remove the parameter in the call:
    Call Postpone(target)

    And add it explicitly into Postpone:
    Public Sub Postpone(target As Range)
    ...other dims
    Dim iOffset as integer
    ...
    ...
    iOffset = 10

    But if future locations will have a variable distance, then I would leave in the parameter

    2) To clear the date add the line in red below in your code:
    Cells(target.Row, iTargetCol - iCol).ClearContents
    Next
    Cells(target.Row, iTargetCol).ClearContents
    ActiveSheet.Unprotect Password:="open"

    [This was my mistake. Your orginal had a line to clear N in the first section and Y in the second that I mistakenly deleted before I "converted" into the new scheme...]

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....so far, everything looks good, altho I now have myself in a different type of jam....I can't save the real file that I am working on b/c Excel won't let me....I have started a new thread late yesterday, called "Excel unable to save file; huge file size" trying to figure out that problem, but once I do, I am sure that the code you gave me will do a great job...I'll let you know, as I progress....thanks, again.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi again Steve...so far, so good....I was able to 'solve' the huge file size issue (seems to be due to lots of formatting and varied font sizes?)....anyway, everything else seems to be working as required....I continue to be amazed at how helpful people are on this site/forum....I only wish my own skills were stronger so that I could help as well, but I think that my answers to questions would be so basic, as to almost useless compared to the other replies...nonetheless, it is a tremendous site...

  11. #11
    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
    I only wish my own skills were stronger so that I could help as well
    Many of my skills were honed by answering questions, going thru the answers of others, trying to recreate the solutions, etc. The more you code the better you get at it.

    Note I did not change much of your "logic". I only generalized the code. Look at line by line what you did and what I changed it and make sure you understand the differences and the logic to why one way over another (though be careful, there is not just one "right way", there are many possible routes to a solution).

    I also advise stepping through the code line by line (make sure screen updating is not turned off) and see line by line what happens to the code. This can help understand the flow of a routine.

    Doing some simple routines can also help. Use the recorder and do some simple tasks. Then look at the code created, it can help to learn the VBA model of excel.

    Good Luck
    Steve

Posting Permissions

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