Consulting

Results 1 to 3 of 3

Thread: opening a form from a combobox

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    2
    Location

    opening a form from a combobox

    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;


    [vba]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( _
    What:=Target(1).Value)
    .Close True
    End With
    If found Is Nothing Then frmNewCust.Show
    End If
    End If
    Application.ScreenUpdating = True
    End With[/vba]

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

    Can anyone help?
    Last edited by mark007; 06-29-2004 at 10:10 AM. Reason: Check out the VBA tags for formatting code!;)

  2. #2
    VBAX Tutor
    Joined
    May 2004
    Location
    Germany, Dresden
    Posts
    217
    Location
    You could try using this code:

    [VBA]Private Sub cboCust_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With cboCust
    If .ListIndex = -1 Or .ListIndex = .ListCount - 1 Then
    frmNewCust.Show
    End If
    End With
    End Sub[/VBA]

    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.

  3. #3
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Any luck, Greg?
    ~Anne Troy

Posting Permissions

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