Karl_mark
asked on
Unlock Range in Excel using VBA
I've been tasked with setting up a spreadsheet which will be distributed to HR Managers across our business which will produce an output that will be used at board level to develop the ongoing HR strategy. To this end, I need to create a spreadsheet which allows simple data entry as I make the assumption that the HR community is non-technical meaning that I need to protect as much of the spreadsheet as possible. My main area of expertise is in Access (using VBA), so I'm having a bit of difficulty with a few things in Excel. A key problem is how to protect a worksheet, but allow data entry into certain cells. In particular, I would like to have a range which can have data entered, but the range itself needs to be able to grow. For example, the range contains five columns (the number of columns is static) which will contain data like name, Job Title, Line Manager etc. The number of rows is dynamic and will grow depending on how many staff the relevant department contains. Obviously I can set up a spreadsheet using basic Excel worksheet protection and allowing the editing of ranges. However, this gives me no control over resizing the range which can be edited. I've tried the following code, but I always get a type mismatch on the .Protection line:
I've also tried declaring a worksheet object instead of using the sheets collection, but this has the same error. I also realise that the code above will not grow the range, but I don't want to run before I can walk, so I'm starting with what should be the easiest problem to solve...
Public Sub sProtectSheets(blnProtected As Boolean)
Dim intSheet As Integer
If blnProtected Then
intLastSheet = ActiveWorkbook.Worksheets.Count - 1
For intSheet = intFirstSheet To intLastSheet
If Sheets(intSheet).Name = "Talent Spotlight" Then
Sheets(intSheet).Unprotect (mPassword)
Sheets(intSheet).Protection.AllowEditRanges.Add Title:="TSDataEntry", _
Range:=("A27:G27"), Password:=mPassword
Sheets(intSheet).Protect
End If
Next
End If
End Sub
I've also tried declaring a worksheet object instead of using the sheets collection, but this has the same error. I also realise that the code above will not grow the range, but I don't want to run before I can walk, so I'm starting with what should be the easiest problem to solve...
Sorry - typo:
Sheets(intSheet).Protection.AllowEditRanges.Add Title:="TSDataEntry", _
Range:=Sheets(intSheet).Range("A27:G27"), Password:=mPassword
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks rorya. Referencing the sheets in the range makes sense. Only problem is I now get an "Application-defined or Object-Defined error" when it reaches that line in the code. Is this because it is trying to create a range which already exists once the code has executed the first time?
I suspect so. You might try deleting that range, or adding it using a different name as a test, or simply delete it before you add it again.
ASKER
Very useful formula royhsiao. Presumably I just increase the COUNTA range should I need to go past row 180... Clever and fairly simple!
ASKER
rorya:
OK, I altered the code so that it checks to see if the named range already exists in the workbook; if it does, then it is deleted before recreating the range (I haven't added in the resizing part yet, want to get this bit working first). I still get the application/object defined error message though when it reaches the Sheets(intSheet).Protectio n... line. I can't see anything which might throw this error as the code looks fine to me. Any ideas? I've pasted all the code in question below (it's in the ThisWorkbook Object):
OK, I altered the code so that it checks to see if the named range already exists in the workbook; if it does, then it is deleted before recreating the range (I haven't added in the resizing part yet, want to get this bit working first). I still get the application/object defined error message though when it reaches the Sheets(intSheet).Protectio
Public Sub Workbook_Open()
sProtectSheets (True)
End Sub
Public Sub sProtectSheets(blnProtected As Boolean)
Dim intSheet As Integer
If blnProtected Then
intLastSheet = ActiveWorkbook.Worksheets.Count - 1
For intSheet = intFirstSheet To intLastSheet
If Sheets(intSheet).Name = "Talent Spotlight" Then
Sheets(intSheet).Unprotect (mPassword)
If NamedRangeExists("TSDataEntry") Then
ActiveWorkbook.Names("TSDataEntry").Delete
End If
Sheets(intSheet).Protection.AllowEditRanges.Add Title:="TSDataEntry", _
Range:=Sheets(intSheet).Range("A27:G27"), Password:=mPassword
Sheets(intSheet).Protect
End If
Next
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. Works perfectly now!
Open in new window