I can look at this for you. Will come back if I have any questions.
Hi Excel experts,
I have a 4-day (96-hour) deadline on this, but really hope that someone out there might have a bit of time to spare?
10% of the fee has already been paid to Ozgrid.
Basically this is a site roster/staffing project with staffing columns that need to be updated to match any new entries made in the site columns. However, it also needs to be able to delete the appropriate columns if a user changes their mind (or is just trying out different staffing patterns) Ė and in my hands at least this is where a worksheet change event didnít seem to be enough.
Below are some notes about the attached file:
Following the 2017 dates, which are in column B (with components split out into columns A and C), there are 3 ranges that for description purposes Iíll call LOCATION, SITE and STAFF. The sizes of these ranges are fixed at the moment, but it might be necessary to add additional columns to them in the future (more locations, more sites, more staff). There will always be a blank column between the SITE and the STAFF ranges (the blank column currently being column S).
LOCATION range: There are pairs of columns with site locations (city, area etc) Ė there is no data in these right now as this will be pulled through from other workbooks (probably via an Ďupdateí button). Itís not part of the project spec, but Iím mentioning it just in case it has an impact.
STAFF range (columns T to Y): This holds data on staff availability and is where pre-booked holidays, meetings, site maintenance tasks, time reclaimed etc. will be entered here. This data does not need to be copied to any other range on the sheet.
In the hope of making it simpler to distinguish between entries originating from this range and those originating from the SITE range (see later for reason why), any site maintenance tasks entered in the STAFF range will begin with a string that is not a Site title i.e. entered as ďMaintenance: Site 1Ē not ďSite 1 MaintenanceĒ. Users should be able to enter and delete text freely from this range.
SITE range (columns N to R): When a member of staff is allocated to a site their name will be entered under the appropriate Site Name (column header) and Date (row).
- Excel should then check whether that member of staff is available (i.e. whether the corresponding cell in the STAFF range is empty)
- If the staff member is available the entry should be allowed and the site name should be copied to that staff members column in the STAFF range
- If they arenít available the user should be notified of this via a message box, and the entry should not be allowed.
- If the staff members name cannot be found in the STAFF range the user should be notified via a message box, and the entry should not be allowed.
DELETING/AMENDING ENTRIES IN EITHER THE STAFF OR THE SITE RANGE:
This is where it gets trickier. I originally tried to code the above using a very basic worksheet change/lookup combination see attached file, but then realised that if someone later changed their mind and deleted an entry, the system failed. Iíve seen posts elsewhere suggesting a hidden Ďmirrorí sheet to hold the pre-deletion values, but in the time that I have, that level of coding is way beyond me.
- Any deletions made in the SITE range should lead to the deletion of the corresponding entry in the STAFF range
- Any deletions made in the STAFF range should lead to the deletion of any corresponding entry in the SITE range, if one exists (N.B. there should only be a corresponding entry if the text begins with a Site Name)
CHECK STATUS BEFORE SAVE?
Iím not sure how feasible this final request is (and I can manage without it if necessary), or whether thereís a better alternative, but it would really help if there was a way to double-check that all entries in the SITE range have a corresponding entry in the STAFF range before saving , potentially catching any previous incidents where a user has forgotten to turn on macros?
The STAFF and SITE columns will eventually pull through to separate workbooks where any mismatches between the two ranges probably wonít be apparent until itís too late.
N.B. The staff names given are not real and neither are the site titles, so the code would probably need to refer to the header locations rather than specific strings.
All cell fill/colouring can be ignored Ė as it will applied via conditional formatting later (I just used it to try to make the sample data/logic a bit easier to visualise).
I think thatís it, but if Iíve missed anything please let me know.
Last edited by isame; December 28th, 2016 at 10:01. Reason: old code removed
I can look at this for you. Will come back if I have any questions.
Thanks, that would be great.
Question, will there be last name along with first name ?
Hi, that's a very good question. At the moment we I don't have to use a last name (as there are no two people with the same first name) BUT I'm more than happy to use a last name and I think it would make the macro more robust in the longer-term, so yes please code to use both a first name and last name.
I should have thought of that, well spotted :-)
The file is ready. Please make the payment(See PM for PayPal details) and I'll attach the workbook upon receipt of the payment.
Payment made: transaction ID 7HR06697YV572500M
Many thanks for working on this.
Got the payment and thanks. Please find attached file. Please test it and let me know if you need anymore help. If the user forgot to enable the macro, there is no mechanism which will ensure that everything is okay, unfortunately.
Hi, I'm still testing it, and in general it's looking great. One thing I did notice though (and this is probably my fault for not being 100% clear in the brief), is that the word 'travel' or similar isn't carrying over in the output (from the site range to the staff range), there are a couple of examples in the sample data I think. If there's a quick-ish way of fixing this I'd be really grateful, as I don't want to mess up your fabulous code by trying to change things myself.
I'm going to work my way through the code and try to understand it, but with my basic knowledge that may take a while. Is it okay if I come back with a one or two questions re. how it works?
Thanks once again.
Thanks for the feedback and yes, happy to answer your question.
To fix that issue, replace these section
withVB:'//else put the site name Application.EnableEvents = False Cells(Target.Row, Found.Column).Value = Trim(Cells(SiteNames.Row, Target.Column).Value) Application.EnableEvents = True
VB:'//else put the site name Dim SiteDescription As String SiteDescription = Trim(Replace(Target.Value, Found.Value, vbNullString, , , 1)) SiteDescription = Trim(Cells(SiteNames.Row, Target.Column).Value & " " & SiteDescription) Application.EnableEvents = False Cells(Target.Row, Found.Column).Value = SiteDescription ' Trim(Cells(SiteNames.Row, Target.Column).Value) Application.EnableEvents = True
There are currently 1 users browsing this thread. (0 members and 1 guests)