Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    shared workbook irritation (xl2ksr1)

    I have a modest 735kB workbook. One sheet has a list, which will grow. A new row is created at row 3 (the first row of data in the list, below a row of labels) by triggering a point'n'shoot macro which copies some lookup formulas from the now-previous row 3 so that when new data is added some cells fill in automagically.
    Another macro triggers a sort by 3 criteria of the now-new list.
    This all works fine.
    HOWEVER, when the workbook is made 'shared', as I need it to be, the effect of triggering the 'sort records' macro is this:
    Cut row 2 (the labels) and paste at end of list
    Put the new row where it should be, in the body of the list.
    This is unsatisfactory.
    Very.
    Ideas?
    More inf available for the devoted.
    Thanks

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

    Re: shared workbook irritation (xl2ksr1)

    Sharing a workbook is always problematic, you should avoid it. If you really need multi-user capability, use a database application (Access for example).

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shared workbook irritation (xl2ksr1)

    Could you show us the sort macro?

    My guess is that you either haven't specified the "Header" argument to the sort method, or have it set to "xlGuess" and Excel Guesses wrong. Set it to xlYes.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shared workbook irritation (xl2ksr1)

    Thanks, both, haven't acknowledged sooner as the email alert was trapped by Council spam filter!
    The macro is
    sort Macro
    ' Macro recorded 21/06/2005 by John Rose
    '

    '

    Application.ScreenUpdating = False
    Range("A2:G254").Select
    Selection.sort Key1:=Range("E3"), Order1:=xlAscending, Key2:=Range("G3") _
    , Order2:=xlAscending, Key3:=Range("C3"), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=3, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A3").Select
    Application.ScreenUpdating = True
    End Sub

    so I'll change the Guess as suggested.
    Can't use Access, I fear, as grateful employer won't provide it.

    John Rose Asst Recycling Officer Birmingham City Council

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shared workbook irritation (xl2ksr1)

    And top marks to Pieterse for mind-reading bugs in Excel, or me, or both.

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

    Re: shared workbook irritation (xl2ksr1)

    Congratulations on solving John's problem! I'm curious - why would the code produce different results depending on whether the workbook is shared or not?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: shared workbook irritation (xl2ksr1)

    It *might* have nothing to do with the state the workbook is in, more with the exact data on row 2 of the sorting range causing Excel to guess wrong. Or maybe with the last setting the user has used from the UI. Dunno really.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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