Can I do this?

viper

Active Member
Joined
Feb 15, 2002
Messages
382
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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,
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-18 17:35
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top