PDA

View Full Version : [SOLVED] Insert New Row issue...doesn't work when the worksheet is protected



pattishort
05-12-2015, 06:21 AM
Hi All,
I'm new to this particular board. I have an issue with my code (and I'm still fairly new to VBA). I have a workbook that poses as a template for the engineers. I have 2 buttons: one to add a new row and one to delete active row. The requirement from the lead engineer is to have the worksheets protected to keep the other engineers from going rogue...he wants consistency. The following code works great for adding a new row and keeping the formula in cell A, BUT it doesn't work when the sheet is protected.

Code is as follows:

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) ' row selection based on active cell
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If


Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name


x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup


Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown


Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault


On Error Resume Next 'to handle no constants in range
' to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

Can someone help me with this?

SamT
05-12-2015, 08:49 AM
Bump for me

SamT
05-12-2015, 07:06 PM
Patti,
Welcome to VBAExpress, the best on the web.

It sure looks like you are an experienced programmer, even if you are new to MS VBA. For information on using Protection see the Protection Object in Help and the Worksheet Protect Method.

Although I spent some time looking at your code, I am not sure that what it does is what you want. Mostly because I can't see the workbook. What your code does, in a very round about way, is let the user select several worksheets at once and insert a number of Rows into each sheet in relation to the selected Cell on the active Sheet.

I see that you have set up the procedure so that it can be called programmatically.

See what you think of this code. It does whet I described above.

Option Explicit
'Option Explicit requires declaring all variables.



Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' row selection based on active cell
Dim CurrentCell As String
Dim SheetsSelected As Object
Dim Sht As Worksheet
Dim Cel As Range

'Save Starting point. There can be only one!
CurrentCell = ActiveCell.Address 'Top left most cell in Selected Cells

'Static Collection set to current state of Dynamic Collection
Set SheetsSelected = Windows(1).SelectedSheets

If vRows = 0 Then 'Called by User
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If

With Application 'Speed things up
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With


For Each Sht In SheetsSelected
Sht.Select
Range(CurrentCell).EntireRow.Select 'I don't trust Excel's Selection. YMMV
Selection.Offset(1).Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillCopy
'xlFillDefault = Excel's best guess as to Fill Type. See xlFillCopy

For Each Cel In Range(Selection.Offset(1).Resize(vRows).EntireRow.Address)
If Not Left(Cel.Formula, 1) = "=" Then Cel.ClearContents
Next Cel

Next Sht

'Return to original view
SheetsSelected(1).Select
Range(CurrentCell).Select

With Application 'Restore default settings
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

pattishort
05-14-2015, 06:26 AM
Hi SamT,
I tried this on a protected worksheet and it freaks out on the following line:


Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillCopy

However, when the worksheet is unprotected, it freaks out at this line:


If Not Left(Cel.Formula, 1) = "=" Then Cel.ClearContents

I have an Add New Row button on each of the tabs so these buttons are using the same macro. The engineers are writing procedures so there is a formula in column A (=ROWS(A$3:A3)) so that they don't have to manually enter in the step number. At this time, this is the only formula in the "table". Again, we'd like to have the tabs locked because there are some engineers who will want to change the formatting and the lead engineer wants consistency across the department...makes his job easier.

Thank you,
Patti

SamT
05-14-2015, 09:25 AM
I tried this on a protected worksheet and it freaks out on the following line:
Well, yeah. The worksheet is Protected.

Let's get this part to work before we add more troublesome code to confuse us (me.)

Please Define "It Freaks Out" for the ClearContents error. Provide error message text and numbers if available.
How have you tried correcting it that didn't work? Please be specific.

Can you share that workbook or a sheet from it?

Sounds like Column A is the only cell with a formula?
Try clearing the entire selection's Contents then add this line of code

Selection.Columns(1).Formula = "=Row()-3" 'Requires quotes and no spaces
'Edit "-3" to suit

Is this App supposed to work on several sheets simultaneously?

What, exactly, does your supervisor want to be consistent?

Patti, it ran fine on my machine (Win XP, Excel XP) across 1 to 3 sheets, with 4 columns of formulas, three of Constants, and some with blanks, but no formatting

