automatical protect data after it has been entered

johnpdavey

Board Regular
Joined
May 30, 2002
Messages
88
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
575
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Well-known Member
Joined
Aug 4, 2002
Messages
851
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

Board Regular
Joined
May 30, 2002
Messages
88
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top