Forum Home
Press F1
 
Thread ID: 102558 2009-08-24 03:47:00 Excel cell protection help GorCh (13021) Press F1
Post ID Timestamp Content User
803749 2009-08-24 03:47:00 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 		GorCh (13021)
803750 2009-08-24 03:56:00 Wheres the screenshot? Speedy Gonzales (78)
803751 2009-08-24 04:02:00 Whoops there it is GorCh (13021)
803752 2009-08-24 04:26:00 Within your macro you can use


ActiveSheet.Unprotect "your protect password here"

'rest of your code

ActiveSheet.Protect "your protect password here" 		nofam (9009)
803753 2009-08-24 04:44:00 Ok that looks good. Is there a way to unprotect all sheets in a workbook a similar way? Cheers GorCh (13021)
803754 2009-08-24 05:01:00 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 		nofam (9009)
803755 2009-08-24 05:50:00 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 (13021)
803756 2009-08-24 12:35:00 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 		GorCh (13021)
1