Temporarily unprotect cell then protect afterwards
 

is it possbile to lock down every cell in a sheet then when a code is run
temporarily unlock a specific cell allow it to be modified/let a value be
entered in it, then lock the cell again so the user cant type over it?

Temporarily unprotect cell then protect afterwards
 

The best way to do this is to use the UserInterfaceOnly parameter
of the Protect method. This allows VBA to modify anything, but
locks out the user. Unfortunately, this property is not saved
with the workbook when the workbook is closed, so you'll need to
protect it when the workbook opens. E.g., in the ThisWorkbook
module, use code like

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="abc",
userinterfaceonly:=True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Reggie" wrote in message
...
is it possbile to lock down every cell in a sheet then when a
code is run
temporarily unlock a specific cell allow it to be modified/let
a value be
entered in it, then lock the cell again so the user cant type
over it?

Temporarily unprotect cell then protect afterwards
 

Select the cells you want to Lock or Unlock using menu
Format--Cells...--Protection. Then use menu Tools--Protection--Protect
Sheet to "Lock out" user entry. In your macro you will have to unprotect
then reprotect the sheet to make changes.

Sheets("Sheet1").Unprotect

(Code here to make changes to cells)

Sheets("Sheet1").Protect _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowInsertingRows:=True

(Options as necessary, etc.)

"Reggie" wrote:

is it possbile to lock down every cell in a sheet then when a code is run
temporarily unlock a specific cell allow it to be modified/let a value be
entered in it, then lock the cell again so the user cant type over it?

Temporarily unprotect cell then protect afterwards
 

Much nicer

"Chip Pearson" wrote:

The best way to do this is to use the UserInterfaceOnly parameter
of the Protect method. This allows VBA to modify anything, but
locks out the user. Unfortunately, this property is not saved
with the workbook when the workbook is closed, so you'll need to
protect it when the workbook opens. E.g., in the ThisWorkbook
module, use code like

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="abc",
userinterfaceonly:=True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Reggie" wrote in message
...
is it possbile to lock down every cell in a sheet then when a
code is run
temporarily unlock a specific cell allow it to be modified/let
a value be
entered in it, then lock the cell again so the user cant type
over it?

Temporarily unprotect cell then protect afterwards
 

i have only one problem with the code you submitted below...is there anyway
to get the password automatically? what i mean is in the code
"Worksheets("Sheet1").Protect Password:="abc", " where the abc = password, is
there anyway to get this password filled in automatically..Because we want
to be able to change the password without going into the code..

"Chip Pearson" wrote:

The best way to do this is to use the UserInterfaceOnly parameter
of the Protect method. This allows VBA to modify anything, but
locks out the user. Unfortunately, this property is not saved
with the workbook when the workbook is closed, so you'll need to
protect it when the workbook opens. E.g., in the ThisWorkbook
module, use code like

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="abc",
userinterfaceonly:=True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Reggie" wrote in message
...
is it possbile to lock down every cell in a sheet then when a
code is run
temporarily unlock a specific cell allow it to be modified/let
a value be
entered in it, then lock the cell again so the user cant type
over it?