pattishort
05-14-2015, 12:14 PM
Hi SamT,
What I mean by "freaking out" is that after entering the number of rows to add I get a Run-time error '1004' (The cell or chart that you are trying to change is protected and therefore read-only.). I've attached the error.

I have uploaded the workbook so you can take a look...I had to zip it because it is an xltm and that wasn't a valid file format.

And yes, you are correct that cell A is the only one with a formula but I definitely see this template evolving. There are Add New Row buttons on each tab of the workbook and on each tab cell A has the same formula to create sequential numbering for the step procedures (these cells are locked when worksheet is protected).

The lead engineer wants consistency across all procedures written and he knows several lower-level engineers will, for example, add columns. The lead engineer does not want this; he wants every one to follow the template without making modifications to the format. Does that make sense?

I have Office 2010 and I'm running on Windows 7.

I have not had a chance to try the following modification yet:


Selection.Columns(1).Formula = "=Row()-3" 'Requires quotes and no spaces
'Edit "-3" to suit

Hope this helps.

SamT
05-14-2015, 04:24 PM
How did you get a Protection Error on an unprotected sheet?

Well, yeah. The worksheet is Protected.

Patti, I think it is easier pulling chicken's teeth than getting answers from you. :banghead:


Let's get this part to work before we add more troublesome code to confuse us (me.)
That means to test everything on an unprotected sheet. When that part works, (on an unprotected sheet,) then we can work on Protection.
I see that you have 8 subs in your Workbook, but the only one that works is
Sub Delete_Row()
'Rows(ActiveCell.Row).Delete
Selection.EntireRow.Delete
End Sub

Is this App supposed to work on several sheets simultaneously?

Paul_Hossler
05-14-2015, 05:43 PM
patti -- how much of the sheet is protected? I.e. which cells are Locked?

Even if a sheet is protected, any Unlocked cells can still be edited



As an aside, I usually have a command button or macro Unprotect the sheet, do what needs to be done, and then re Protect it before exiting

As an aside2 -- in 2010 at least, the Protect command has more flexibility

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowDeletingRows:=True

Paul_Hossler
05-14-2015, 08:15 PM
The easiest thing would be to unprotect and protect it afterwards

Look at my ------------------------------------------------------ marks




'this method does not move the form control check box down to the new row...it copies it but does not move it
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' row selection based on active cell
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
Dim Sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each Sht In Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(Sht.Name).Select
'---------------------------------------------------------------------------
ActiveSheet.Unprotect
'---------------------------------------------------------------------------

i = i + 1
shts(i) = Sht.Name
x = Sheets(Sht.Name).UsedRange.Rows.Count 'lastcell fixup
Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Inser t Shift:=xlDown
Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillDefault
On Error Resume Next 'to handle no constants in range
' to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow.SpecialCells(xlConstants).Clear Contents

'---------------------------------------------------------------------------
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingRows:=True
'---------------------------------------------------------------------------

Next Sht
Worksheets(shts).Select
End Sub

pattishort
05-15-2015, 05:52 AM
All the tabs in the workbook are protected. Again the lead engineer does not want the other engineers to change the formatting (i.e., add or delete columns). I have unlocked the cells that they can modify as well as allowing them to add/delete rows. My (our) goal is to eliminate the extra step of manually entering in the step number (i.e., 1, 2, 3, and so on)...we want this to be automatic. We do not want any of the engineers to unprotect the sheet to do their work and then protect it because it defeats the purpose of locking it down and forcing them to use the template AS IS...no modifications what so ever. The sheets are locked because of them...in other words, if they would follow a format without modifying to their liking then we wouldn't be building a template with protected sheets.

pattishort
05-15-2015, 06:12 AM
Sorry Sam that I'm being difficult, but I've explained it as best as I can. Trying again:

