Can I do this?

[viper]

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 ]

[Al Chara]

What do you want to do exactly?

[viper]

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,

[Mudface]

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.

[viper]

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?

[NateO]

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 ]

[viper]

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?