PDA

View Full Version : Excel cell protection help



GorCh
24-08-2009, 02:47 PM
Hi there

I am trying to protect a set of cells in excel, so that they cannot be altered. I can do this in itself. The problem is that I need to run a macro across the workbook that references the cell.

When I attempt to do this however, I get a visual basic runtime error message (see screenshot for message).

The sheet is for my old school hostel. It outputs tables of travel arrangements for the manager and bursar for long weekends. I get an email asking for help every few months, and it usually turns out to be because the cell in question has been deleted.

I know there are probably better methods than excel, but I understand excel and it works fine- when it works. :)

So anyway, any ideas how to lock the cells in this particular way?

Cheers,
GorCh

Speedy Gonzales
24-08-2009, 02:56 PM
Wheres the screenshot?

GorCh
24-08-2009, 03:02 PM
Whoops there it is

nofam
24-08-2009, 03:26 PM
Within your macro you can use


ActiveSheet.Unprotect "your protect password here"

'rest of your code

ActiveSheet.Protect "your protect password here"

GorCh
24-08-2009, 03:44 PM
Ok that looks good. Is there a way to unprotect all sheets in a workbook a similar way? Cheers

nofam
24-08-2009, 04:01 PM
I don't know of a way to actually protect/unprotect a whole workbook (perhaps Andrew93 will be around?) but the following works.

Usual caveat applies regarding loops; if you had 40 sheets in your workbook then things could get slow, but it's quite nippy with a couple of sheet.


Option Explicit

Sub Protect_Workbook()

Dim work_sheet As Worksheet
Dim Pwd As String

For Each work_sheet In Worksheets
work_sheet.Protect Password:=Pwd
Next work_sheet

End Sub

Sub Unprotect_Workbook()

Dim work_sheet As Worksheet
Dim Pwd As String

On Error Resume Next
For Each work_sheet In Worksheets
work_sheet.Unprotect Password:=Pwd
Next work_sheet
If Err <> 0 Then

End If
On Error GoTo 0

End Sub

GorCh
24-08-2009, 04:50 PM
Sorry for not understanding completely nofam, but my programming experience is limited. Do I need to change any of the variables in that code? For instance specify what the password is?

Cheers

GorCh
24-08-2009, 11:35 PM
OK so decided to just dig in and start playing around. Thanks a lot for the help nofam.

I ended up creating two new modules in the documents VBA section, one containing the 'lock' code, the other the 'unlock'.

Then I just added a line calling each module to the beginning and ending of each of the other macros. I didn't need to change anything from how it was presented to me on this thread (there was no password - locking was just to stop a wrong cell being deleted).

My only experience with coding before is with MATLAB stuff for uni. I had originally created the macros by recording the mouse movements etc, and just cutting out the unnecessary bits in the VBA editor. Feels good to have (kind of) learnt something new!

Cheers again fro all the help,

GorCh