Macro adds rows with values, not blanks

Hello!

Using the modified Dave McRitchie InsertRowsAndFillFormulas macro as well as a highlight active row macro. Rows are being added, but they have values in them. Workbook attached. Anyone see how to fix this code?

Thanks!

Lost

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect Password:="SECRET"
Cells.FormatConditions.Delete
With Target.EntireRow
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 20
End With
 ActiveSheet.Protect Password:="SECRET"
End Sub
Private Sub cmbAddRow_Click()
If Intersect(ActiveCell, Range("A12:B40")) Is Nothing Then
    MsgBox "Your cursor is at cell " & Selection.Address & ". Please place your cursor within the index."
    Exit Sub
Else
MsgBox "You are going to add rows below cell " & Selection.Address & "."
Call InsertRowsAndFillFormulas
End If
End Sub
'/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()
'adds desired # of lines below the current line and
' copies the formulas to that/those lines
'added selection of more than one worksheet
' - Gary L. Brown
' - Kinneson Corp. 01/17/2001
' - modification from thread discussion in
' Microsoft.Public.Excel.Programming newsgroup
' on 01/17/2001
' Re: Insert Rows -- 1997/09/24 Mark Hill
' The original macro is described in
' http://www.geocities.com/davemcritchie/excel/insrtrow.htm
Dim blnProtectContents As Boolean
Dim blnProtectDrawingObjects As Boolean
Dim blnProtectScenarios As Boolean
Dim vRows As Long, i As Long
Dim strAddress As String, shts() As String
Dim sht As Worksheet

'set default for whether worksheet is protected or not
blnProtectContents = False
blnProtectDrawingObjects = False
blnProtectScenarios = False
strAddress = Selection.Address

'rev. 2005-08-02 - check if worksheet unprotected
' if it's protected, get various information
If Application.ActiveSheet.ProtectContents = True Then
blnProtectContents = True
If Application.ActiveSheet.ProtectDrawingObjects = True Then
blnProtectDrawingObjects = True
End If
If Application.ActiveSheet.ProtectScenarios = True Then
blnProtectScenarios = True
End If
ActiveSheet.Unprotect "SECRET"
If Application.ActiveSheet.ProtectContents = True Then
'not unprotected so stop process
Exit Sub
End If
End If

' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add?" & vbCr & vbCr & _
"Rows will be added UNDERNEATH this row.", _
Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number

If vRows = False Then Exit Sub
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line

ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0

'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
ActiveSheet.Unprotect "SECRET"
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 remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht

'reselect original group - Dave McRitchie 01/17/2001
' and go back to original selected cells
Worksheets(shts).Select
Range(strAddress).Select

'set worksheet back to original protected/unprotected state
ActiveSheet.Protect Password:="SECRET", DrawingObjects:=blnProtectDrawingObjects, _
Contents:=blnProtectContents, Scenarios:=blnProtectScenarios

End Sub
'/=======End of Code==========================/

Hi Learning,

I see you have a single worksheet in this workbook. You also have 3 userforms. There is about 400 lines of code. You have event code behine the worksheet that fires each time a change happens on it.

When I tried to open this workbook I got a debug stop message on the Unprotect line.

My suggestion is to take the Subs that are behind the worksheet that are NOT event macros and put them into a MODULE. I see the code in the Sub you mention goes through all sheets. I'm not sure that is allowed when in code behind a single worksheet.

Read http://www.cpearson.com/excel/Events.aspx for more background

hi, leaning, try to comment out the following line or check attachment:

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

in Sub InsertRowsAndFillFormulas()

MarvinP,

I cleaned up the attached workbook. Thanks for the help!

Watersev: Your help fixed the problem!

Two last things:

1) The "data table" itself needs to be unlocked to allow user edit. And the cells below it need to be locked. If the user adds rows, those rows need to have just the A and B cells unlocked.

2) Sometimes when the user adds rows, the formatting (borders, text alignment, don't make it to the new row. So, the user has to unprotect the document, do edits, and then reprotect. (If you look at the attached, I added a row under the "17", and the borders didn't carry over.)

Any ideas about these?

Lost

All,

In case anyone needs this thread, here's what the attached workbook has:

1) Table-like section with 2 columns for user to add data. (This section has nothing below it.)
2) Button to add new blank row to the bottom of that section.
3) New row code allows changing the properties (border, font, data validation, etc.) for each of the individual cells added (Columns A:G for this).
4) Column G has a SUM formula (via VBA) and is locked.
5) There is a named range (WONum) that looks at Col A of the section (without listing the blanks), so you can use those values for data validation in other places.
6) There is code to highlight each row as you are working on it.
7) The Delete Rows button can delete multiple rows selected, not just one.

Thanks to everyone for your help on this!

Regards,

Lost