Prevent manual user input but not Macro

Hi, new to the forum, looking forward to learning some great stuff.

I have a spreadsheet where I need to stop manual user input into some cells, but still allow a macro to fill the cells, is that possible?
Thank you.

Lock the cells needed.(default i believe) and unlock the ones user can enter data.

Set a password on the sheet.
Let the macro unlock, then write data.
it its a formula, lock that cell too.

Public Const kPASS = "bob"

Sub MyMacro1()
    ActiveSheet.Unprotect Password:=kPASS
      Range("C2").Value = Range("A2").Value / Range("b2").Value
    ActiveSheet.Protect Password:=kPASS, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub

Sorry, I would need more instruction than that. I know how to go in and edit modules at a beginner level, but where would I enter that information? There are currently 4 modules in the spreadsheet now. I was not the person that created this spreadsheet.

If you need that level of help then it's probably easier if you attach a desensitized version of your workbook and explain exactly what it is you need.

BSB

I want users to open the spreadsheet without a password, be able to manually input into some cells and not other. I want to force the user to use the macro to insert their data into certain cells.

I just unlocked all cells, then made sure only the cells I want protecting were locked. Then I protected the sheet with a password. That worked in that, I could no longer manually enter data into those cells, but this didn't allow the macro to input those cells either.

Hi and welcome.

Yes it is indeed possible. You can protect the worksheet and set the "UserInterfaceOnly" setting to True. That way the VBA can interact with the sheet but the user cannot.

See the link below for more information on its usage
http://www.cpearson.com/excel/Protection.aspx

BSB

Okay, thanks for your help.

Because you need to protect the sheet, in VBA, with the UserInterfaceOnly set to True. Just protecting the sheet via the user interface stops the macros being able to make changes too.

Something like this (adapt sheet names to suit) in your ThisWorkbook module will do the trick.

Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

BSB

That did the trick, thank you so much!!

Happy to help

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB

Will do, but one more quick question. How do I get it to run that macro automatically when the file is open? Currently, I'm having to manually run it.

It's a "workbook open event", therefore so long as it's in the workbook module (not a standard, class or sheet level module) then it will run when you open the workbook (so long as macros are enabled!)

BSB

Here's what it looks like. I entered your code in module 5
Capture.PNG

Look a few lines up from there. See where it says "ThisWorkbook" just below "Sheet5 (Drop Downs)" and just above "Modules"?
Cut the code from Module 5, double click where it says "ThisWorkbook" and paste it in there. That way it will fire whenever you open the workbook.

BSB

Fantastic, thank you for your patience with the beginner newbie.

No problem at all. We all started somewhere

BSB