Making a cell protected after some1 else has added data

skarun

New Member
Joined
Oct 27, 2006
Messages
1
I have created a booking system for some IT suites and want to let other users book a room and then stop anyone overwriting it unless they contact me if they have made a mistake so I can then unlock it again. Is this possible? I know how to lock/ unlock cells and protect them but is there any code so I can do this?

The process would be
1) User Finds room available (blank cell) so they can type in it
2) Book room by user typing into cell their intials
3) Pop up message comes up asking for confirmation of booking
4) cell locks so no one else can overwrite booking (unless admin does it)

Thanks in advance!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello skarun, welcome to the board.
I've had to make some assumptions for this example so you'll need to amend it to suit.
If you have any problems doing that just post back with the details and we can get it fixed right up for you.

First thing is to unlock all the cells - either in the entire worksheet or just the columns you want the users to make entries in.
Then you'll want to protect the sheet.

The assumptions made are:
1) The list of room numbers already exists. (In this example, in column A.)
2) The customer names get entered into column B
3) The user's initials get entered into column C

This code goes in the sheet module for the sheet of interest.
(Right click the sheet tab, choose View code. Paste the code into the white area that is the sheet code module. Press Alt+Q to get back to the sheet.)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

'CHANGE ALL REFERENCES TO Column A TO THE COLUMN CONTAINING THE ROOM NUMBERS
'CHANGE ALL REFERENCES TO Column B TO THE COLUMN THE USER ENTERS THE CUSTOMER'S NAME
'CHANGE ALL REFERENCES TO Column C TO THE COLUMN THE USER ENTERS THEIR INITIALS

If Not Intersect(Target, Columns("C")) Is Nothing Then
  If MsgBox("Confirm room " & Cells(Target.Row, "A").Value & " for " & _
    Cells(Target.Row, "B").Value & "?", vbYesNo, "ROOM CONFIRMATION") = vbYes Then
    With ActiveSheet
      .Unprotect "Your Password Here"
      .Cells(Target.Row, "B").Locked = True
      .Cells(Target.Row, "C").Locked = True
      .Protect "Your Password Here"
    End With
  End If
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top