Macro Not Working Right... Can someone help?

[belly0fdesire]

I created this beautiful spreadsheet to manage time for my office, for which someone on here helped me make a macro. It's in the code for the sheet entitled "Summary". The user is supposed to make changes in the name or department on the Summary sheet and the row is supposed to change color according to the department. Also, the sheet is supposed to re-sort by department and then by name. Also, the sheet for every month is supposed to sort accordingly.

I thought the sheet was sorting okay, but now that I have data entered (time) in the individual month sheets, when I make a change on Summary and the name moves on the list when it is automatically sorted, the time that goes with that name remains in the same place and is not sorted. I have uploaded a copy of the spreadsheet here... if anyone could just take a look at it and let me know what's wrong with it, that would be great. Thank you.


If link above does not work try this one

 

[belly0fdesire]

Thank you so much, but it still isn't working right. But I feel much closer than I was... and ALOT better now that someone has tried to help.... Those Macros ( 1 and 2) that I had were just crap macros that had nothing to do with the spreadsheet and should have been removed.

I am going to try to work with this code you've given me to get it working correctly. I changed the syntax at the bottom of the script to add a space between your "Exitsub"... that's the only change I made before trying it, but it looks like all it does when I make a change is unprotect and protect the sheets.

 

[Zack Barresse]

No, take the space back out please.

Also, what exactly isn't working? Seemed to work for me..

 

[belly0fdesire]

hmm..... i'm hoping that wink meant you were j/k....



When I make a change, for instance if I change the DeVantier to HeVantier, which is what I did to test it, it sorts the Summary sheet, and unprotects all the sheets, but does nothing else. The other sheets are not sorted, nor are any of the sheets re-protected. It continues to say Calculate in the status bar. When I make another change, like changing it back to DeVantier, nothing happens. It's as tho it gets stuck in the middle of the macro or something.


You kick so much butt for helpin' me btw...



Does it have a problem with a macro in one sheet telling another sheet to sort?

 

[Zack Barresse]

No problem with telling another sheet to sort, but it cannot sort if the sheet is protected. And by the wink I wasn't joking, sorry. I should have named "ExitSub" something else as it's a little confusing. It's basically a jump point for earlier in the code to skip the main portion if criteria is not met.

Try stepping through your code. Now with a change event it gets a little more difficult to step through the code because you cannot initilize it as you would a normal (standard) procedure. To do this, open up your Summary code sheet and select the first line (cannot be a Dim line) and press F9 to toggle a Breakmark. It should turn this line red. You can toggle this off/on by clicking where the red dot appears on the left column.

Go back to your sheet and make your change. It should fire the change event and bring up the Visual Basic Editor at the specified breakmark. Step through your code with the F8 key. See exactly which line(s) of code are running and if it stops anywhere. Post back if it stops on a line or you get any errors.

 

[belly0fdesire]

Alright... this is great and very exciting...

I changed the "Exit Sub" back to "ExitSub" and put a Breakmark in at the first line of code, then made a change and when I F8'd through, it stopped me at:

If Target.Column <> 6 Or Target.Count > 1 Then Exit Sub

where it instantly occured to me that maybe this should be:

If Target.Column <> 6 Or Target.Count > 1 Then GoTo ExitSub

So I changed it and.... It seems to be working... as far as the VBA code goes anyway.... it IS sorting...

The workbook still isn't acting the way I want it to, but I believe it has to do with the way I have the formula's in the cells set up... I'm going to play with it and try to get it figured out the best I can and post later on with my success story or with another question.

Thank you so very much for you help with this... you've taught me so very much with just the help you've provided. It is greatly appreciated.

 

[belly0fdesire]

Okay....

My problem now is that I want all the "month sheets" to update with whatever changes are made on the Summary sheet. So if I add a name it will add the name to all the sheets. So when I first made this spreadsheet I used:

=IF(ISBLANK(Summary!B6),"",Summary!B6)

on all the sheets for the names column. This will not work with the method I'm using to sort because once the change on the summary sheet is made, and the Summary sheet is sorted, the changes are instantly reflected on each of the month sheets and the sorting does nothing. Consquently, all of the data (time) entered for a particular name is now associated with the wrong name.

If I copy and paste just the values into this column and change the macro to sort by column B of the sheet instead (the names), it works fine. However without that formula, I do not know a way for all the changes (added/changed names) to show on the month sheets.

Does this make sense to you?

Do you have any ideas of a method that would work to accomplish my goal?


You have the best, most helpful attitude that I've encountered on any forum btw...

 

[belly0fdesire]

Basically, what I need is for any change made in 'Summary!B6:B120' to be reflected in 'Jan:Dec!B7:B121' and any change in 'Summary!F6:F120' to be reflected in 'Jan:Dec!M7:M121' sheets and then to have all the sheets sorted by Column B. The sorting part is happening just fine, but I need another method, other than the one I am using, to get the changes made in Summary to be reflected in Jan:Dec.

Also, the Protect sheet macros are working, but I'm searching for a way to protect using the macro, but allow for autofilter.


Any help that anyone can give me with these issues would be very helpful and I would be forever indebt to you.

 

[Zack Barresse]

Depending on which version of Excel you are using, you can't use AutoFilter on a protected sheet. Now in versions 2002 (XP) and up, this has been taken into account and you can specify if somebody can use AutoFilter or not when the sheet is protected. Now in your case, if you're talking about Sort, this is the same case as well. If regular protection is used (no other arguments used) these features will bomb your code.

It's not looking like I will have much time this week (work is very busy) to look specifically at your other issues, but I'll try to take a peek one of these evenings, unless somebody else gets more time.