Solved: Protect worksheet and allow input in some cells

[coliervile]

I'm banging my on this vba and can't seem to get it to do what I want. I have posted this on mrexcel three days ago, but haven't found an answer to resolve what I want it to do.

In worksheet1 is an employee schedule and on worksheet2 is an hourly spreader for the date, based off of worksheet1, in cell A1 on worksheet2. Worksheet2 takes the shifts of the employee from sheet1 on the date entered in cell A1 of sheet2 and transposes them into cells B3:B25. Then a macro runs "TheSelectCase1" and inputs the "Scheduled" start and end times fopr the shifts located in cell B3:B25 and then updates the Gantt chart.

The way the Worksheet_Change event runs is when a new date is entered in cell A1 and the enter button is depressed the worksheet is unprotected all of the information is tranposed and updated and then the worksheet is once again protected. What I want worksheet2 to do is the exact same thing, but I want cells E3:F25 to stay unlocked,like cell A1, and allow the user to update the "Actual" start and stop times. The reason I need this flexibility is that the Scheduled times are used to design the schedule, but employees have the flexibility of coming in early or late as much as an hour and also leaving early at the end of their shifts.

I hope that this makes since and thank for any ideas or suggestions.

Regards
Charlie

[coliervile]

Here's the file...sorry
Charlie

[coliervile]

I may have attached the wrong folder, but here's the newest version...the password is "PASSWORD".

[mdmackillop]

Hi Charlie, I'm getting a circular reference problem on Sheet3 O4

[coliervile]

md, I removed the circular data and here's a copy of the file.

Thanks
Charlie

[coliervile]

I guarantee this one has no errors...accept my apologies.

Regards
Charlie

[mdmackillop]

I think you just won the prize for the most convoluted If statement I've ever seen!

[Aussiebear]

Hi Charlie,

Can I ask a question? All of the range B27: O36 on Sheet 1 contain a formula based on Sumproduct(Isnumber(Find("Value", Range)) +0), except cell B30 which has a formula Sumproduct(Exact((A1 : D10,"d")+0).

When I replace the "A1" argument with "A3" it removes the error message, but I was wondering why the need for a different formula?

Ted

[mdmackillop]

Hi Charlie
As far as I can see you only need to remove the locked property of the cell.
You can simplify the If statement by using Offset and Match. The macro is not really required here. A Vlookup will simplify things and is easier to maintain.
I added some code to allow times to be entered without the fiddly colon, eg 0615
See the Revised sheet.

[johnske]

...What I want worksheet2 to do is the exact same thing, but I want cells E3:F25 to stay unlocked,like cell A1, and allow the user to update the "Actual" start and stop times...

Hi Charlie
As far as I can see you only need to remove the locked property of the cell...

You could do this programmatically, but you only need to do it once, so you could do easily it manually: Select E3 to F25, right-click and select 'Format Cells', click 'Protection', uncheck 'Locked', and click 'OK'.

The next time you protect the sheet you can now make entries in E3:F25 but the rest of the sheet will be protected

[coliervile]

Sorry for all of those who are offering your help. This excel file is on my work computer and I was out for the evening when you submitted your ideas.

Aussiebear thanks for your comment. I was trying different formula for summing up the letters that are case sensitive (i.e. "E" and "e"), taken care of now though.

"md" your comments are always welcomed and humorous at time "I think you just won the prize for the most convoluted If statement I've ever seen! " ". What can I say I'm new at this and my knowledge pool is much more shallow than yours and most of the people that help on this site. I will take a look at what you sent me.

"johnske" I will look at your idea as well.

Please submit any ideas that you see that would simplify this or streamline it.

Best regards to all,
Charlie
</IMG>

[coliervile]

"md" as I said before my knowledge pool is ankle deep where your's is ocean deep. Your "revised sheet does get rid of all of the IF's and makes the whole a better product.

Two questions: 1- Is it possible to sort the "revised" worksheet A3:F25 descending from the earliest "Scheduled" time to the latest time?
2- Can E3:F25 be reset or clear when a new date is entered in cell A1?

I did come across one items on the "revised" worksheet- when I changed the date in cell A1 and put a time in the "Actual" start and end times it always came up as 00:00 rather than 15:30 that I typed in.

Regards

Charlie

[mdmackillop]

Hi Charlie,
One problem I foresee is that the manually changed data cannot be simply stored in this format, and will be lost when the date is changed on sheet 2. You could look at creating a table of dates where the manual changes are copied upon any changes, this data could then be recovered for previous dates etc.

[coliervile]

"md" You can forget about this part of my last message "I did come across one items on the "revised" worksheet- when I changed the date in cell A1 and put a time in the "Actual" start and end times it always came up as 00:00 rather than 15:30 that I typed in." I opened form the website orginally, but after downloading the file it works.

Charlie

[mdmackillop]

Updated version.
There was a minor bug if an error occurred then EnableEvents was not reset to true, which would result in the 00:00 time. Hopefully this is sorted. The Sort requested should run when the date is changed.

[coliervile]

"md" it works great, I truly appreciate your help with this.

Regards
Charlie
</IMG>