match and update vba code

[Patriot2879]

Hi hope you can help with vba code ease as i am completely stuck.

On sheet1 I have 2 comboboxes named ‘name1 and name2’, incell C10 this is where the date (dd/mm/yyyy) is inputted in this formatmanually as this needs to match what is in sheet4 which is the calendar.



I also have a comboxcalled ‘Area’ which will also need to match what is in sheet4 with the area,date and name1 once that info is found on sheet4 then name1 needs to be updatedwith name2 by clicking the update button.

 

[sijpie]

Can you try to rephrase what you need?

Do it in steps, that will make it a lot more clear, for instance:

1. In Cell C10 i enter a date
2. In combobox name1 I select a name
3. ...
...
X. Now my combobox Nam2 should be updated depending on etc

 

[Patriot2879]

Hiya thanksfor your reply back hopefully the below steps make sense ��

Sheet1 I havea……
Combobox1which has a drop down list of peoples names
Combobox2 whichhas a drop down list of Areas in the UK
In C10 cellis where you enter the date manually like dd/mm/yyyy
Combobox3 whichhas a drop down list of Subject like swift swap, change hours, lateness,sickness etc
TextBox1where it updates with the persons date of birth from combobox1
TextBox 3 whereit updates with the persons skill set
ComboBox4which has another drop down list of secondary names of people
Text Box 2 whereit updates with the persons date of birth from combobox4

In sheet2
Column Ahas a list of peoples names
Column Bhas date of birth
Column C is the skill set
Column G isthe Subject
Column I isthe areas

In sheet4

Cell A1,A19, A37, A55 is where I have an area
Cells B1, C1, D1, E1,F1, G1, H1, I1, J1, K1 i have dates from WC 13/07/2019 to 14/09/2019
Cells B19, C19, D19, E19, F19, G19, H19, I19, J19,K19 i have dates from 21/09/2019, 28/09/2019 etc
Cells B37, C37, D37, up to K37 then dates from 05/10/19 etc
Cells B55, C55, D55 up to K55 then dates again for example from01/11/2019
In B4 to K17 this is where names are to be updated under thecorrect date and area
In B22 to K35 this is where names are to be updated underthe correct date and area
In B40 to K53 this is where names are to be updated underthe correct date and area
In B58 to K71 this is where names are to be updated underthe correct date and area

 

[Patriot2879]

HI this is the file I have added to dropbox hope you can have a look, when a name is selected in 'name1' and the area and date I want this to update into sheet4, but when the name is changed in 'name2' I want this to find the name in name1 the area and date and update the name. hope this makes sense

https://www.dropbox.com/s/l0zmebx1rw3f0j4/Copy%20of%20Book1.xlsm?dl=0

 

[sijpie]

hi patriot,
been looking at the file and what you are trying to achieve. As I understand it you want to use this form to select a suitable person to swap with someone on the schedule.
Would it not be be more logical to start with the date (as a combobox), selecting the dates from the ones on the sheet4 going forward from today? then the details of the person on the schedule for that week are automatically filled. In the right form then only those names with the same skill are shown for selecting.

Alternatively you start with the name of a person on the schedule in the left form, and then only the dates where that person is on shift are selectable. The rest would be the same.

let me know if my understanding is incorrect

 

[Patriot2879]

Hi, thank you for the reply, your first option is a great idea, with the combobox for the dates etc, it has to go by area as well though , as different people work in different areas, please can you advise also how would I do the combobox wit the dates, as in sheet 4 the dates are on different lines and are in rows and not columns.