CODEWITHSUNDEEP
vbaexcelvalidation
C. Henke
C. Henke

Reputation: 161

Check if Data was entered by the User or by a macro [VBA]

In my VBA-Project I have different Buttons (Ribbon Bar with Custom UI Editor) that start Subs, which enter data into the Cells. I want to prevent the users from entering data on their own to the cells as this might destroy the routines. Is there a way to validate whether the data has been entered manually or automatically? I thought about a control sheet where I would put a variable to 1 if I am in a function and check for this variable in the worksheet_change sub. Anyhow I think this might be a risky way as a Sub can always crash and then the value is still at 1 and no values can be entered anymore.

Upvotes: 0

Views: 529

Answers (1)

Teasel
Teasel

Reputation: 1340

I suggest you simply lock the sheet from user input with the Protect function.

You have to use the UserInterfaceOnly property of this function to achieve what you're trying to do.

Sheets(“sheetname”).Protect Password:="Secret", UserInterFaceOnly:=True

This will protect your sheet from user input and let VBA change values in the sheet.

EDIT - Disable alerts

You asked in the comments if it's possible to disable alerts when the user select these cells. The following code should do the trick.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

Upvotes: 1

Related Questions