0

I use the VBA code below to copy the row above a button in the worksheet and insert that copy when directly above the row the button is on. Its purpose is to insert a new row for the user to add step information to a project management document.

For some reason, when I add information in one of the rows above this copied row, I get an error on the indicated line. The error is a

run-time error '1004: insert method of range class failed.

If I end and push the button again, I get a

run-time error '1004': Unable to get the TopLeftCell property of Button Class.

If I shut down Excel and reopen it, the button works fine. I usually add all the lines I need, enter the data, but when I go to add a new row, it get this series of errors again.

Is there a reason for this or are there better codes practices that will help me avoid this issue?

Also, I just noticed that it appears that another worksheet must be open for these error pattern to occur.

 Sub rowselect()
      Dim b As Object, cs As Integer
      Dim Row2 As Integer
      Dim Row As Integer

      Set b = ActiveSheet.Buttons(Application.Caller)
      With b.TopLeftCell ' 2nd error when code attempted again. Excel must be shut down for code to run properly
             Row = .Row - 1
             Row2 = Cells(Row, "e")
      End With

      Rows(Row).Select
      Selection.Copy
      Selection.Insert shift:=xlDown 'First error thrown here
      Cells(Row + 1, "e") = Row2 + 1
      Selection.EntireRow.Hidden = False
  End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
MsAgentM
  • 153
  • 2
  • 14
  • I can't see where you have indicated the line that throws the error? Can you add a comment on the line or just update your question with line? – Zac Jan 08 '19 at 14:34
  • The question was updated. – MsAgentM Jan 08 '19 at 14:41
  • 1
    First, it's best practice to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251). Second, why are you using `Application.Caller` to set your `b` button? Does that work correctly? – BruceWayne Jan 08 '19 at 15:02
  • `Row2 = Cells(Row, "e")` I think this will return a value of E-Row not the row of E-Row, could the value of Row2 be 0? – Nathan_Sav Jan 08 '19 at 15:41
  • Note that you cannot use `Integer` for row counting variables. Excel has more rows than `Integer` can handle instead always use `Long`. – Pᴇʜ Jan 08 '19 at 16:03
  • I found this code when searching for ways to find the row above a button. I don't know what it uses application.caller but I have never gotten an error on that line. – MsAgentM Jan 08 '19 at 16:13
  • Row2 is meant to return the number that cell, not to count rows and then increase that number by one. – MsAgentM Jan 08 '19 at 16:15
  • Maximum range of integer is **32767**. @Pᴇʜ point is valid: if your row number is over that, you will see an error thrown. To be on the safe side, I would recommend the same. I haven't used `Interger` type for years for that very reason – Zac Jan 08 '19 at 16:19
  • `Application.Caller` doesn't always return a `String` type. [Have a look at this post](https://stackoverflow.com/questions/11267597/more-information-about-application-caller-with-vba-in-excel) – Zac Jan 08 '19 at 16:25
  • @ZacThanks, I'll change but the integer never gets anywhere near that number and it won't for this doc. Also, I'm not looking for a string to be returned. I'm looking for a row number. There are several buttons like this on worksheet so I need to know the row number of the button pressed for a reference. – MsAgentM Jan 08 '19 at 20:11

0 Answers0