+ Reply to Thread
Results 1 to 11 of 11

ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Hi All,

    Could you please help me on my below two issues with the UserForm, which has a ComboBox with three different type of actions for data - To Add, To Update and To Delete.
    1.) I want to restrict the value in the dropdown list, so I am just using "Match Required = True" in the ComboBox property.
    I am getting an error message as "Invalid Property Value" while clicking on the combobox then try to jump/go to other Optionbuttons/ Textboxes.

    Example: If I choose "Update" or "Delete" option, then the combobox displays the list of all the available records, but If I pick/select any record number from it, then delete that value immediately (just simply press "Delete" key"), and try to jump/go to another option like "Add" or Others, then I get this error message. Everytime I have to press the "ESC" key to bypass the error message. I am unable to figure out the perfect soultion of it.

    2.) If you please look at my UserForm, here I am able to Add the new records, but I don't know why I am unable to "Update" or "Delete" any record properly.

    Please help on both above points.

    Thanks in Advanced!
    Regards,
    SunOffice
    Last edited by SunOffice; 12-05-2013 at 04:26 AM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    First thing, set MatchRequired to False.

    Then use code to check if the user has selected/entered a value on combobox's list

    You can do that by checking it's ListIndex, it'll be -1 if they have't selected/entered something from the list.

    Perhaps something like this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Thanks for the quick reply!

    The Query 1st is almost solve with this, but If you please look at my UserForm; I am still facing problem with "Update" and "Delete" optionbuttons.

    Values are not updating - Because everytime they pick their old values.
    Records are deleting - but the code also 1st update their old values, then delete the record (Run the code in F8 key mode).

    Please Login or Register  to view this content.
    Please you/anyone look into my excel issues.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Not sure I understand, the code I posted will only run once and what forever is executed is determined by the caption of the submit button.

    What exactly are you seeing when you step through?

    How can I recreate the behaviour you describe?

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    I don't know what I am doing wrong with the UserForm functionality, that's why I am not getting the accurate results.

    Actually I have three kinds of OptionButtons:
    1.) "Add" - To add a new record on sheet.
    2.) "Update" - To update/edit the already available record.
    3.) "Delete" - To delete the complete row of the selected record (by ComboBox Value)

    I am changing the Caption of Submit button as per the selection of an OptionButton.

    Please help me and correct my program.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    I can't look at the workbook right now but I notice in the code you use the record number to locate the row the record is on.

    Is that always going to be accurate?

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Yes... the 1st row is for Column Headers.
    Please Login or Register  to view this content.
    ... it is to identify the record number, which helps me to identify its Row number.
    Please let me know if anything else is the best method for it.

    Apart from it... I have also tried to take a new variable in btn_Submit_Click(), but it was unsuccessful attempt.
    Please Login or Register  to view this content.
    Last edited by SunOffice; 12-04-2013 at 02:20 AM. Reason: added more information.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    I think the problem is caused by the use of RowSource to populate the record no combobox.

    When you do that then any changes you make to the range that is the rowsource will trigger the Change event of the combobox.

    That change event places the values from sheet into the controls on the userform.

    Anyway, take a look at the attached workbook.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Thank you so much Norie!
    Yes you are right!

    Please also see my method for the solution. It's totally different to you solution... Your's is short and simple.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Your code looks fine and if it works that's the important thing.

    Mind you, I'm not sure about the GoTo in the combobox Change event.

    The Goto just seems to exit the sub, so why not just use Exit Sub.

  11. #11
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: ComboBox - "Error: Invalid Property Used" while restricting the dropdown values

    Yes! You are right again! Thank you!

+ 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. Run Time Error-380 "Could not set the value property. Invalid property value"
    By Cijo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-29-2012, 04:04 AM
  2. Replies: 1
    Last Post: 06-07-2012, 11:38 AM
  3. "Wrong number of arguments or invalid property assignment" *sigh*
    By bg_enigma1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-21-2012, 05:54 PM
  4. Clear Form results in INCORRECT "Invalid Property" message....
    By B in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-20-2006, 01:30 PM
  5. "Invalid property" after "Clear Form"
    By B in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2006, 12:10 AM

Tags for this Thread

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