Independent Drop List Duplication Removal.

[KatieA]

Good Morning,

Can somebody answer if you can do this please. This is regards to independent drop down lists.

So as an example I have three drop down lists all independent from each others information.

When i get into the third list, is there any way if the same selection was made in the next row, that the third selection could only contain two of the three selections on the list say?

I have already used data validation to create my lists, so not sure how to remove duplicate selections into the process.

Maybe a VBA code if anybody knows.

[torachan]

One prepared earlier this morning ready to reply to another similar situation.
Using VBA and simple form.
torachan.

[KatieA]

Good Morning Torachan, please can I show you what I require, as this isnt exactly what I require. That is good though what you have done

[KatieA]

Attached is my spreadsheet if anybody can assist.
So how it works is I have a list of perm drivers, which isnt connected into the independent drop down lists. The infringement type, infringement name & remedial action level are used in independent drop downs. I have used indirect formula in data validation to achieve my results for the drop downs to work, they are all using information from the master data tab.
What I would like to happen is if I selected the same information on the next row below the same driver name, infringement type, infringement name, that the second level, third and fourth could only remain in the remedial action level, as the same driver has had all the same categories selected and was at level one already.

[torachan]

I will wait for someone else to respond as my approach would be entirely VBA driven.
There are many members with far better formula based skill than me, they may see a solution that I cannot.
torachan.

[KatieA]

Torachan

I would be happy with VBA code in the background if you can find a solution to my problem. Its driving me insane.

I know exactly what you are saying though with regards to other members being able to solve it.

I just need a result otherwise it wont be as good.

Thank You.

[sintek]

I would also make use of VBA and a UserForm...
No formulation in sheets as these need to be updated all the time depending length of entries...

[KatieA]

Sintek - I know it says on the excel forum that people do not do your homework for you.

However I am so baffled on this one. Please could you update my sheet, or is it too much work?

Thank you ever so much.

[sintek]

We suggest going a different route...Make use of UserForms and VBA...See Torachan sample...Excellent idea...

[KatieA]

Good Afternoon thank you. How do I start with this and can I use this approach in many rows for different drivers and selection etc, just in the remedial action.

[sintek]

I suggest contacting a Moderator to have the thread moved to VBA section...

Then explain in step by step detail what you are wanting this file for and what process you are trying to achieve...Automation of business is the way to go...Is so much more versatile than formulation...

[KatieA]

Sintek - I have sent a request like you have said. If it is allowed I will send over exactly what I want and need out of this.

I appreciate your assistance on this, my heads been battered over this, but would like to see it in full swing.

[torachan]

I have quickly overlayed your sheet on one of my previous apps just as a beginners exercise.
Only the first two comboboxes are loaded, it is an elimentary start as there are going to be possibly numerous entries of same person.
This will make the filtering/sorting possibly the most complex to code.
The sizes of data entry boxes can be altered to suit.
To keep the code condensed the textboxes and their labels are determined by the table headers and their sequence.
Only the 'CLEAR' - 'ADD' & 'CLOSE' are coded at the moment. To add a new entry first 'CLEAR' the form.
Then select the first two combos - then add a lot of garbage to the rest of the textboxes-when finished press 'ADD' to pass to sheet.
I feel it is going to be easier to 'flag' a message box preventing duplication of infringement levels rather than removing from combo-lists.
Have a play then comeback with suggestions - Rome was not built in a day, this app will possibly take longer
torachan.

[KatieA]

Torachan, this looks spot on idea
As I am at home now and can only see so much. If I could give you some more information to try for now and I will keep an eye out at work. You are an amazing help.
There are two types of infringements, EU & Working Time Directive, which is why I tried to build the drop downs. EU Infringement categories are DailyDriveTimeLimitExceeded etc in column C. The other type Working Time Directive are in column G. When these are filtered down I wanted to be able to select in the remedial action column say an EU category was selected then the information displayed in column H2:H6 to be displayed as an option as action that was required, like level 1. If the driver was to obtain the infringement again, then the first level would be not be accessable again.
P.S I will require all the same information but for a seperate tab for agency drivers to

[KatieA]

I will send you an update of the information required today for you to use. It may make more sense
I have just realised now, when I have moved and edited the columns that the information referred to the setup in the user form. Please dont me mad, this is why I have set up the layout now

[KatieA]

Sorry i forgot to mention also. I was trying to introduce on certain columns for sign off a date/time stamp so people updating and entering the records can not falsify the date when the records are entered updated.

