Run-time Error 1004 - Method 'Range' of object '_Global' failed

[MsBecca]

Could someone review my attached code and let me know why I am receiving the "Method Range of object_Global failed".

Comboboxlist is a named range (Thanks to SkipV for tip):
=OFFSET('Contract DB'!$IV$2,0,0,COUNTA('Contract DB'!$IV:$IV)-1,1)

Column IV on my Excel worksheet contains all my values for the combobox list. I would like my macro to add the user entered text into my combobox.

The error seems to happen in the code starting
With comboboxlist....

Any help anyone can provide would be greatly appreciated.

HERE'S MY CODE:
Private Sub cmdEnterPrintClearRec_Click()
'Move to the next empty row within the Worksheet object

NextRow = _
Application.WorksheetFunction.CountA(Range("A:A&quot) + 1

'Declare variable for the various textbox entries on the form
Party = txtParty
Party1 = txtP1
Party2 = txtP2
Party3 = txtP3
Party4 = txtP4
Party5 = txtP5
AgrTitle = Me.cboAgrT.Text
EffDate = Me.txtEffDate.Text
ExpDate = Me.txtExpDate.Text
OptDate = Me.txtOptExDate.Text
Geography = Me.txtGeo
Products = Me.txtProd

' Instructs VB to input the values entered in the form into an Excel worksheets
Cells(NextRow, 1) = Party
Cells(NextRow, 2) = Party1
Cells(NextRow, 3) = Party2
Cells(NextRow, 4) = Party3
Cells(NextRow, 5) = Party4
Cells(NextRow, 6) = Party5
Cells(NextRow, 7) = AgrTitle

'Display an empty cell if user does not enter an Effective Date

On Error GoTo ErrMsg1
If Me.txtEffDate = "" Then
Cells(NextRow, 8) = ""

ElseIf IsDate(Me.txtEffDate) = False Then Err.Raise 11
EffDate = Me.txtEffDate
Cells(NextRow, 8) = EffDate
Else
Cells(NextRow, 8) = CDate(EffDate)
End If

'Display an empty cell if user does not enter an Expiration Date

On Error GoTo ErrMsg2
If Me.txtExpDate.Text = "" Then
Cells(NextRow, 9) = ""

ElseIf IsDate(Me.txtExpDate) = False Then Err.Raise 11
ExpDate = Me.txtEffDate
Cells(NextRow, 9) = ExpDate
Else
Cells(NextRow, 9) = CDate(ExpDate)
End If

'If user does not enter an Option Exercise Date, display an empty cell

On Error GoTo ErrMsg3
If Me.txtOptExDate.Text = "" Then
Cells(NextRow, 10) = ""

ElseIf IsDate(Me.txtOptExDate) = False Then Err.Raise 11
OptDate = Me.txtOptExDate
Cells(NextRow, 10) = OptDate
Else
Cells(NextRow, 10) = CDate(OptDate)
End If

Cells(NextRow, 11) = Geography
Cells(NextRow, 12) = Products

'Print User Form

UserForm1.PrintForm

'Add new value from combobox to the end of list

On Error Resume Next

With comboboxlist
Cells(.Rows.Count + .Row, .Column).Value = cboAgrT.Value
End With

'Clear the UserForm for new entries

Me.txtParty = ""
Me.txtP1 = ""
Me.txtP2 = ""
Me.txtP3 = ""
Me.txtP4 = ""
Me.txtP5 = ""
Me.txtEffDate = ""
Me.txtExpDate = ""
Me.txtOptExDate = ""
Me.txtGeo = ""
Me.txtProd = ""
Me.txtGeo = ""

'Set Focus to txtParty

Me.txtParty.SetFocus

Exit Sub

ErrMsg1:

Me.txtEffDate = InputBox("Please enter an EFFECTIVE DATE in proper format:" & vbCrLf & " DD-MM-YYYY or DD/MM/YYYY", "Effective Date&quot
Resume Next

ErrMsg2:
Me.txtExpDate = InputBox("Please enter an EXPIRATION DATE in proper format:" & vbCrLf & " DD-MM-YYYY or DD/MM/YYYY", "Expiration Date&quot
Resume Next

ErrMsg3:
Me.txtOptExDate = InputBox("Please enter an OPTION EXERCISE DATE" & vbCrLf & "in proper format: DD-MM-YYYY or DD/MM/YYYY", "Option Exercise Date&quot
Resume Next
End Sub

 

[RobBroekhuis]

When this is being executed, is "Contract DB" your activesheet? Could it be that a chart sheet is selected?

Rob

 

[MsBecca]

Rob,

All of the above code is currently being executed in the cmdPrintEnterClearRec click event. There is only one worksheet in the "ContractDB" workbook that the information is being dumped into. This same worksheet also holds my values for the cboAgrT combobox, but in Column IV.

When "Contract DB" is opened, the InputForm loads and covers the entire screen, so the user cannot see the Excel underneath. When the data entry is done, button is clicked, data is dumped in row "1" of form, copy of form prints, form is cleared for new entries, and process starts all over again.

Everything else works perfectly until I get to the With...End Loop for updating the combo box dropdown list item information.

 

[RoyVidar]

Hi!

Could it be that you need referencing the named range thru:

[tt]with range("comboboxlist&quot [/tt]

Roy-Vidar