+ Reply to Thread
Results 1 to 19 of 19

activex combobox to trigger automatic sheet code

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    activex combobox to trigger automatic sheet code

    Hello,
    I'm trying to make harmony between the two codes below
    The following code enters the value from the combobox directly into cell D32:
    Please Login or Register  to view this content.
    ...and as for this code, if a value is entered in column D it detects through the range if there is a duplicate of that value and then displays a message box and clears the target cell (in this case it's cell D32). I hope that since cell D32 is linked to Combobox1 then ComboBox1 would automatically clear as well
    Please Login or Register  to view this content.
    Thanks in advance

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: activex combobox to trigger automatic sheet code

    I believe that cell D32 is based of the combobox, not the other way around so if D32 is cleared, it wont clear the combobox. This will though, add it to your 2nd code
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Ok thank you.
    i apologize but i failed to mention that the second code doesn't even execute at all.
    I was thinking faster than i was typing

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: activex combobox to trigger automatic sheet code

    I used your codes in a blank workbook and it did work correctly. Where did you put the codes?
    Edit* It also closes and clears the combobox.
    Last edited by gmr4evr1; 01-08-2016 at 01:49 PM. Reason: Added edit

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Here is an example workbook Cmd-kosherboy.xlsm
    Choose a name from the dropdown that already exists in column D.
    Code should remove it since its a duplicate but seems that the code is ignoring the 2nd code.

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: activex combobox to trigger automatic sheet code

    I think I know what the problem is. The combobox isn't actually part of the sheet and the value being placed into D32 is not being placed there manually, therefore it is not reading it as a change. If you select a name in the combobox then click in a cell on the sheet, the code runs fine, but, I know this isn't an ideal way to do this. When I did it myself, I put the combobox on a userform, that's why it worked for me. Replace your first code with this..
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Thanks again for your reply gmr.
    The reason why i don't use the default 'change' event for the combobox is because i have a code that i wrote that must need be executed only once i go out of the combobox. In other words: lose focus, exit the combobox etc. I don't want to complicate matters too much but the code will not work properly whilst the user is still in the combobox.
    I searched everywhere online and i didn't see any talk about a "before Update" on active x controls

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

    Re: activex combobox to trigger automatic sheet code

    Selecting a value from the combobox doesn't trigger the BeforeUpdate event, and that might be because an ActiveX combobox on a worksheet doesn't have such an event.

    What exactly are you trying to achieve?
    If posting code please use code tags, see here.

  9. #9
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Quote Originally Posted by Norie View Post
    Selecting a value from the combobox doesn't trigger the BeforeUpdate event, and that might be because an ActiveX combobox on a worksheet doesn't have such an event.

    What exactly are you trying to achieve?
    Hi Norie,
    Basically i have a sheet with dozens of active x comboboxes, as soon as a value is selected from the dropdown, it's linked cell in column D is populated and then the below code runs:
    Please Login or Register  to view this content.
    The problem that i am having is the following: let's say i selected "Jack" from the 1st combobox and then i started typing out "Jill" in the 2nd combobox, as soon as i type in the "J" for Jill the code runs since the combobox displays the first choice for "J" which is "Jack" and which would result in the combobox getting cleared via the code, i could avoid this if i just select "Jill" from the combobox but this is not the ideal situation since the drop down menu goes on forever.
    I know this wouldn't be a problem in userforms since the boxes in userforms have the option of triggering a code only when the user selects out of the box, in my first post i was under the assumption that active x controls work the same way as userform controls.
    Maybe there is a workaround?
    Last edited by kosherboy; 01-11-2016 at 10:53 AM.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: activex combobox to trigger automatic sheet code

    maybe so
    Please Login or Register  to view this content.
    Previously set the LincedCell property to ""

  11. #11
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Thanks nilem,
    Ideally i was trying to avoid putting the code in the combobox because then i would have to copy it into every combobox and i have 50 of them! but i will worry about that later...
    Anyway, i copied your code to the next comboxbox like this:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    ..but the msgbox got displayed and cleared the cell even when i entered the first value into combobox1?
    Last edited by kosherboy; 01-11-2016 at 11:51 AM.

  12. #12
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: activex combobox to trigger automatic sheet code

    probably need a sample of your file with multiple combobox'es

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: activex combobox to trigger automatic sheet code

    Hello Kosherboy,

    This code worked on the example workbook you posted. This can be adapted to other ComboBoxes as well.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  14. #14
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Hi Leith,
    Thanks for your response.
    Here is the workbook uploaded again Cmd-kosherboy version2.xlsm
    Now, try to type in the second combobox "Jill", you will see that since the combobox displays the first option for J (which in this case is jack) the code will run right away and clear the box even though i want to type in jill.
    Also, it seems that the sheet's change procedure runs twice and bugs on the second try

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: activex combobox to trigger automatic sheet code

    Hello kosherboy,

    The behaviour you see is because the ComboBox is tied to the worksheet's range "N1:N4". Excel imposes it's auto-matching rules on what you type into the ComboBox as if you were typing into a cell.

    While using the ListFillRange property is easy, it has unintended consequences for controlling the ComboBox. This is one of them.

    If you explain what you are trying to achieve, it will be easier to provide a solution.

  16. #16
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    That actually makes sense, didn't think of that.
    I would like for the code to run after a complete match is either MANUALLY typed into the combobox or selected from the dropdown.
    I noticed that the automatch would highlight in blue the rest of the value, is there a way to detect if there is no blue highlighted characters?

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

    Re: activex combobox to trigger automatic sheet code

    You can check if a value from the list has been selected/entered by looking at the ListIndex property of the combobox.

    If that's -1 a value from the list hasn't been selected/entered.

  18. #18
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: activex combobox to trigger automatic sheet code

    Hi Everybody!
    i am very excited to share with you all that i have found a solution to this ongoing 2-week-old excel problem
    i need to change
    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.
    This solves a big portion of the issues i have been having!
    Thank you all for the time spent on this, it is much appreciated

  19. #19
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: activex combobox to trigger automatic sheet code

    Glad you were able to get it worked out.

+ 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. VBA code to ComboBox (ActiveX Control)
    By kasun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2014, 04:41 AM
  2. VBA Code Vs Vlookup formula for ActiveX Control Combobox
    By newqueen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-04-2014, 04:35 PM
  3. Copy ActiveX ComboBox from One Sheet to Another and Then Have it Execute Code
    By mawalker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-13-2013, 09:47 AM
  4. Code to change LinkedCell of ActiveX ComboBox
    By ozhunter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-08-2011, 07:10 AM
  5. ActiveX ComboBox listfillrange automatic update
    By Makafi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2009, 01:54 PM
  6. [SOLVED] Prevent automatic combobox event trigger
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2006, 04:00 PM
  7. [SOLVED] ActiveX ComboBox Control on Sheet
    By quartz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2005, 03:06 PM

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