[KatieA]

Hey torachan

I have now made some changes to the sheet for you to assist with and make some code. I hope this is much more clearer for you to understand. I cant wait to see this master piece!
I have made an agency tab also which I would like to populate. I want to be able to make changes to the name lists and different levels also just incase, which I know I should be able to do inthe Valid Data tab.
In column N in the valid data this is just for my reference at the moment of the possible stages. This sheet is to be able to have clear view for sending drivers to disciplinary if necessary hence the differentlevels.
I am also trying to add a timestamp in, which I wrote some code in mine. However I could only get to apply to one column, but I wanted it in multiple

[KatieA]

Thank you ever so much for your assistance on this

[torachan]

Just picking up all those that I am assisting at the moment.
Before proceeding any further it is important that the 'goal posts' are fixed now.
Ensure your requirement is fully laid out before coding, saves time and you do not land-up with a 'dogs breakfast'
Single sheet for your 'record database' > primary key (probably 'drivers name' in first column)> shown difference (permanent/agency) with secondary key in second column.
Where is your code for time-stamping and in which columns > how is it applied at time of entry.
When you are sure you have finalised all detail post back on this site and I will take a final look.
torachan.

[KatieA]

Torachan

I would like the main page to be called Infringement Tracker.

I have now taken on board what you have written and amended the log so it is just on one. Anything in green is for time stamping. Anything in yellow is for manual data to be entered by other colleagues (no code required.) I do not want the green and yellow to be permanent though, thank you.

I am happy with the lay out. I want to be able to add entries into the driver lists in the valid data and update the levels. Everything else is good to go
My code for timestamping is showing below. I wanted to enter it into multiple columns the ones highlighted in Green. I was trying to automate it so the date and time couldnt be falsified. I just couldnt work out how to get it working across multiple columns.

With regards to the Remedial Action, I hope that you can create code, so that if Level 1 has already been selected against the same Driver, Infringement Type/ Infringement Name, that it can only let you select the remainding Level 2, Level 3 or Level 4. I am unsure to this stage how many level each Infringement name might have, they may have more than 4 levels. I need to be able to rename each level, such as Level 1 Driver Training, Level 2 Disciplinary or even just Driver Training or Disciplinary. I cant put that final detail in there was it has to be confirmed/approved.

I have added a column also called Roadside Penalities, the data is highlighted in orange for the cost of these in "Valid Data". If a driver gets an EU Infringment called Insufficient Breaks in Driving Period - it could have a fine of £100. Let me know if you can bulid this in also. If not ill remove.


I would also like the sheet to be able to be filtered so I can see all the same driver and his records. I would just usually use a manual filter, but you may know something a hell of a lot better than me

How long will this take you, I would like it in the next couple of week if thats possible?

Please Login or Register  to view this content.

[KatieA]

Sorry attached is the spreadsheet.

[torachan]

I will see what I can do, I do not work to timetables at my advanced age, waking up each morning is a bonus.
This site normally gives hints and tips to enable all to advance their knowledge, full blown apps are a rarity.
torachan.

[KatieA]

Torachan,

I would be so thankful if you could solve this. I am now in the process of teaching myself userforms and how to set these up. I am a novice at this, but I am keen to learn. I have not found anything yet that will do my independent drop down selection yet though in a userform. I am starting from the beginning with this. I would be so keen to see how you did and look at your work and learn from it.

[KatieA]

All,

If you could send me a solution to the original post. Is there any way that you can enter a formula or quick vba to remove a selection if already chosen previously? I had set up the multiple drop down lists originally, without using vba. It would help me immensily as I am still looking for a solution if I can not get the userform created.

[torachan]

ref your post #24, I am afraid it requires someone brighter than I to come up with a formula to remove item if already used.
You then have to re-populate the dropdown ready for next (this would be an interesting formula).
So back to basics, the sheets are only used as storage medium (there are NO formulas on them) and you are ill advised to mix on-sheet formula and VBA.
Play with the UserForm until you break it - note any error codes and note any highlighted codelines.
To Add a new infringement > ClearForm > Select Permanent/Agency > Drivers Name > Agency(if applicable) > Fill details as neccessary.
As you press 'ADD' the first timestamp is added to the new entry - the shorter listboxes have a dropdown calendar on doubleclick.
To Update or Remove first Select Permanent/Agency > Drivers Name > Select appropriate record from listbox > Edit as required > update.
Final datestamp is added if there is any content in 'Ops Managers Comments' on update.
'Filtered_Driver' sheet is populated when selecting driver combobox - this is also used for data manipulation while using UserForm.
Have fun, post back on this site for any minor tweaks.
torachan.
nearly forgot, a message box will appear if a driver has the same selection of (infringement+remedy) made a second time, this occurs on the remedy selection combobox selection.
Also first row of Table1 with 'xxxxxxxxxxxxxxx' must remain in place for advanced filter to work.

