Results 1 to 2 of 2

Error in Excel VBA code for inserting combo box - "Insert method of Range Class failed"

Threaded View

adityam Error in Excel VBA code for... 04-01-2014, 02:59 AM
adityam Re: Error in Excel VBA code... 04-01-2014, 03:21 AM
  1. #1
    Registered User
    Join Date
    04-01-2014
    Location
    Pune, India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy Error in Excel VBA code for inserting combo box - "Insert method of Range Class failed"

    Hi
    I have created an excel code in which when a button is pressed, it inserts a row and two combo boxes. it works fine when the button is pressed a couple of times but after pressing the button 3-4 times (inserting 3-4 rows), I get the error - "Run time error '1004': Insert method of Range class failed". I am unable to find any work around based upon alternatives suggested in this forum. Please can any one go through this code and set me on the correct path. Following is my code -
    ----------------------------------------------------------------------------------------------------------------
    Private Sub CommandButton1_Click()
    
    
    Dim phase_Link As String
    Dim phase_rng As Range
    
    Dim category_Link As String
    Dim category_rng As Range
    
    Dim oleObj_phase As OLEObject
    Dim oleObj_category As OLEObject
    
    ' Find the empty row to insert the code
    startRow = 16
    i = startRow
    Do While Cells(i, "B").Value <> ""
    i = i + 1
    Loop
    endRow = i - 1
    
    'Insert the empty row and & a few details below the last row (endRow)
    Cells(endRow + 1, "A").EntireRow.Insert
    Cells(endRow + 1, "B").Value = endRow + 2 - 16
    Cells(endRow + 1, "H").Value = 1
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Insert combobox in the cell(newly inserted row, "E") 
    Set phase_rng = ActiveSheet.Cells(endRow + 1, "E")
    
    With phase_rng
    Set oleObj_phase = .Parent.OLEObjects.Add _
    (ClassType:="Forms.ComboBox.1", _
    Link:=False, _
    DisplayAsIcon:=False, _
    Left:=.Left, _
    Top:=.Top, _
    Width:=.Width, _
    Height:=15)
    End With
    
    'Link the combobox value to the cell
    phase_Link = "$E$" & endRow + 1
    
    'Set combobox list
    With oleObj_phase
    .ListFillRange = "Matrix!$A$8:$A$10" 
    .LinkedCell = phase_Link
    .Name = "combPhase" & endRow + 1
    End With
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    'Insert combobox in the cell(newly inserted row, "F")
    Set category_rng = ActiveSheet.Cells(endRow + 1, "F")
    
    With category_rng
    Set oleObj_category = .Parent.OLEObjects.Add _
    (ClassType:="Forms.ComboBox.1", _
    Link:=False, _
    DisplayAsIcon:=False, _
    Left:=.Left, _
    Top:=.Top, _
    Width:=.Width, _
    Height:=15)
    End With
    
    'Link the combobox value to the cell
    category_Link = "$F$" & endRow + 1
    
    'Set combobox list
    With oleObj_category
    .ListFillRange = "Matrix!$B$8:$B$16"
    .LinkedCell = category_Link
    .Name = "combCategory" & endRow + 1
    End With
    
    End Sub
    --------------------------------------------------------------------------------------

    Also there is a weird problem, the excel crashes very often when I use this sheet. Also the error points to the following line of code -

    Cells(endRow + 1, "A").EntireRow.Insert

    Many thanks,
    -Aditya
    Last edited by adityam; 04-01-2014 at 03:20 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Insert Row throws error "Insert Method of Range class failed"
    By bg_enigma1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2015, 11:30 AM
  2. "Insert method of Range class failed" in Shared mode
    By BartDeHertogh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2012, 03:50 PM
  3. Excel VBA Runtime error 1004 "Select method of Range class failed"
    By jeskit in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2012, 10:16 AM
  4. Error Using filters "Method of range class failed"
    By papermoon in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-12-2010, 10:07 AM
  5. "Select method of range class failed" error
    By RBI in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-22-2008, 10:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1