automatical protect data after it has been entered

[johnpdavey]

i have a simple spreadsheet and two questions
the spreadsheet has dates along the top row and time periods in column A.
the intention is that staff can book a piece of equipment in advance by entering their initials and the code for the equipment in the appropriate date/time cell.
my questions are;
1) how do i drag dates across the top row, starting with 28/10/02 in cell A2 and only get dates for weekdays, not the weekends?
2) how can i set up the sheet so that when someone has entered a request then that cell is automaticaly locked so that no one else can overwrite it and take the equpment, and i can change things if ammendments need to be made?
the spreadsheet will be accessible to 30+ staff so needs to be fairly robust.
thanks in advance for your help
john

 

[Paul B]

John, don’t know about the first question but for the second one try this

'Automatically Protecting After Input
'unlock all cells in the range first
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

Set MyRange = Intersect(Range("A1:A100"), Target)
If Not MyRange Is Nothing Then
Sheets("Sheet1").Unprotect password:="123"
MyRange.Locked = True
Sheets("Sheet1").Protect password:="123"
End If
End Sub

 

[PaddyD]

re the first Q - use a formula, not the standard fill. something like

=WORKDAY(a1,1)

there is another optional argument for holidays.

paddy

 

[Yogi Anand]

or

You can do Edit|Fill|Series:in_rows step-Value:1 type:Date Date_Unit: Weekday -- provide the date for the Stop_value, and click OK.

Regards!
Yogi Anand

 

[BigC]

Just in case you're not aware of this, MS-Outlook (if you have it) has the functionality to do exactly what you're after. Outlook calls this activity "booking resources" (each resource [piece of equipment] can be set up as a defacto user with whom the other users effectively make an appointment.) It is relatively easy to set up & "control" of the resources can be assigned to a "real" person who can make decisions if a conflict arises.

HTH

 

[johnpdavey]

thanks for the help guys.
i will look into the outlook option, couldnt get it to run on my machine at home but will try at work
the macro for locking the cell after input looks interesting but i am not familiar with macros, its an area i have yet to explore. where do i put it, do i just record a macro and type in the details that have been posted or do i need to do something else?
once again thank you for the help
john