asked on

Excel - UserForms

I have an employees details form that implements a UserForm, at the moment when you have input the data into the userform and added the details you can still go in to each worksheet and change the cell manually. Is it possible to lock all the cells that are related to the userform within each worksheet and leave all other cells avaliable for daily input?
Employee-details-REV15.xls

WJReid

Hi Carl,
Workbook attached. I have used Magic as Password, you can change by doing a search and replace in the code.
Bill
Employee-details-REV15-1-.xls

Carlandrewlewis

ASKER

Hi Bill, thanks for your time again, this has locked the whole workbook, what i would like to do is only lock the cells that relate to the UserForm in each worksheet e.g. All of 'Stevenage Employees' & 'Wales Employees' and only columns 'A', 'B' & 'C' & 'AG' in 'Holiday Entitlement' & 'Sick Leave' and columns 'A', 'B' & 'C' & 'J' in 'Weekly Payroll'.

Regards

Carl

ASKER CERTIFIED SOLUTION

WJReid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hippohood

Do some modiication in your code by inserting the lines below in the button subs. As I understand, you don't want allow users to change anything in the existing lines, but let them add new ones. This code will lock all the rows which are filled-in but unlock everything below

  With Sheet1
    Sheet1.Unprotect "111"
 
    '... put all your insertion code here
 
    Range(.Cells(1, 1), .Range("A65536").End(xlUp)).Rows.Locked = True
    Range(.Range("A65536").End(xlUp).Offset(1, 0), .Range("A65536")).Rows.Locked = False
 
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowSorting:=True, _
        AllowFiltering:=True, AllowUsingPivotTables:=True
    .EnableSelection = xlNoRestrictions
  End With

Open in new window

Carlandrewlewis

ASKER

Bill that works great but how???? Just incase i need to delete anything?

Carlandrewlewis

ASKER

Excellent, how does it works though incase i need to delete anything???

WJReid

Hi Carl,
The code protects and unprotects the sheets using the password 'Magic'. You can change the password by doing a search and replace in the code. Anytime you need to change anything, unprotect the sheet with the password and when you start operating it again, it will automatically protect.

Bill