View Full Version : opening a form from a combobox

06-28-2004, 05:47 PM
I have a form with a combobox (cboCust).

If "add a new" (which is in the list of cboCust) is selected, or if the text typed into cbocust is not located in the list of cbocust, I want it to automatically open up another form (newcustfrm). the newcustfrm needs to open when the user leaves cbocust.

I have been using a simular vba code in worksheet_change to do it with a certain cell on a worksheet. but I would like to be able to do it from a form. This is what I am using on the worksheet;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim found As Range
With Target(1)
If Not Intersect(.Cells, Range("D3")) Is Nothing Then
If Not IsEmpty(.Value) Then
Application.ScreenUpdating = False
With Workbooks.Open("f:\db1.xls")
Set found = .Sheets("Cust").Columns(1).find( _
.Close True
End With
If found Is Nothing Then frmNewCust.Show
End If
End If
Application.ScreenUpdating = True
End With

I have been trying to code this into cboCust_change() but can't seem to get anything going.

Can anyone help?

06-28-2004, 10:57 PM
You could try using this code:

Private Sub cboCust_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With cboCust
If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then
End If
End With
End Sub

Use the event _Exit to fire when you leave the combobox. The ListIndex is -1 if the user selects nothing or enters text that is not in the list.
ListIndex = ListCount -1 means the user selected the last entry in the list, which I assumed to be the New Item entry.

Anne Troy
07-05-2004, 09:19 PM
Any luck, Greg?