Restrict MS Excel cell input by Command Button only

May 18, 2015 at 00:05:50
Specs: Windows 7
How can I restrict Excel cell input by only Command button which I have created.
Basically what I'm trying to avoid manual entry and allow data input from command button only to a range of cells.

See More: Restrict MS Excel cell input by Command Button only

Report •


#1
May 18, 2015 at 02:42:09
Hi,

When you say you want to allow input from a button, do you mean that you want the button to actually enter the data into a cell, so for example, pressing commandbutton1 will enter the text "Hello" in to range("A1") ?

What you will have to do is

1) Select all the selects and lock them
2) Protect the worksheet

Modify the below code to achieve what you want

Private Sub CommandButton1_Click()

    Sheet1.Unprotect "Password"
        
        Sheet1.Range("A1") = "Test"
    
    Sheet1.Protect "Password"
    
End Sub

What this does is, first unprotects the worksheet with your specified password, then protects it again once its entered the value you want.

is this what you were looking for?


Report •

#2
May 18, 2015 at 03:56:09
No. This code however let me insert value upon a button click but what I actually want is prevention of manual entry. I wish to have a excel workbook where data to certain no. of cells can only be entered through this button

Although this code enabled protection of sheet post data entry,theeby enabling no manual change but the cell A1 could be manually entered before running the VBA program, which is what exactly I want to restrict.

Appreciate your help :)


Report •

#3
May 19, 2015 at 04:34:01
It is not clear to me what you are trying to accomplish.

When you say "data can only be entered through this button" what exactly to you mean?

Do you mean that the user can enter data into certain cells only after the button is clicked or do you mean you want the button to present an InputBox so that the user can enter the data there?

Perhaps you could provide a step by step description of what you are looking for.

e.g.

1 - User opens workbook, all manual entry is restricted
2 - User clicks button, manual entry into (specific range) is allowed
3 - etc.

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


Report •

Related Solutions

#4
May 26, 2015 at 03:45:56
Firstly very sorry guys for being ir-responsive from long, there were certain technical glitches which refrained me from submitting a follow up thread.

DerbyDad,
There are certain cells say A1:A10, I want to enter data into these cells only by means of a command button which will give current time stamp (I have created that, luckily) . The issue here is when an operator moves to any one of these cells, with the availability of keyboard he can enter data manually means he can enter this timestamp of his desired value which may actually differ from current timestamp.

What I want to do is restrict him from manually entering this data and allow any value to be input in these cells by means of this command button only.

I can use sheet protection but this won't serve the purpose as it won't allow data entry by any means.

The Step wise description as requested could be this:
1- User opens the workbook, certain cells are locked
2- User moves to cell A2 (say)
3- User type down time value in cell A2 but system shows up an error message or don't take up entry at all
4- User hit command button with cell A2 selected. He gets current Timestamp and moves to other data cells.


Report •

#5
May 26, 2015 at 05:08:38
I have 2 requests:

1 - Can you tell us if this requirement is related to the Virtual Keyboard request in your other thread?

2 - Can you please post the Command Button code that you are using so that we have a clearer picture of what you are doing?

Your comment that "Sheet Protection won't allow data entry by any means" is not entirely correct, but we need to know what the Command Button is doing before we can proceed.

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

message edited by DerbyDad03


Report •

#6
May 26, 2015 at 06:27:47
1. Yes, both the requirements are in the same project but target cells for both are different.
2. Here is the code as requested, please note after every entry I'm locking up the sheet by this code so as to prevent manual manipulation

Sub CommandButton1_Click()
Dim emptyRow As Long
ActiveSheet.Unprotect
ActiveCell.Select
ActiveCell.Value = Time
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

This allows me to enter current time to any selected cell.

For further info, 1- Every such cell where I require this timestamp is set as unlocked
2- once upon entering value via command button the sheet goes into protected mode
3- Now when I move on to next cell where I require next timestamp,(since its unlocked) I can enter using my keyboard itself which I want to restrict and permit only data entry via command button.

Coming to that comment of mine, by that I specifically meant it won't allow data entry neither manually nor by means of this Command Button.

Hope this helps.
Thanks for you time, really appreciate this !!

message edited by Vipul07


Report •

#7
May 26, 2015 at 08:19:30
First, a posting tip:

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.


Report •


Ask Question