+ Reply to Thread
Results 1 to 14 of 14

ListBox and ComboBox in UserForm not responding to code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    ListBox and ComboBox in UserForm not responding to code

    When I open the Userform & tab through controls, this code is not having any effect?

    Private Sub cmbMonth_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
        If IsEmpty(cmbMonth.Value) Then
            cmbMonth.BackColor = rgbPink
            Cancel = True
        End If
    End Sub
    Private Sub LstCustomer_AfterUpdate()
    
        If IsEmpty(LstCustomer.Value) Then
            lblCustomer.ForeColor = vbRed
            Cancel = True
        End If
    End Sub
    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,151

    Re: ListBox and ComboBox in UserForm not responding to code

    If you are just tabbing through the controls, then you have not updated the control & therefore the code will not run.
    Depending on what you are trying to do , you could use the Exit event instead

  3. #3
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    Re: ListBox and ComboBox in UserForm not responding to code

    If the user does not select anything from the list, I want to change the color of the label to red to remind the user to make a selection.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: ListBox and ComboBox in UserForm not responding to code

    cmbMonth_BeforeUpdate

    That code will not do anything until the control is changed.

    If you want code to be triggered when you tab into the control then use the Enter or Exit codes.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,151

    Re: ListBox and ComboBox in UserForm not responding to code

    In that case use the exit event.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: ListBox and ComboBox in UserForm not responding to code

    
    Private Sub cmbMonth_Enter()
    
        If IsEmpty(cmbMonth.Value) Then
            cmbMonth.BackColor = rgbPink
            Cancel = True
        End If
    End Sub
    Private Sub LstCustomer_AfterUpdate()
    
        If IsEmpty(LstCustomer.Value) Then
            lblCustomer.ForeColor = vbRed
            Cancel = True
        End If
    End Sub

  7. #7
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    Re: ListBox and ComboBox in UserForm not responding to code

    Thanks for your reply but this is not working? I launch the form in Excel & if I don't make a selection from the Listbox, I want to change the color of the label to remind the user to make a selection. I removed the line of code "Cancel = True" for the Listbox:
    Private Sub LstCustomer_AfterUpdate()
    
        If IsEmpty(LstCustomer.Value) Then
            lblCustomer.ForeColor = vbRed
            
        End If
    End Sub

  8. #8
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    Re: ListBox and ComboBox in UserForm not responding to code

    I also tried the Exit event but it also did not work?

  9. #9
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,322

    Re: ListBox and ComboBox in UserForm not responding to code

    I am confused why are you attempting to use either of those events as they are inappropriate.
    Without your workbook it is difficult to see in what context you are using the events.
    See the yellow banner at the head of the page to help us to help you.
    To check for empty interrogate the listcount.

    If LstCustomer.ListCount = 0 Then MsgBox "Listbox empty"

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,151

    Re: ListBox and ComboBox in UserForm not responding to code

    Can you please supply a sample workbook?

  11. #11
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    Re: ListBox and ComboBox in UserForm not responding to code

    Thanks - I have attached an example file. If you click "Post Record" with no selections, the month code works but not the ListBox code.
    Thanks in advance
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,151

    Re: ListBox and ComboBox in UserForm not responding to code

    Thanks for that.
    You need to remove the Exit Sub lines, otherwise it never checks the listbox, you also need to change the listbox part like
    Private Sub cmdPostData_Click()
    
        If cmbMonth.Value = "" Then
            cmbMonth.BackColor = vbRed
        End If
        
        If ListBox1.ListIndex = -1 Then
            lblProduct.ForeColor = vbRed
        End If
        
        Range("Product").Value = ListBox1.Value
        Range("Month").Value = cmbMonth.Value
        
    End Sub

  13. #13
    Forum Contributor
    Join Date
    06-11-2009
    Location
    Cape Town
    MS-Off Ver
    Microsoft 365
    Posts
    854

    Re: ListBox and ComboBox in UserForm not responding to code

    Thank you - (ListIndex = -1) was the solution. Spent so much time trying to figure this out but learnt a lot of other stuff! Thanks, once again

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,151

    Re: ListBox and ComboBox in UserForm not responding to code

    You're welcome & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] UserForm ComboBox Filter ListBox Display
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-07-2017, 04:43 AM
  2. Using ComboBox on Userform to filter ListBox
    By burger160 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2014, 05:03 PM
  3. [SOLVED] Changed userform combobox to listbox, unable to get userform to retrieve datasheet values
    By dragonabsurdum in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2013, 01:38 PM
  4. [SOLVED] filling listbox from userform combobox problem
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 07-13-2013, 03:48 AM
  5. UserForm listbox/combobox
    By adeleex in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-25-2011, 03:54 AM
  6. Userform One or more options listbox? combobox?
    By starcraftbud in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2010, 11:44 AM
  7. UserForm;comboBox,ListBox
    By samsara in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-03-2009, 06:13 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1