[KatieA]

Torachan - do not put your self down. It takes somebody with intelligence to build such a wonderful userform. I am over the moon. People may think that is pretty sad how happy I get over excel.

I am still looking at the formula, to try and solve it. I study excel in my spare time. I am only intermediate though. Copy and pasting macros and so on.

I would just like to say this look fab already and I really appreciate the time you have spent to help me with my project. I was very suprised this morning to find this great piece of work that you have done which will help me so much.
I will have a play with the sheet. I have come across an error this morning with regards to the selecting the driver. I can select the names however not the record to update. I have attached the error I have been given.
Once this error is rectified I will use this sheet and I do not want to break it I can see this being a brilliant database.

A question with regards to the tables. I can still manipulate these cant I. What I mean is that I can add and remove driver names, and place them in alphabetical order. Any data on on the valid data form can I change if necessary. I can see all the data has been made into table. How do you change the cell references on these incase I add more data to each list. I sound like such a novice dont I

Again, thank you ever so much on this. Look forward to being able to use it.

[torachan]

The reason it fell over was there are no records in the 'Infrigement File'.
Remove the existing ListBox1 code and paste in the attached code, all I have done is added four extra lines to catch anyone clicking on an empty listbox a message will pop up.
You can add rows to any of the valid data tables merely by typing in the row below the table (thats the beauty of tables, they automatically expand) you do not have to alter any code it will find your addition on the next loading.
The UserForm is only recording infringement details, if you have not recorded (Added) an infringement against a driver there is no record to fill the listbox, the listbox is there to enable you to select the relative record in the case of the driver having multiple infringements recorded.

Please Login or Register  to view this content.

[KatieA]

Hey - I have entered the code above and seems to be working fine. I was just wondering if such a thing was possible on the remove record. I like the comments that you have added.

Is there any way that we could password protect this, so certain team members can not delete records. I am asking this, just because I am dealing with the legal side, I do not want people just to delete a record that has been put on there by myself or the OPS managers.

As I am a novice. Are all the columns on the infringement tracker set up to the userform by column references. So the question I am asking is "Must the Columns not be cutted and pasted out of sequence? I am not planning on doing this by the way but I just thought id ask.

[KatieA]

I am just familiarising myself with this. Let me know on the password

By the way how long have you been doing this for and where did you learn it?

[KatieA]

Torachan - when adding an entry. When I press enter after inputting the date into the first text box called date of infringement it shows this, please see word document attached. The add entry does not allow on first time of pressing enter.

[KatieA]

Torachan - a minor tweek. Any possibilty for the date of conversation to be automatically timestamped like the date and time of infringement logged. This would be superb. The reason I ask is that the I do not want the team managers to falsify this information or be able to change that particular function for auditing purposes

[torachan]

Password protecting Excel can easily be bypassed by those determined to, rather than use the inbuilt protection which can prove irksome.
I have placed an extra 'very hidden sheet' (Sheet5) (look in the VBA properties) the password is in cell A1 - set the sheet > visible and alter to suit then reset the sheet > very hidden, the password at present is "password" (very original)
Throughout you will see the code snippet below in several Subs, this saves writing 17 lines of code individually naming destination/source each time, the larger the form the greater the saving - downside you have to give more thought to data sheet because columns and textboxes are synced

Please Login or Register  to view this content.

How long & where did I learn - started mid 1960's with Cobol & Fortran - spreadsheets were a late comer mid 1980's with VisiCalc & Lotus123
About the same time as my first real computer (Amstrad1640 with GemBasic)thereafter progressive (three steps forward, two backwards)
Now using the forum challenges to ward off dementia.
Use the attached update, I have done this rather than patching in bits of code.
torachan.

[jindon]

When i get into the third list, is there any way if the same selection was made in the next row, that the third selection could only contain two of the three selections on the list say?

I have already used data validation to create my lists, so not sure how to remove duplicate selections into the process.

Maybe a VBA code if anybody knows.

Isn't it simply

Please Login or Register  to view this content.

