Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Can I do this?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am wanting to call a macro on sheet 1 from the change_event on sheet 6. Can this be done? I've been having a terrible time getting my workbook to sort the way I want so I've decided to go at it differently and I think if I can call my macro from sheet 6's change_event it will work.

    Thanks,


    _________________
    Soon I hope to be answering alot of these questions instead of asking them.

    viper

    [ This Message was edited by: viper on 2002-03-18 15:58 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you want to do exactly?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On sheet 6 in columns A and B I enter names of employees and in column C I enter their salary.

    On Sheet 1 columns A and B the names are pulled from sheet 6 and the same with salary. Then on sheet 1 in columns D:J I enter the hours they work each day.

    What I'm trying to accomplish is when I need to hire or terminate an employee I can add or remove a name from sheet 6 and still keep it alphabetized.

    I have the code to alphabetize the names on sheet 6 and this works great, I can add a name at the bottom of my list and when I hit enter the macro actomatically alphabetizes the names.
    My problem however, is that when these names are alphabetized on sheet 6 (which in turn keeps them alphabetized on all sheets) if I add a name or remove a name in the middle of a week and the names are alphabetized the hours I have for each person on sheet 1 (D:J) doesn't stay with name.

    So, I figure I will just use sheet 6 as employee data only(names and salary) and alphabetize sheet one, that way everything associated with columns A:C will stay with A:C and not columns A:C alphabetize but columns D:J stay.

    So what I'd like to do is when I add or remove a name on sheet 6 call my macro to alphabetize sheet 1, sheet 2, sheet 3, sheet 4, and sheet 5.

    I hope I explained it okay, it's really hard to explain what I want to do with out you looking at my setup.

    Thanks,

    I appreciate the help from everyone at Mr. Excel.

    viper

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In answer to your original question, as long as your macro isn't defined as a private sub, you can call it from wherever you like.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right,

    But what I'm wanting to do is call the macro to alphabetize sheet 1 through sheet 5 when cells on sheet 6 change values.

    I'm not sure how to do this?

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right click on worksheet 6, click view code. Paste:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:b1000")) Is Nothing Then
    application.run("Viper's Macro Name")
    end if
    end sub

    You may want to change your target cells. Me thinks you can call private subs with this procedure.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-03-18 17:35 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, new code works great. Can a formula in a cell change the way it sorts? Everything works fine but the sorting. I can call the macro, it will activate on the cells changing but the sorting does not stay the same on each worksheet.

    On sheet 6 the sorting works as I want. Top to bottom(all empty cells at the bottom) but on the other worksheets the empty cells are at the top then the names alphabetized. Example: sheet 6 range A5:C10 only two names in this range. Both names alphabetized in A5,A6 then empty cells, on the other sheets same range but empty cells at top in cells A5,A6,A7,A8 then the names in A9,A10. The only difference is the range on sheets 1-5 contain an ='sheet6'!A6 in each cell. Can this effect the sorting?
    I appreciate the help from everyone at Mr. Excel.

    viper

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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