Macro to delete entry in Sheet 2

[abjac]

Hi. I have this nearly finish project. I can make an entry i can search and find entries.
The only thing i miss, is to be able to delete a entry.
My calendar is in sheet2, which is protected with code 1111.
I can from sheet1 make an entry in the calendar.
I can also from sheet 1 search for a name, and it will come up and show all the entries in
sheet3.
What i finally miss is to be able to delete an entry. I imagine that when i have searched the name and i see the entries in Sheet3. i select the cells to delete and press a button
in sheet 3 to delete this entry in sheet 2.
It should be possible. In sheet 3 i have all dates upon the entries and its the same in sheet 2.

Sheet 2 is runned by a Private Sub Worksheet_Change code. Its to put color on the entries.
Its also code protected with code 1111.
When i want to delete the entry i guess the code should be inside this. It disable the Worksheet_Change even and also take the protection away.
After it take all back to normal.

Please have a look at below testsheet. Hope some can help with this final thing.

Sincerely
Abjac

Code to put new code inside.

Please Login or Register  to view this content.

[HaHoBe]

Hi, abjac,

I have some questions about regarding my understanding of the problem.

I got that you would like an InputBox to enter the name of the person to search for (Iīd preferred an UserForm with a ComboBox or Listbox and a button for that) and you placed an example for the first week in Sheet3. What about the rest of the year? Do you want to consider the whole year or only a given part of it (if so: which part - half a year, a quarter) or only the first week of absence?

If you want to show the whole year you would have to tell me about the placing of the segments, i.e. directly underneath each other (Date/Absence/Date/Absence)? Do you only want to delete or amend as well?

Ciao,
Holger

[abjac]

Hi Holger.
When i make the entry i can choose what ever date i want all year. So when i search for a person, in this case his name is Employe2. It will in sheet 3 show all the entries all year out, what ever date it is. I have in the below sheet make other entries for Employe2. So it cover more than one entry. So you can see how the layout is. I only want the possibility to check a persons entry and lets say its a mistake, i want to be able to delete that entry.
It could be made different maybe, and i have had doubt, how to make it possible to delete the entry. But falled back on this solution.
You can infact try it all in the sheet. Make a entry or more for one selected Employe, and after search for this employe it will be displayed in Sheet 3 then. Just remember to manually delete Sheet 3 before a new search(will make that).
So in the sheet below you can see more entries.
I am open for other suggestions to make this fore sure. Could be deleted with a combobox, instead of the entry in sheet 3.
So dont hold yourself back Hope this answer your questions good enough.

Just read your questions. I have a inputbox coming up, when you press button check or delete entries in calendar. Then you search for that name, and all entries for this person will be displayed in Sheet 3

Sincerely
Abjac

[HaHoBe]

Hi, abjac,

this is sort of a double thread as you would need to write the contents from Sheet2 to Sheet3 and only after that start deleting possible entries from Sheet3 on Sheet2.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

HTH,
Holger

[abjac]

Hi Holger. Thanks for the codes. I tried your code but cant make it work. The first code find the entry for one person fine. But when i have to delete a entry, i cant make it work. Nothing is running. I dont know if i do it correctly. I made 2 entries for Employe2. I run your macro and it finds the 2 entries perfect.
They are now shown in sheet 3. Then i want to run the other code. I mark the cell or cells with the entry i want to delete in Sheet 3, but nothing seems to be running.
I guess it should work like that. That i can select the cells in sheet 3 i want to delete and it delete the same cells in sheet 2.
Please have a look, when you have time. Or maybe send my test back.
Thanks for now
Sincerely
Abjac

[HaHoBe]

Hi, abjac,

but nothing seems to be running.

Maybe I should have been a bit more specific on the limitations for deleting entries but they are all listed in the code:

Please Login or Register  to view this content.

The code will ony run if the active Sheet is Sheet3 and one single cell with any reason of absence is marked in Columns F to K in any fourth row starting with Row 11 (11, 15, 19 etc.). Only then the contents of that cell will be deleted from Sheet2 (and after that from Sheet3 as well). I havenīt thought about how to develop a macro that would allow to delete multiple entries from Sheet2 since this one isnīt doing what it is supposed to do (according to your post).

HTH,
Holger

[abjac]

Hi Holger Now i understand it. I think i tried all but maybe i all time selected more than one cell and i couldnt get it working. The code works brilliant sorry for my missing knowledge, how to use it. It works really great. Could you give it a try to make it with multiple cells? That would really be so great to have that working.
Thanks for the explanation, and sorry for not understand how to use the code.

Sincerely
Abjac

[HaHoBe]

Hi, abjac,

the code for multiple deletion is based on the code for deleting one single entry: the check for the worksheet and the name to search for are still made before any looping through the selected cells is done.

Please Login or Register  to view this content.

Ciao,
Holger

[abjac]

Hi Holger. Just checked the code and again again wow. Its just working so perfect. I am really impressed. Your a code master for sure. Thank you very very much, its a great result you have helped me with, so i am really greatfull.
You dont have any limits for your skills here, i am just so impressed.

Thanks allot
Have a nice day

Sincerly
Abjac