[torachan]

@jindon, if it was that simple I have been reading far more into the problem than necessary.
torachan.

[KatieA]

Jindon thank you for the code, I have pasted that formula into the excel forum example above, and it didnt work. It still let me select all levels, associated with the infringement type. So Its still not solved. I have had to email a company in London to solve it. I am hoping the information that they send me will help.

If I select Andrew Abbott - EU Daily Drive Time Limit - Level 1, Level 2 Level 3 Level 4 appears.
If I am to select the same again Andrew Abbott - EU Daily Drive Time Limit - I only want Level 2, Level 3, Level 4 to appear say.

Thank You. Let me know if you have any ideas for that though.

[KatieA]

Torachan,

I have received your updated sheet which I am looking at again. However when I have downloaded and set it to the desk top. When I try and add a driver details, it will not let me "add entry" from beginning stage after pressing enter when entering the infringement date, or name, which is what I thought it would do.

I have to now press enter until the end on the first record and then it lets me clear form first and then add entry and the reminder pops up about the clearing of the form. If I then close the form and try and open it back up, the same happens again. There seems to be a problem with the add entry each time the form is opened and closed.

[KatieA]

Torachan, WOW you have many years of experience writing & programming. How long did this excel take you. If it was me it would have probably taken years lol. I am self taught excel really. Always keen to learn how it all works. I think that its amazing that you assist on the forum and aid people like myself to show and improve their knowledge. Since being on this forum I have received some brilliant assistance and it has improved my work skills to, which is a real bonus. When I have time I will look over your code and try and piece it all together. For now though I will not interfer like a typical woman would hahaha

[torachan]

Good morning KatieA,
In fairness to @jindon his code does work as per your request in post #1.
However I feel you did not describe the scope broad enough, then again my logic may have caused me to see toooo many scenarios.
Firstly I would assume because you have applied a case to the first driver then that case would also be available to be applied to another driver.
This then makes the procedure somewhat more complex as a checking routine is needed to sample all drivers records.
I have attached a file with @jindons code pasted in, it does what you asked for in post #1.
Now to look at the fault you describe in my latest code, initially I can not replicate it.
torachan.

[KatieA]

Torachan

I did explain how I wanted it to work in post 4

I have tried jindons code and it isnt exactly how I wanted it to work as I wanted it to apply to the Remedial Actions. I do appreciate him helping though as it would be interesting to see and for me to sleep at night lol.

I did explain my scenario below.

Attached is my spreadsheet if anybody can assist.
So how it works is I have a list of perm drivers, which isnt connected into the independent drop down lists. The infringement type, infringement name & remedial action level are used in independent drop downs. I have used indirect formula in data validation to achieve my results for the drop downs to work, they are all using information from the master data tab.
What I would like to happen is if I selected the same information on the next row below the same driver name, infringement type, infringement name, that the second level, third and fourth could only remain in the remedial action level, as the same driver has had all the same categories selected and was at level one already.

I did explain my scenario below. Its maybe just my first opening post wasnt very concise. I am still learning how to post.

Jindon code does not let me select the same Infringement name, which isnt what I needed, as I wanted example

Andrew Abbott - EU Daily Drive Time Limited Exceeded - Level 1, Level 2, Level 3, Level 4.
Andrew Abbott - EU Daily Drive Time Limited Exceeded - Level 2, Level 3, Level 4.
Wayne Ames - EU Daily Drive Time Limited Exceeded - Level 1, Level 2, Level 3, Level 4.
Wayne Ames - EU Daily Drive Time Limited Exceeded - Level 2, Level 3, Level 4.

[KatieA]

Torachan - It isnt picking faults at all Like i said you have been an amazing help.

[KatieA]

Torachan - I am actually trying to apply this logic just to see if I can work it out. I am planning on using your userform though.

[URL="https://bettersolutions.com/excel/data-validation/prevent-duplicates-drop-down.htm"]

[torachan]

