none
VBA Userforms – Getting errors when item from combo box is not selected RRS feed

  • Question

  • Hi there
    Thank you in advance for taking the time to check this out.

    Objective
    :
    I have 2 combo boxes, one is dependent on what has been selected in the first combo box (dynamic named range), they work fine except for an irritating error when the user accidentally clicks in the empty Cmbox_IncCategory and it won’t allow the user to go back to the cmbx_Category_Type box if the user forgot he had to make a selection from that first before selecting the Cmbox_IncCategory.

    The error that pops up is “Invalid property value”.

    I tried having text in there to say “please select from Cmbox_IncCategory first, but that didn’t fix it.
    I tried to ‘If error resume next’ but that didn’t like it either. Now I am stumped.

    Main combo box= cmbx_Category_Type
    2nd combo box (displaying a list dependent on what was selected in Main combo box)= Cmbox_IncCategory

    I know there must be a way to fix it so that if a user clicks on the combo box, but doesn’t make a selection it won’t lock up the form.

    Yes, it is a mandatory field, and I was considering using a message box to advise the user that this must be completed, but I am not sure how to do it (and avoid the errors )

    Here’s the current code I have for the combo boxes.

    Me.Cmbox_IncCategory = "" 'Clears the contents of the 2nd combobox when another category is chosen
                                
                                
                                                            On Error Resume Next
    'I can't seem to have the Incident Category combobox to be empty when the form is open _
     I have tried Cmbox_IncCategory.Value = "", but I get an error. I then tried Cmbox_IncCategory.text = "" _
     but also get the error. I don't know how else to get it to work .. I tried both codes in the _
     form_initialize, but get an error ... I'm stumped !
     
                                   Select Case Me.cmbx_Category_Type
                                        Case "Crime"
                                            Me.Cmbox_IncCategory.RowSource = "Inc_Cat_CRIME"
                                            
                                        Case "Property Damage - Minor - NS"
                                            Me.Cmbox_IncCategory.RowSource = "Inc_Cat_PROPRTY_NS"
                                            
                                        Case "Property Damage - Significant - S"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_Proprty_S"
                                
                                        Case "Safety"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SAFETY"
                                         
                                        Case "Security Breach"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_BREACH_S"
                                        
                                        Case "Support"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SUPPORT"
                                        
                                        Case "Vehicle"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_VEHICLE_S"
                                      
                                    End Select
    
                        End Sub

    I’d be really grateful if someone could help me out, or perhaps direct me to where I might find some coding that will achieve the result I am seeking.

    This links to My Sample form

    With much gratitude,
    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 8:14 AM

Answers

  • Describe what you mean by when the combo "empty", do you mean it is unpopulated with no List, or it has not yet been selected and appears empty with its ListIndex = -1.

    Either way it should be possible to exit the Combo unless your code errors and breaks, where is your error.

    In passing, you can't do myCombo.Text = "" unless one of its items is an empty string. Try myCombo.ListIndex = -1. On which point, after applying a new list with RowSource it might be worth doing myCombo.ListIndex = 0 to show the first item (ListIndex is zero base)

     
    Tuesday, March 31, 2015 8:52 AM
    Moderator
  • I've got your file, in a quick glance it has several issues! 

    To fix the one you raised here, replace the combo Cmbox_IncCategory, it seems corrupt and I have an idea why it became so.

    Select and copy Cmbox_IncCategory on the form and paste as a new combo. Delete the old Cmbox_IncCategory after noting it's Left & Top properties. Important, delete the new combo's RowSource and Text/Value properties, name it as Cmbox_IncCategory and place it back in position.

    Tuesday, March 31, 2015 1:27 PM
    Moderator
  • Hi Peter :)

    I've taken your suggestion onboard and created a new form.

    Whatever was causing the problem is now resolved :) ... I don't have any issues thus far :)

    Thank you so much for your help and patience.

    I apologise for not responding earlier, but I was away for Easter.

    There is bound to be another question regarding another query, but I will post it under a new Post ... I just have to give it a go first.

    Thank you very much again.

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Monday, April 6, 2015 9:50 PM

