Run-time error '1004': No cells were found

J

Jamie

I'm working in Excel 2002

I have a form that is protected. The user has the option of adding a new
blank row that maintains the formatting and formulas of the row above it. I
have an Insert a Row button that activates the following macro coding:

Sub AddRow()
Dim Msg, Style, Title, Response
Dim rngAdd As Range
Set rngAdd = ActiveCell
Msg = "Do you want to insert a row?" 'Define message.
Style = vbYesNo + vbDefaultButton2 'Define buttons.
Title = "Insert a Row" 'Define MsgBox title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 'User chooses Yes.
ActiveSheet.Unprotect
rngAdd.Offset(0, 0).EntireRow.Insert 'Add a row "above" the current
position
rngAdd.EntireRow.Copy 'Copy the row you just "moved down"
rngAdd.Offset(-1, 0).EntireRow.PasteSpecial xlPasteAll
'Paste "move" the old data "up" into newly created row
rngAdd.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
'Clear old row so that it "appears" to be a "new" row
ActiveSheet.Protect
End If
rngAdd.Offset(0, 0).Select
End Sub

This works great if the current row they are in, has information in it, and
the Insert a Row button is pressed. Here is the problem. If the user has
not entered any information in the row that they are currently in and they
press the Insert a Row button I receive the Run-time error "1004': No cells
were found. If I press End and then the ESC key (to unselect the row) my
worksheet is now unprotected, this adds to the problem.

So, my question is how can I suppress this message or replace it with my own
stating something like: "Each row in the table must be completed before
inserting a blank row."

Thanks for any help you can give me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top