Hi KatieA,
I have cycled through the procedures 20/30 times and they are working as intended.
From the beginning, if you want to log a new infringement firstly press 'Clear Form', (DO NOT press 'ADD'),next select option 'Permanent or Agency', next select from the 'Drivers Name ComboBox', next (select from 'Agency' combobox (if applicable)), next 'Date of Infringement' (either type in the date or double-click for calendar choice), then work your way through the rest of inputting (all date boxes have doubleclick calendar if you want it), once you have driver details and infringement date you can press 'Add Entry' and come back at anytime to update.
To Update first select driver as per previous, (DO NOT press 'Update'), the listbox will fill with all infringements applicable to this driver, select which infringement you wish to update, to save changes press 'Update'.
The cross checking of drivers previous infringements is done when you select from the 'Remedial Action Level' combobox, if this has been previously applied for the same infringement a message box will appear to inform you.
Date & Time Infringement Logged is automatically recorded on first 'Add Entry' , 'Ops Timestamp SignOff' is automatically logged if there is content in 'Ops Manager Comment' on respective update.
First draft approx four hours, although every userform differs, the framework follows similar pattern, my speed is hampered as I am a two finger typist, editing and tweaking usually takes longer than initial concept.
torachan.

[KatieA]

Hey Torachan, Thank you for the clear description above. I am really sorry I bet you think I am a ditz, I missed the first important/vital part "clear the form." I got swept away with the other trials and tribulations I asked you about.

BTW - I have thought of a way to solve my problem using that website information but it is terribly long winded and could lead to errors being made. I will wait to see if Jindon has a VBA solution so I can see this in action in my spreadsheet. I will be using yours however as its fab.

I would like to thank you for all your assistance on this Torachan, if you wouldnt mind maybe sending me your address so I can post you a little something to say thank you. If you wouldnt mind I wont close this query as of yet, and mark as solved. I will make a small example of how I worked it out and post it on here as an example that it can be done, for me though it will take weeks to solve manually.

I havent been the easiest of candidates, but have learnt to much over these past few weeks.

[torachan]

That is a very kind thought, however helping someone progress their knowledge is sufficient reward.
I would be interested if you do get a formula based solution.
A VBA solution using the Worksheet_Change event will use the same logic used in my UserForm.
I took the easier option of flagging the case by using a message box.
To remove from the validation list then repopulate would require convoluted code.
The major problem comes when you have thousands of rows of data and your program becomes so slow because with more cases the checks become exponentially greater.
I have circumvented this problem by the path I took to filter the data.
torachan.

[KatieA]

Torachan - I hope you had a fantastic weekend.

I just wanted to show you the logic/formula approach that I was discussing and how I wanted it to populate.

I could not get the information to drop down from independent selections so I had to come up with another logical way, which you will see would prob end up with 30,000 rows on data entry. Also in the drop down selection in remedial action, there would be the eqivalent minus the ones already selected. This is why I wanted to use indirect and the independent drop down idea.

On the driver logic tab, is how I built my blue sky thinking/logic. You will see all the formulas. The column highligted in yellow, is the list my drop down uses in infringement tracker and when one item is selected it will then disappear from this list. The column highlighted in red is where I have used CONCATENATE, to join up the drivers name, infringement name and level.

In the infringement tracker I have then used the formula in a data validation list in the remedial action column =OFFSET('Driver Logic'!$I$2:$I$33,0,0,COUNTA('Driver Logic'!$I$2:$I$33)-COUNTBLANK('Driver Logic'!$I$2:$I$33),1)

Please select the next level for andew abbott to see it in full force.

[KatieA]

Torachan - is there any chance you could tweek the following. Please if possible would you be able to timestamp on, Date of conversation and also the Date of Training?

[torachan]

Good morning.
Should there have been a new file attached to your post #46 ???
Will do request mods this evening, got a large lawn to mow before the bad weather breaks.
torachan.

[KatieA]

Good Morning - Opps file was to big had to change it to a ZIP.

As for gardening, I dont mind mowing lawns, I really hate weeding!! Good luck I hope you manage to get all gardening done.
P.S I have followed all your instructions and am so impressed by your work. I really liked what you did with the remedial action level. This was a good touch and is what I was aiming for. Was that hard to code, as you can see my own logic is mind blowing.

[torachan]

I have coded the timestamps - they work the same as the Ops Manager - if there is an entry in the relative comments box the entry is time stamped.
The coding for the message box was relative simple as the individual driver record has already been filtered to sheet4 therefore simple query on a small amount of data.
As you can see it uses a similar approach as yourself (concatenated string), although I only do it once, And save myself 30,000 rows of data

Please Login or Register  to view this content.