All replies

  • Describe what you mean by when the combo "empty", do you mean it is unpopulated with no List, or it has not yet been selected and appears empty with its ListIndex = -1.

    Either way it should be possible to exit the Combo unless your code errors and breaks, where is your error.

    In passing, you can't do myCombo.Text = "" unless one of its items is an empty string. Try myCombo.ListIndex = -1. On which point, after applying a new list with RowSource it might be worth doing myCombo.ListIndex = 0 to show the first item (ListIndex is zero base)

     
    Tuesday, March 31, 2015 8:52 AM
    Moderator
  • Hi Peter,

    Thank you for your quick response :) Brave man! :)

    Both combo boxes are blank to start with (because nothing has been selected). If I click on the first combo box (cmbx_Category_Type) which lists the main events/category types, but I don't select any thing, and I click in the secondary box (Cmbox_IncCategory) and then click on any other control, I get an “Invalid property value” error. When I click OK on the error (a few times) and I return to the code window, it gives no indication where/what is breaking the code/causing the error. I have no choice but to exit the form by stopping in the code window.

    If I click on the first textbox, (cmbx_Category_Type), and then do the right thing and make a selection, then I have no problems with the secondary textbox (Cmbox_IncCategory).

    As I am not that up on VBA I am trying to follow your instructions... I have changed the code as follows, removed and replaced the rowsource name, but I encounter the very same problem.

    When I changed my code from Me.Cmbox_IncCategory="" to yours myCombo.ListIndex = -1  (changing the combo box name to reflect my combo box name) and adding the second line of code ... Me.Cmbox_IncCategory.ListIndex = 0 I still got the same behaviour and error.

    I even tried moving each row of the named rows down one row (so that there would be a 'blank' row which could be selected to get out of trouble, but it wasn't very successful either.

    Have you opened my form to see what happens, I included the link so whoever was looking at my problem (and yes, I have many I know), could see for themselves what it is doing/not doing.

    The code now reads as below (is this what you meant?) - the results were as I mentioned above.

                                Me.Cmbox_IncCategory.ListIndex = -1 'Clears the contents of the 2nd combobox when another category is chosen
                                Me.Cmbox_IncCategory.ListIndex = 0
                                
                                
                                                           ' On Error Resume Next
    'I can't seem to have the Incident Category combobox to be empty when the form is open _
     I have tried Cmbox_IncCategory.Value = "", but I get an error. I then tried Cmbox_IncCategory.text = "" _
     but also get the error. I don't know how else to get it to work .. I tried both codes in the _
     form_initialize, but get an error ... I'm stumped !
     
                                   Select Case Me.cmbx_Category_Type
                                        Case "Crime"
                                            Me.Cmbox_IncCategory.RowSource = "Inc_Cat_CRIME"
                                            
                                        Case "Property Damage - Minor - NS"
                                            Me.Cmbox_IncCategory.RowSource = "Inc_Cat_PROPRTY_NS"
                                            
                                        Case "Property Damage - Significant - S"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_Proprty_S"
                                
                                        Case "Safety"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SAFETY"
                                         
                                        Case "Security Breach"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_BREACH_S"
                                        
                                        Case "Support"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_SUPPORT"
                                        
                                        Case "Vehicle"
                                        Me.Cmbox_IncCategory.RowSource = "Inc_Cat_VEHICLE_S"
                                        
                                
                                
                                    End Select
    
                        End Sub

    I appreciate your patience :) I am growing in knowledge, but its a slow process. Please let me know if there is anything else I can help clarify for you :)

    This links to My Sample form

    Thanking you again

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 12:06 PM
  • I've got your file, in a quick glance it has several issues! 

    To fix the one you raised here, replace the combo Cmbox_IncCategory, it seems corrupt and I have an idea why it became so.

    Select and copy Cmbox_IncCategory on the form and paste as a new combo. Delete the old Cmbox_IncCategory after noting it's Left & Top properties. Important, delete the new combo's RowSource and Text/Value properties, name it as Cmbox_IncCategory and place it back in position.

    Tuesday, March 31, 2015 1:27 PM
    Moderator
  • Hi Peter,

    LOL ... I am quite aware that I have several issues LOL :)

    That's why I need people like you to share your knowledge for us poor grasshoppers :)

    I did actually work out that the problem was what was in the the properties of the Cmbox_IncCategory ... nasty thing that :)

    Even with that grand knowledge was trying different avenues ... in an attempt to fix it ... (entered a row in the named range, thinking that if it was 'blank' it shouldn't be a problem ... but just like politicians, corruption creeps in everywhere :)

    I need some beauty sleep now, as I have been at it since 8:30 this morning and it is now past midnight ... so I will look into it tomorrow at work.

    I really appreciate you taking the time to help me out ...

    Thank you very much ... and I'll let you know how I go

    Cheers,

    The very tired butterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 1:42 PM
  • Bit of beauty sleep always helps :)
    Tuesday, March 31, 2015 2:38 PM
    Moderator
  • Hi Peter :)

    I've taken your suggestion onboard and created a new form.

    Whatever was causing the problem is now resolved :) ... I don't have any issues thus far :)

    Thank you so much for your help and patience.

    I apologise for not responding earlier, but I was away for Easter.

    There is bound to be another question regarding another query, but I will post it under a new Post ... I just have to give it a go first.

    Thank you very much again.

    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Monday, April 6, 2015 9:50 PM