Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unlock Range in Excel using VBA

Posted on 2011-03-07
9
Medium Priority
?
1,002 Views
Last Modified: 2012-05-11
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:

 
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

Open in new window


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...
0
Comment
Question by:Karl_mark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35057793
You need to specify the sheet for the allow range too:
        Sheets(intSheet).Protection.AllowEditRanges.Add Title:="TSDataEntry", _
        Sheets(intSheet).Range:=("A27:G27"), Password:=mPassword

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35057806
Sorry - typo:
Sheets(intSheet).Protection.AllowEditRanges.Add Title:="TSDataEntry", _
        Range:=Sheets(intSheet).Range("A27:G27"), Password:=mPassword

Open in new window

0
 
LVL 6

Assisted Solution

by:royhsiao
royhsiao earned 1000 total points
ID: 35058144
To create a dynamic range you will need to use the following function: Address, Row, column and counta, Offest.

The best way to understand how to put them together is using the following example.
1) put the following function in cell(1,1)
=ADDRESS(ROW($A$2),COLUMN($A$2),,,"Sheet1")&":"&ADDRESS(ROW(OFFSET($A$2,COUNTA(A3:A180),0)),COLUMN($B$2))

2) put the following value in range ("A2:B6")
Loan      Amt
1      $100
2      $200
3      $300
4      $400

3) if you did it correctly, you should see the following in cells (1,1). You could use VBA to reference that cell to resize the range.
Sheet1!$A$2:$B$6

Best,

Roy
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Karl_mark
ID: 35058423
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35058447
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.
0
 

Author Comment

by:Karl_mark
ID: 35058474
Very useful formula royhsiao. Presumably I just increase the COUNTA range should I need to go past row 180... Clever and fairly simple!
0
 

Author Comment

by:Karl_mark
ID: 35058806
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).Protection... 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):

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

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35058909
I meant that you need to delete it from the AllowEditRanges collection actually:
Sheets(intSheet).Protection.AllowEditRanges("TSDataEntry").Delete

Open in new window

(untested)
0
 

Author Closing Comment

by:Karl_mark
ID: 35070711
Thanks guys. Works perfectly now!
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question