+ Reply to Thread
Results 1 to 11 of 11

ListBox _change() command doesn't execute

  1. 04-05-2020, 08:50 PM #1
    Kevin-S
    Kevin-S is offline
    Registered User
    Join Date
    04-02-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2019
    Posts
    10

    ListBox _change() command doesn't execute

    Hi,

    In a worksheet, I created a MultiSelectMulti ActiveX ListBox called ListBox1. The code is in the target worksheet called Feuil1.

    Part of the code in the _change() event handler does not to execute.

    According to the documentation (docs . microsoft.com/en-us/office/vba/language/reference/user-interface-help/change-event), it should fire for the following:
    • Clicking a CheckBox
    • Selecting a new text value for a ListBox

    As per below example, the MsgBox executes fine every time I un/select a row. Yet, weirdly, the .BackColor does not. I've to open the debugger and manually press F8 for it to execute.

    Please Login or Register  to view this content.
    Does someone have an idea as to why this happens?

    Thanks,

    Kevin
    Reply With QuoteRegister To Reply
  2. 04-05-2020, 09:22 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,182

    Re: ListBox _change() command doesn't execute

    You change the background color to RGB(255, 125, 255), and then immediately change it again to RGB(0, 125, 0) on the very next line of code. What color does it start as?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 04-05-2020, 10:51 PM #3
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: ListBox _change() command doesn't execute

    Try BeforeUpdate or AfterUpdate event instead of Change.
    Reply With QuoteRegister To Reply
  4. 04-06-2020, 02:08 AM #4
    Kevin-S
    Kevin-S is offline
    Registered User
    Join Date
    04-02-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2019
    Posts
    10

    Re: ListBox _change() command doesn't execute

    Quote Originally Posted by 6StringJazzer View Post
    You change the background color to RGB(255, 125, 255), and then immediately change it again to RGB(0, 125, 0) on the very next line of code. What color does it start as?
    I included the two command lines to show case that I tried both syntaxes. Before run time, it tried commenting one or the other to test each syntax: neither work.
    Reply With QuoteRegister To Reply
  5. 04-06-2020, 02:13 AM #5
    Kevin-S
    Kevin-S is offline
    Registered User
    Join Date
    04-02-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2019
    Posts
    10

    Re: ListBox _change() command doesn't execute

    Quote Originally Posted by jindon View Post
    Try BeforeUpdate or AfterUpdate event instead of Change.
    I'm afraid, neither of these appear as options in the object's dropdown of events ..:-/ 2020-04-06 (3).png
    Last edited by Kevin-S; 04-06-2020 at 02:40 AM.
    Reply With QuoteRegister To Reply
  6. 04-06-2020, 02:49 AM #6
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: ListBox _change() command doesn't execute

    The property get changed, you can see this if you revert back to design mode and check the listbox properties.

    The LostFocus event can be used. In order to lose focus you need another control.
    I don't see a method that would force the LostFocus event to fire.
    Cheers
    Andy
    www.andypope.info
    Reply With QuoteRegister To Reply
  7. 04-06-2020, 02:57 AM #7
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: ListBox _change() command doesn't execute

    Quote Originally Posted by Andy Pope View Post
    The property get changed
    Ahh, I didn't know that,,, thanks.
    Reply With QuoteRegister To Reply
  8. 04-06-2020, 09:36 AM #8
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,182

    Re: ListBox _change() command doesn't execute

    Quote Originally Posted by Andy Pope View Post
    The property [got] changed, you can see this if you revert back to design mode and check the listbox properties.
    I have experimented with this and confirmed that the property got changed, but it did not affect the display. The Change event does fire and changes the property. This is what prompted the OP to ask the question. If I change the property manually the display changes, but not if it changes in code through the Change event.

    This is inexplicable.
    Reply With QuoteRegister To Reply
  9. 04-06-2020, 10:08 AM #9
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,485

    Re: ListBox _change() command doesn't execute

    Try the mousedown event, appears to work in there.
    Reply With QuoteRegister To Reply
  10. 04-06-2020, 05:46 PM #10
    Kevin-S
    Kevin-S is offline
    Registered User
    Join Date
    04-02-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2019
    Posts
    10

    Re: ListBox _change() command doesn't execute

    In addition, when running the code from the debugger (pressing F8), I noticed that upon execution of .BackColor all tick-boxes are un-checked / reset => Whaaay?

    I copy/pasted the code and created a ListBox in a UserForm, thinking they are a bit more stable than ActiveX. The background color property is set yet not displayed..
    Last edited by Kevin-S; 04-06-2020 at 06:42 PM.
    Reply With QuoteRegister To Reply
  11. 04-06-2020, 06:20 PM #11
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    27,182

    Re: ListBox _change() command doesn't execute

    The UserForms use only ActiveX controls.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Can't execute 'Or' Command???
    By bralew in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-13-2017, 08:27 AM
  2. Can't execute an Other Than command
    By bralew in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2017, 05:27 AM
  3. VBA Code Doesn't execute if...
    By Cartaphilus in forum Excel General
    Replies: 3
    Last Post: 04-11-2015, 02:29 PM
  4. Sub procedure doesn't execute itself
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2013, 11:26 PM
  5. Using macro to execute a command in Shell Command
    By aadarsh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-19-2010, 07:06 PM
  6. VBA _Change() command Question
    By D3Pratt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-28-2009, 06:55 PM
  7. Excel doesn't execute the formula sometimes
    By mark_neil2 in forum Excel General
    Replies: 2
    Last Post: 02-24-2009, 08:44 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1