Hi,

excel 2k
windows2k

I have an excel spreadsheet in which the user will click a commandbutton_click event to enter data either being copied from already existing data from another cell or entered manually. The cell, or range of cells, say: range("A1:C10"), are unlocked after the spreadsheet has been locked for all other cells. I want the cell to be locked right after the data is entered so that the user can not alter the data. Or is there a way to have the entire sheet locked and then when the user click the command button, the cell is unlocked, the data entered and then when the user presses enter, the cell is locked?

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Range("A" & i).Select
Range("A" & i).Locked = False
Range("A" & i).Value = Range("H" & i).Value
Range("A" & i).Select
Selection.Locked = True

Range("c" & i).Select
Selection.Locked = False
Range("c" & i).Value = Date
Range("c" & i).Select
Selection.Locked = True
'Range("c" & i).Locked = True
Next i
End Sub


This does not work. Any help is greatly appreciated.

what i think you need to look at is the Worksheet_Change Event
something like
HTH
Dave

Leave the cell Locked at all times. The key is to unProtect the beginning of that section of the macro, then Protect at the end. This way you don't need to have a separate Private sub.

Hi,

neither option really works. Here is the code I want to execute:

I want the sheet to be locked, but when clicking the button, then i want to be able to ulock that cell, apply the data and then lock the cell again so that it can not be touched.

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
Range("a" & i).Value = "This is a test " & i
Next i
End Sub

You don't need this in the Private code.

Rather, put this in the general Module:
Then assign this macro to the commandbutton. If you want to have it password protected, then add appropriate items.

I tried this and it works fine.

HI,

That works well if the whole sheet is to be protected. but my problem is that the user should be able to modify some cell and not others.