Excel VBA: How to only allow macro to make cell input

Question

I'm currently working on a worksheet, which should use different userforms to perform tasks and calculations. The userforms are called via buttons.

So all the input by the user should be made via those userforms; the actual worksheet should contain only the results which should be read-only (for the user).

Problems:

1. Protecting the worksheet will also prevent the macro from making changes.

2. The following code has the same issue as protecting the worksheet.

   Private Sub Worksheet_Change(ByVal Target As Range)
       If Not IsEmpty(Target) Then
           Target.Clear
           MsgBox "SomeAlertMSG"
       End If
   End Sub
   

Any suggestions how to accomplish this without using self-made boolean flags?

Answer 1

What you are looking for here is the UserInterfaceOnly:=True flag for protecting sheets in VBA.

Protect your sheet from VBA by the following line:

ActiveWorkbook.Sheets("YourSheet").Protect Password:="123", UserInterfaceOnly:=True

This will protect the sheet and prevent the user from editing it manually, however any macros can still change the sheet! Obviously you can choose a different password, or have it as an input so it's not baked in/visible in your code.

Documentation: https://msdn.microsoft.com/en-us/library/office/ff840611.aspx

UserInterfaceOnly - True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.