1. Cell A has the =ROWS(A$21:A21) formula for sequential numbering in steps. Therefore, when I click the Add New Row while the sheet is protected I get the Run-Time Error '1004'. The Add New Row button works FINE when the sheet is unprotected.
The line of code that it doesn't like when I try to add a row on a protected sheet is:

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
2. The Add New Button is a single action and the button is on EVERY sheet so it should not work on several sheets simultaneously; just the active sheet. For example, if the engineer is writing steps of a procedure and there are more than 20 steps in the procedure he can click a cell in the last row, click the Add New Row button and enter the number of rows still needed to complete the procedure. In other words, if he has 25 steps but the template has 20 places holders for steps, then he clicks a cell of the last row, clicks the add new row button, enters the number 5 to add the 5 additional rows he needs. On the other hand, if the procedure has less than 20 steps, he clicks a cell in the row he wants to delete, and presses the Delete Active Row button. I wanted this to be flexible so if they need to add rows in the middle of a procedure they can or if they need to delete rows in the middle of the procedure they can do that too.
3. All worksheets are PROTECTED because the lead engineer does NOT want any of the other engineers adding or removing columns...reformatting of the template to their liking is NOT ALLOWED (i.e., this is the consistency thing I've been talking about). We are allowing them to edit cells and add/delete rows...nothing else.
4. The Overview tab is protected because the title of the document is auto populated based on what the Project, Type, Version, and Location (CLLI) is. We do not want the engineers to name the workbook something that may make since to them but not to others. This is a simple title to prevent duplication...i.e., one engineer writes a procedure but the title doesn't make since so another engineer goes to write the same one with another name. Also, there should NOT be a need to add additional information to the Escalation Tier section of the Overview tab. Again, CONSISTENCY.
5. For now the Delete Active Row button works just fine when the sheet is either protected or unprotected.

The issue is that the ADD NEW ROW button does NOT work when the sheet is protected; only unprotected.

To be honest, I haven't really tried anything on my own to fix it because I don't know where to start. I have done a lot of research and tried someone else's code to try to get it to work but it didn't do anything but add a row without the formula...I want the formula in cell A. The engineers shouldn't haven't to think about the numbers of the steps, just the procedure itself.

Paul_Hossler
05-15-2015, 06:26 AM
1. Out of morbid curiosity, did you try the changes I added in #9? Were there any issues?


2.
so it should not work on several sheets simultaneously; just the active sheet

It would appear that every selected sheet ...



For Each Sht In Application.ActiveWorkbook.Windows(1).SelectedSheets



... has the macro run on it (unless I'm missing something)


3. You might suggest that your Lead Engineer create a Functional Spec, and redesign the template

SamT
05-15-2015, 09:30 AM
Brute force: After Insert Rows... Range("A3:A" & LastRow).Formula = "=ROW()-3"

This is Paul's example, modified to work on only the active sheet and all "Step" Formulas in "A" replaced. Some extraneous code removed.

Option Explicit

'this method does not move the form control check box down to the new row...it copies it but does not move it
Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
' row selection based on active cell

If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'---------------------------------------------------------------------------
With ActiveSheet
.Unprotect
'---------------------------------------------------------------------------
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'---------------------------------------------------------------------------

Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows).Inser t
Selection.Offset(1).Resize(vRows).EntireRow.ClearContents
'---------------------------------------------------------------------------
'Note: CStr'ing LastRow et al is marginally faster and VBA-proof
Range("A3:A" & CStr(LastRow + vRows)).Formula = "=ROW()-3" 'Edit numbers to suit
'---------------------------------------------------------------------------

.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingRows:=True
'---------------------------------------------------------------------------
End With

End Sub

Personally, I would add buttons to the Ribbon for Inserting and Deleting.

Aussiebear
05-16-2015, 03:25 PM
Personally, I would add buttons to the Ribbon for Inserting and Deleting.

Well, that beats trying to skin a cat by putting it under a 10 ton road roller :devil2:

SamT
05-16-2015, 06:15 PM
Sure beats having 30 Command buttons on 15 worksheets :D

And they never scroll out of sight.

Paul_Hossler
05-17-2015, 05:05 PM
Range("A3:A" & CStr(LastRow + vRows)).Formula = "=ROW()-3" 'Edit numbers to suit


If I were re-doing it, I'd forget messing around with a worksheet function and just re-build all the numbers using VBA as values

In addition to SamT's ribbon approach

But there's many other what I would call design deficiencies that could cause issues if they don't do everything exactly correctly

For example, if the cursor is in row 8 and you [Add Rows] 2 of them, then you get the screen shot

pattishort
05-18-2015, 11:47 AM
Thank you gentleman! This has been resolved. I did run some of your ideas by the lead engineer and he liked them so they will go into the next version of the template.

Sorry again for being difficult.

Have a great week :)