Hello All,

I wrote the codes below a few days ago and encountered problem. The code
always halted at the "row insert" statement (marked with ?). The error
message was "Run-time error 1004 : Application-defined or object-defined
error". The error also caused Excel to close for auto-recovery. I had
tried to run the statement in the immediate window. They worked fine.
Then, you all become my last resort.

My Excel version is XP. I just want to monitor the user input of a combox
box named cbItem. If input item is not found from the list, it will then
be added to the list for future selection. Sheet "Others" is another
worksheet and locates in the same workbook. It is hidden initially.

Thanks in advance!

Patrick


Private Sub cbItem_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
If cbPayee.MatchFound Then
With cbMethod
.Activate
.SelStart = 0
.SelLength = 10
End With
Else
ans = MsgBox("Are you want to add NEW item?", _
vbQuestion + vbYesNo, "Item Not Found")
If ans = vbYes Then
strg = Application.InputBox("Please provide its CODE.", _
"New Item", Type:=1)
Sheets("others").Visible = True
Sheets("others").Activate
? Sheets("others").Range("slmaster").Range(Cells(3, 1), _
Cells(3, 3)).Insert Shift:=xlDown
Sheets("others").Range("slmaster").Cells(3, 1).Value _
= Application.WorksheetFunction.Proper(cbItem.Text)
Sheets("others").Range("slmaster").Cells(3, 2).Value = strg
End If
End If
End If
End Sub

Try breaking the problem line into several steps:

dim cell as range
dim rToShift as range
set cell = Sheets("others").Range("slmaster").cells(1)
set rToShift =Range(cell.offset(2,0), cell.offset(2, 2))
rToShift.Insert Shift:=xlDown

This will narrow the problem down if a crash occurs. And it might solve as
it is a set of simple septs. You can set watches on cell.address and
rToShift.Address to confirm they are the correct ranges.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"pwz" wrote in message
...
Hello All,

I wrote the codes below a few days ago and encountered problem. The code
always halted at the "row insert" statement (marked with ?). The error
message was "Run-time error 1004 : Application-defined or object-defined
error". The error also caused Excel to close for auto-recovery. I had
tried to run the statement in the immediate window. They worked fine.
Then, you all become my last resort.

My Excel version is XP. I just want to monitor the user input of a combox
box named cbItem. If input item is not found from the list, it will then
be added to the list for future selection. Sheet "Others" is another
worksheet and locates in the same workbook. It is hidden initially.

Thanks in advance!

Patrick


Private Sub cbItem_KeyDown( _
ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then
If cbPayee.MatchFound Then
With cbMethod
.Activate
.SelStart = 0
.SelLength = 10
End With
Else
ans = MsgBox("Are you want to add NEW item?", _
vbQuestion + vbYesNo, "Item Not Found")
If ans = vbYes Then
strg = Application.InputBox("Please provide its CODE.", _
"New Item", Type:=1)
Sheets("others").Visible = True
Sheets("others").Activate
? Sheets("others").Range("slmaster").Range(Cells(3, 1), _
Cells(3, 3)).Insert Shift:=xlDown
Sheets("others").Range("slmaster").Cells(3, 1).Value _
= Application.WorksheetFunction.Proper(cbItem.Text)
Sheets("others").Range("slmaster").Cells(3, 2).Value = strg
End If
End If
End If
End Sub

Hi Patrick,

Is the problem resolved?
If you still have any concern on this issue, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.