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

x
?
Solved

Programatically securing individual rows of data in Excel 2007 spreadsheet

Posted on 2013-01-24
3
Medium Priority
?
446 Views
Last Modified: 2013-01-24
I’ve received an odd request at work and I’m not sure how to even begin going about attacking it.  The user I’m trying to help has downloaded what is essentially a flat file then the adventure begins.
The following steps are what has been requested.  I’ve asked them to consider different options, but this is what they want and I’ve been unable to move them off of this specific request:
1)      Insert two blank rows between each row of data
2)      Format the blank rows where the font is italics and the color is red when inputs are added
3)      Protect the original rows (do not allow edits), but do allow inputs in the “new” blank rows that are input.  

The worksheet does not have to be password protected, only set up to not allow changes to the “original” data.

I’ve found this handy tidbit of code to insert the two blank rows, and then modified it to handle the formatting of the new columns, but that is as far as my skills with excel will allow me to go.

Sub Insert_2_Rows()
Dim i, nRows, firstRow
Application.ScreenUpdating = False
nRows = 2
firstRow = 1
i = firstRow + 1
While Not IsEmpty(Cells(i, "A"))
Rows(i & ":" & i + nRows - 1).Insert
Rows(i & ":" & i + nRows - 1).Select
    Selection.Font.Italic = True
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With

i = i + nRows + firstRow
Wend
End Sub

The assistance I need is with Step 3.  I tried protecting the sheet first, then running the macro to input the blank rows, but it gave me a 400 error.  I guess I’m looking for a way to protect the sheet programmatically, then to go in and “unlock” the rows where there is a blank value in column A (or vice versa, to lock only rows where column A is not blank).

FWIW, I’m working in Excel 2007.

Any help is obviously appreciated on this very unique request.
0
Comment
Question by:UTSWPeds
[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
3 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 2000 total points
ID: 38815989
Probably you just want to unlock the new cells you're entering, and then protect the sheet afterward. To do this, add this line before the increment statement:
Selection.Locked = False

And then add this line after the while loop:
Activesheet.Protect

Also, don't forget to turn ScreenUpdating back on again, or you won't be able to do anything with the sheet after you run the script.

With a few other tweaks to code clarity, including the use of a Range object instead of selecting the rows to format, I suggest the following for your full script:
Sub Insert_2_Rows()
Dim i As Integer
Dim nRows As Integer
Dim firstRow As Integer
Dim rRows As Range

Application.ScreenUpdating = False
nRows = 2
firstRow = 1
i = firstRow + 1
While Not IsEmpty(Cells(i, "A"))
Rows(i & ":" & i + nRows - 1).Insert
Set rRows = Rows(i & ":" & i + nRows - 1)
With rRows.Font
    .Italic = True
    .Color = -16776961
    .TintAndShade = 0
End With
rRows.Locked = False

i = i + nRows + firstRow
Wend
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:JonMny
ID: 38815992
Once it's protected you would need to unprotect before making any changes. Can you unprotect then insert the rows and then protect again in your marco?

Activesheet.Unprotect
' Code to add blank row
ActiveSheet.Protect
0
 

Author Closing Comment

by:UTSWPeds
ID: 38816092
Thanks for a very quick and helpful (and accurate) solution to this problem.  The only modification I have is to declare the variables as "Long" instead of "Integer" in the event that you have a large dataset of more than 32k records.  

I wasn't sure I was going to be able to figure this one out, but with the help of the EE community, another problem has been solved.  

Many Thanks,
MS
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

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…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

664 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