PDA

View Full Version : [SOLVED] Locked cells - values from macro



krishnak
08-28-2010, 10:16 AM
Hi All,

In a worksheet I have input data cells which are filled in by the user. In the same worksheet, I have columns containing cells with the output data calculated from the input data. The calculation and populating the output data is done by vba code by pressing a button on the worksheet.

I want to lock the cells in the output columns so that no one can overwrite them. However when I lock these cells, even the macro is not allowed to populate these cells when I press the button.

Is there any way out?

HaHoBe
08-28-2010, 12:06 PM
Hi, Krisnaak,

set the protection on opening the workbook or on activating the sheet by using
ActiveSheet.Protect UserInterfaceOnly:=True to allow macro code to work on the sheet. Pelase mind that you need to do this all the time the workbook is opened as this setting will not be saved.

Ciao,
Holger

lenze
08-28-2010, 12:07 PM
Two ways
You can UnProtect/Protect the sheet in your code, or you can use UserInterFaceOnly

Sub Foo()
Activesheet.UnProtect "Password"
'your code
ActiveSheet.Protect "Password"
End Sub
Or, in the ThisWorkBook module

Private Sub WorkBook_Open()
Sheets(sYourSheet").ProTect "Password", UserInterFaceOnly = True
End Sub
The reason it goes here is that this property is not retained by Excel when the file is closed and needs to be reset each time the file is opened!!
HTH
lenze

krishnak
08-30-2010, 01:03 PM
Thanks for the suggestions. Let me try and come back to you.