Restrict MS Excel Cell Input By Command Button Only

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!

Second, before we address your time stamp issue, I would like to offer a few comments on the Command Button code.

1 – You have a Dim statement for a variable named emptyRow, yet you never use the variable in the code. I don’t see the need for the Dim statement.

2 – There is no need to Select the ActiveCell. VBA can work directly on the ActiveCell.

3 – You are Protecting the sheet with the code, but you are not setting a Password. That means that any user could simply Unprotect the sheet and do whatever they like, not only to the sheet but also to the code itself.

If I were to use a CommandButton for this task (which I probably wouldn’t) I might do it like this:

A – Select the entire Sheet and Unlock all Cells
B – Select and Lock any specific cells where you do not want to allow manual entry, e.g. A1:A10
C – Protect the Sheet with a Password.

Once that is done, the users can still enter data in the Unlocked cells, but they won’t be able to enter data the Locked cells, e.g. A1:A10. They also won’t be able to Unprotect the sheet since it is Password protected.

You could then use the following code, but there is one more key point to be aware of:

Unless you Protect the code and Hide it within the VBA editor, the users will be able to see the Password and also alter the code. You must set the VBAProject Properties to “Lock for viewing” and apply a Password to the code or you are defeating the entire purpose of protecting the sheet.

As I said earlier, if I were to use a CommandButton for this task, my code would probably look something like this:

Sub CommandButton1_Click()
    ActiveSheet.Unprotect Password:="myPassword"
        ActiveCell.Value = Time
    ActiveSheet.Protect Password:="myPassword"
End Sub

With all that said, you don’t need to use a Command Button just to insert a Time Stamp.

If you want to restrict users from manually entering data in a specific set of cells and automatically enter a time stamp into a selected cell, you could do it with the Selection_Change event.

A – Select the entire Sheet and Unlock all Cells
B – Select and Lock any specific cells where you want the timestamp(s), e.g. A1:A10
C – Protect the Sheet with a Password.
D – Right Click the Sheet tab for the sheet where you want these time stamps and paste in the following code.

When the user clicks in any single cell within the Range A1:A10, the current time will be placed in the cell, but no other entries will be allowed. If they click in any other cell, manual entry will be permitted since those cells are Unlocked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
  If Selection.CountLarge = 1 Then
   If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'Insert time stamp in Selected cell
    ActiveSheet.Unprotect Password:="myPassword"
     Target = Time
    ActiveSheet.Protect Password:="myPassword"
   End If
  End If
End Sub

If you want to further restrict user action once a time stamp has been set for a given cell, you can use something like this which will prevent the user from changing the time stamp once the code has set it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
  If Selection.CountLarge = 1 Then
   If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
'If cell contains data, do not allow changes
     If Target <> "" Then
      MsgBox "Time Stamp Already Set" & vbCrLf & vbCrLf & _
             "User Changes Not Allowed"
       Exit Sub
     End If
'If cell is empty, Insert time stamp
        ActiveSheet.Unprotect Password:="myPassword"
         Target = Time
        ActiveSheet.Protect Password:="myPassword"
   End If
  End If
End Sub

Another option to consider is to let the VBA code enter the time stamp in Column A once some other cell has had data entered into it. That would eliminate any need for the user to select a time stamp cell. For example, you could use the following code to insert a time stamp in Column A in the same Row as data was entered into B1:F10.

Private Sub Worksheet_Change(ByVal Target As Range)
'Ensure only one cell within A1:A10 is Selected
  If Selection.CountLarge = 1 Then
   If Not Intersect(Target, Range("B1:F10")) Is Nothing Then
'Insert time stamp in Column A of Target Row
    ActiveSheet.Unprotect Password:="myPassword"
     Range("A" & Target.Row) = Time
    ActiveSheet.Protect Password:="myPassword"
   End If
  End If
End Sub

As a reminder, you must Hide and Protect the VBA code or the users will be able to Unprotect the sheet as well as alter the code.

Click Here Before Posting Data or VBA Code —> How To Post Data or Code.