Marc76
02-15-2019, 06:08 AM
Hello,
please apollogize my bad english, it's not my first language. Hope I won't be misunderstood.
Here's my problem.
I made a workbook for reservations of different rooms.
InThis workbook has for each month a worksheet.
In the column A are the differents rooms and below every room the time settings as 0800-0900 and 0900-1000.
The problem is, that we have a room with a capacity of 83 places.
This room can be splittet in a room of 51 places and one of 32 places.
There are a few users who are able to enter reservations of the rooms an they paste datas by ctrl C and ctrl V. To solve the problem by pasting conditional formattings I found a VBA code.
Now I'm looking for a code that protects cells. (data validation is no option)
So in column A are the names of rooms and the time settings. B-AF are the days 1., 2., 3. etc.
The code should do this:
By leaving the cell B10 (0800-1000 of the room with 83 places) after entering a text, the cell B17 (0800-1000 of the room with 51 places) an d the cell B23 (0800-1000 of the room with 32 places) should be protected that no entry or even selection of this cell is possible and unprotected if the value is deleted.
Even the opposite way. By leaving the cell B17, the cell B10 should be protected, the cell B23 is still open.
You can imagine the macros I'would have to write, but I need one that I will modify.
B10 -> B17 & B23
B11 -> B18 & B24
B12 -> B19 & B25
etc
Then B17 -> B10
B18 -> B11
etc
B23 -> B10
B24 -> B11
an this for each column: B-AF
A problem that I wasn't able to solve, is that I got a "debug" message when ther is more than one time the code "Private Sub Worksheet_Change(ByVal Target As Range)"
Maybe it would be possible to find a solution that the specific macro only runs when one of these cells changed theyr value.
Thank you very much for all solutions.
Best regards Marc
(Greetings from Luxembourg)
please apollogize my bad english, it's not my first language. Hope I won't be misunderstood.
Here's my problem.
I made a workbook for reservations of different rooms.
InThis workbook has for each month a worksheet.
In the column A are the differents rooms and below every room the time settings as 0800-0900 and 0900-1000.
The problem is, that we have a room with a capacity of 83 places.
This room can be splittet in a room of 51 places and one of 32 places.
There are a few users who are able to enter reservations of the rooms an they paste datas by ctrl C and ctrl V. To solve the problem by pasting conditional formattings I found a VBA code.
Now I'm looking for a code that protects cells. (data validation is no option)
So in column A are the names of rooms and the time settings. B-AF are the days 1., 2., 3. etc.
The code should do this:
By leaving the cell B10 (0800-1000 of the room with 83 places) after entering a text, the cell B17 (0800-1000 of the room with 51 places) an d the cell B23 (0800-1000 of the room with 32 places) should be protected that no entry or even selection of this cell is possible and unprotected if the value is deleted.
Even the opposite way. By leaving the cell B17, the cell B10 should be protected, the cell B23 is still open.
You can imagine the macros I'would have to write, but I need one that I will modify.
B10 -> B17 & B23
B11 -> B18 & B24
B12 -> B19 & B25
etc
Then B17 -> B10
B18 -> B11
etc
B23 -> B10
B24 -> B11
an this for each column: B-AF
A problem that I wasn't able to solve, is that I got a "debug" message when ther is more than one time the code "Private Sub Worksheet_Change(ByVal Target As Range)"
Maybe it would be possible to find a solution that the specific macro only runs when one of these cells changed theyr value.
Thank you very much for all solutions.
Best regards Marc
(Greetings from Luxembourg)