As there are now only three manually entered dates I have removed the calendar (it was code heavy in such a small app.
to enter manual dates just enter eight digits (eg 01022020 =(1st Feb) 12122020 (12th Dec)) the separators will appear in the date box on enter.
torachan.

[KatieA]

Torachan - thank you ever so much for your assistance on my userform. It has been a journey. I have learnt several things along the way. People like yourself are the reason I come to the excel forum, as you understand the trial and tribulations with excel, and have so much knowledge to share and can make my life so much easier. Once I have introduced this, I am going to have a long look at how you built this and try and piece it all together. I have something to work from that I understand how it was built to Are you sure you don't want me to send you a little something?

P.S. i liked how you built the calendar function in, it was really smart, the only reason I asked for timestamps is to ensure that nobody falsifies information. Thank you again for this.

If you know of any good websites for me to visit to help then please share below.

Did you manage to get all your gardening done, good job you did it yesterday. As I type this it is absolutely bouncing it down outside?

[KatieA]

Torachan - I will mark this as solved, and then post anything I receive back on this if possible.

As you have coded in a solution to the userform that will stop team members from selecting the same category then I would say that the job is done. As we have discussed I have realised how complicated it would be, as I would have to have approx 7200 lines of information and my drop down would be insane.

[torachan]

Wishing you well, do not hesitate to post on this forum if any probs.
The two sites I find most useful to assist my fading memory are links below.

https://www.thespreadsheetguru.com/b...t-excel-tables

https://www.snb-vba.eu/VBA_Fill_comb...istbox_en.html

[KatieA]

Torachan, I have being writting my guides for work colleagues. I dont know how hard it is to do this but in the Drivers Team Manager (follow up) could a team managers be selected and then this timestamped or a team managers name be written and then the date be applied as 01/01/2020. Also I forgot to ask how does the filtered driver sheet work? I can see data appears on there when I update a driver. Do I clear this form every now and then?

I will keep an eye out for you on here. I have added reputation to you also.

[torachan]

Is the Drivers Team Manger the same as the briefing team manager ?
if so I can change the date box for a combo > select the manager > update will timestamp (similar to others).
Filtered driver sheet is generated each time you select a driver (see ComboBox1 code) no clearance needed the sheet is self maintaining.
Post after breakfast.

[KatieA]

Torachan - yes they will be the same as the team managers list. I will add names to this as and when required etc. Thats great. Are you having anything nice for breakfast. As I start work at 06:00am -I normally have breakfast/lunch at 10:00am haha. I am on a diet you see.

[torachan]

Select the new combobox, select the briefing manager, instead of his name appearing after selection the timestamp will show and record on update.
Sorry update late, beyond my control part of the site has been down most of the day.
Full english 'brunch' keeps me going all day until dinner at 7pm.

[KatieA]

Torachan, No problem buddy. I have literally logged on at home to have a look at the code, and it is so complex. It has blown my tiny female brain. What you have done is so out of my league at the moment. I am picking up tips now, and am going to start from beginning and break it down. Thank you for the update. Is there any way in the code that the record could also show the team managers name in the infringement tracker, so it was name, date and time. Only asking

Could I also pick your brains, with regards to time stamping. I didn't know you could actually get a computer name "timestamped" into the log. I have been reading this afternoon. How hard is something like that to code?

[KatieA]

I am on Keto diet, so all day brunch would be okay for me. However if there was bread and potatoes etc, I would have to give it a miss. Not being to personal are you retired now or do you still work at all?

[torachan]

Find ComboBox9 code and change the one line code, this will put the name + timestamp in the same cell.
You can log the computer name or if you log on to your computers the individuals log can be recorded.
Not been personal at all, we are what we are, been retired 15 years now, fully recommend it, only downside getting closer to the inevitable.

Please Login or Register  to view this content.

[KatieA]

Hey Torachan - So how do you occupy your retirement days apart from helping us novices on excel . Did you manage to get round to any travelling? Its sad to think of the inevitable, but still try and grab life with two hands. When nan passed away, my grandad found a new lease of life. Hes 82, and he loves twitter and putting the world to right. He has so much drive still, i love it.

Thank you for the above code, I will paste it in.

With regards to my above question, would it be difficult for me to change my userform to have the computer name logged in stead. Only asking thats all I found the Environ VBA last night. Like you said I do not want my userform to end up like a dogs breakfast or what I call it a dogs dinner.

I am just looking at other ways I could have improved this and maybe with some help of the code and how to amend the sheet, I could paste it in just for a trial. If that makes sense. I do not want you to rewrite history. Its good that I can as these questions as it still relates to my userform.

[KatieA]

I promise I will stop pecking your head soon haha. I just enjoy being able to chat about excel with an expert. Take Care

[KatieA]

Torachan Sorry I do not have any code for combo box 9. ive looked and looked.

[torachan]

See attached, on page 12, it's in the UserForm (uf1) code.
Only just seen your posting, the site has been offline again today.

[KatieA]

Hey Torachan I may need your assistance again. So when using the form, is there any way when the information has entered into the comments box or record of conversation, that the comments can be locked and not over written.
Could there will be warning box to say when the person enters the information "are you sure that you happy with the information as this can not be overwritten." This would be the icing on the cake as they say.

[KatieA]

It would be handy on the names after they have been updated to.

[torachan]

Give it a thorough test, I have only run it a couple of times.

[KatieA]

Torachan, Thank you ever so much. Can all the information therefore be locked to, after add entry or updated? This would probably be better I didnt think it could be done ha. If you can do this then please send over it. Much appreciated. Is there anything that can be done such as a certain person to unlock the locked data incase of a major error etc, such as a password administrator type thing? Im just asking as curious to how all this works.

How have you been? Thanks for the friends add to.

[torachan]

The data is not actually locked, the code looks at the cell containing the relative data, if data has initially been written the relative comboboxes and textboxes are set .enabled=false.
You could set the record sheet to 'very hidden' and un-hide it and make a direct manual entry/deletion via a password entry on your UserForm.
Excel is not easy to protect, anyone determined enough can easily bypass any of the inbuilt protection.

[torachan]

Update, put a password box just as a demo.
The password ("password") is in cell A1 on the hidden sheet5.
On pressing 'ENTER' button if the password checks O.K. the infringement sheet flip/flops between hidden or visible.

[KatieA]

Torachan as usual this is super. So the password hides the infringement tracker sheet from being amended, from my understanding. I can not actually update the userform screen can I at all ?

I can use the password to hide the infringement tracker so no one can edit it, this would be fine. It would be great if the password then enabled me to edit the userform screen but this is not essential. I have just realised also that the only two boxes that dont becoming inactive after saving is the date of infringement and roadside penalty. Could they be made inactive.

Hope you have a fantastic weekend.

[KatieA]

Can the record removed and the Administrator password be different? Only asking

[torachan]

seperate passwords are on hidden sheet5 - to view this sheet go into the property window of the vba editor select sheet5, set visible, alter passwords to your requirement and hide sheet again.
you can still edit the infringement tracker via the userform, it does not have to be visible, with it hidden it stops anyone been able to edit it by closing the form and physically typing in direct to the cells.
infringement date and roadside fine are both now un-editable once the infringement date is logged.

[KatieA]

Good Morning, When i have tried this today, the combo boxes can still be accessed even after update.

Is there any way you can disable the combo box selection arror after the records have been updated on each record such as the following infringement name, infringement type, briefing team manager, driver team manager and remedial action.

[KatieA]

Also with regards to driver checking I wanted to ask you this.

I know that the code can check if a driver has had one type of infringement, but is there any way that we could get the code to check say if somebody recorded two infringement types in one record. So for example
Record 1 - Working Time Directive - WTD InSufficient Break - system would let me select driver training level 1
Record 2 - Working Time Directive & EU - Insufficient Break & Exceeded 4h30 minutes. - (i wouldnt want the system to be able to let me select level 1 again maybe and flag this up.)

I would want the checker to be able to realise that the person is trying to record the same infringement but as a dual record and for the system to pick this up. Hope this makes sense.

[KatieA]

So basically after each combo box or text box has been filled in then this be disabled. I want to try and cover all bases. I do like the comment box about not being able to overwrite, but I guess I need it for all sections.

[torachan]

I think I have covered each situation now, give it a workout.
torachan.

[KatieA]

Torachan - it is not letting me select a driver to start it is showing the following "no infringements on record for Andy Abbott" etc. I can not select a driver to add the infringment to

[KatieA]

Hope you are enjoying your August Bank Holiday

[torachan]

Try this

[KatieA]

Torachan - The only two boxes which are not allowing any data in once updated is the roadside penalities & the unable to action (email agency.) the unable to action will not even allow me to use it

The roadside penalities is disabled after adding the entry. Say if I was to return and update I couldnt. This is no big issue though.

Others are spot on

[torachan]

Hi.
The roadside penalties is now enabled all the time, the email agency appears to be working O.K. (this is only available if an agency driver is selected)

[KatieA]

Torachan - A Big Homer Simpson DOH!!! with regards to the email agency, i think I had Monday brain on. Could the agency box & Roadside be non be editable after update also. I know you changed Roadside but I think its best that it cant be updated also.
Thats brilliant. Thank you very much.
Can I ask you also with regards to the remedial action, if the person selects the same answer and the warning appears " Driver has already got this remedial action, do you wish to continue? If you select No it wipes all the information out that you have entered, but not updated, such as record of conversation etc. Is there any way that the information can remain what the user has entered even if they select No. If a person has selected say Briefing Team Manager and record of conversation already but not press update. What would be the best way to tackle this?
Also is there any way when this box appears that the user can not select Yes and continue? This is just a question at the minute which I may would like to happen.
I honestly dont think there should be any more queries on this, I bet I have done your head in.
Is it possible also if you write something in the text boxes that you can copy and paste it the information into another record obviously before the record is saved and a user is unable to edit. Copy and Paste sort of idea? Just a thought, I think I have covered all angles.

[KatieA]

I will look forward to hearing back from you. What are your plans for this week? There seems to be a problem with the timestamp. When you add an entry it is the english way around say today 01/09/2020, and then when you press on the record for update it shows it as being the american style 09/01/2020. Can this be rectified? Only just really noticed it. Thank you.

[torachan]

Dates always seem to be the programmers worst nightmare in VBA, there is probably more written on overcoming MS insistence on reverting to US format.
Hopefully the attached stays stable, it only affects combinations of days/months 12 and below, formatting does not always work as the ambiguous always favours the US default.
torachan.

[KatieA]

Hey Torachan seems to be pretty stable, the only slight issue is on the date of conversation, the date enters great but their is no time after this.
Also I can see that you have removed the yes or no selection from remedial action but when I had entered information in the date of conversation box (not pressed update) when i selected the remedial action and the driver already had a record so selected no, it wiped all information I typed in record of conv boxes. Can this be possibly stopped? So in a nutshell its wiped any freshly typed information, it wiped the slate clean

Just to let you know that I am practicising making a userform to run a report from our infringement tracker data. Wish me luck. I have picked up tips and code from yourself to give me a kick start. If I am stuck I will put it on the forum haha.

[torachan]

Updated.
Best of luck with your form.

[KatieA]

Torachan I will give this a whirl at some point today. As you are an expert, could you give me some guidance that I can try with my reporting. Any hints to make this fab, and then I can pinpoint where to start etc. Obviously I am going to use the data in the excel tracker sheet. I looked last night at home it is a mind field. I was thinking a dashboard of some sort. Id like some good advice as you have more experience. Look forward to hearing back from you.

[torachan]

Give me a few clues.
What are the parameters of the report/reports.
Are you wanting to just view them or are you wanting them in print format.
Map out something then I will try and guide you how to achieve it.
torachan.

[KatieA]

Good Morning,
I would like to do the following both print and view them, send as pdf or email - on a weekly basis.
Id would like to show a break down of the infringements with the roadside penalties - With a £ Total.
I would like to show repeat offenders (meaning the same driver with the same type of infringement.)
I would like to show a breakdown of agency and permanent drivers.
Frequency between logged and actioned by the briefing managers (this means how many days they took to be briefed.) How long between briefing manager to driver trainer, how long between driver trainer to ops manager.
At what stage the infringement is at with conversations/actions, like only just logged, at team manager level, at driver trainer level, ops manager level.
Is this what you mean by mapped?

[torachan]

Yes it is a start on another aspect.
Reporting can be a greater challenge with Excel, it is easier if working with a true database such as Access.
Although it is simply another mass of code to drawdown the filtered data to their own worksheet.
Not all MS packages contain Access as standard therefore resorting to Excel becomes the norm.
I will start with just transferring the filtered data in sheet format to pdf then saved to file.
Cosmetically pleasing reports can be time consuming requiring more graphically orientated layout.
Leaving auto emailing to last as you will need to link in your email destination data.

[torachan]

Just made a start on reports.
There is only one at the moment as I want all the requirements before starting in earnest.
As there could be many variations on a theme I want to minimise the amount of repetitive code.
At present all the report is a copy of the filtered page passed to a pdf for view/print.
Just select the driver on the report creator page > press build > press Save/View.
torachan.

[KatieA]

Torachan - thank you I was not expecting you to help prepare the reports but fantastic thank you. I will have a look at this today and tomorrow. I have been away on a work course so only just seen this

[KatieA]

Torachan thank you for your assistance on this. I have looked at this and will use this.