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
Bookmarks