+ Reply to Thread
Results 1 to 13 of 13

GotFocus event won't trigger

  1. #1
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    GotFocus event won't trigger

    Hello Excel Forum,

    I have a worksheet that contains an ActiveX ComboBox, for which I have written some code to populate the drop-down list:

    Please Login or Register  to view this content.
    This code was working as intended earlier in my project's development but now the event doesn't even trigger; I've tested this by placing a breakpoint in the first line of code but the debugger does not open. This piece of code is unchanged from when it was working.

    I've looked around the VBE environment and have tried searching online for potential issues, but I cannot find the reason the code is no longer executing (as if the control is not receiving focus). Does anyone have any idea as to why, typically, a form control (on a worksheet) may stop receiving focus?

    All suggestions will be very much welcomed!

    Thanks
    Andrew

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: GotFocus event won't trigger

    Should work, you're not in design mode? There are also some ActiveX options in the trust center settings, also its definately called that, try rightclicking on it in design mode and view code, see where it takes you.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

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

    Re: GotFocus event won't trigger

    Why not populate the comnobox using another event, for example the worksheet Activate event?

    If you use one of the combobox's own events the user might notice a small lag while the code is running to populate it.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: GotFocus event won't trigger

    thanks for your quick response, nathansav. I'm not in design mode and the control and corresponding code is named correctly (right-clicking on the control in design mode, 'view code' takes me to the correct code).

    Is it likely that ActiveX options have been changed automatically? I ask because I haven't changed any from the point at which my code was working as intended.

  5. #5
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: GotFocus event won't trigger

    You ask a good question, Norie. The reason is that the contents of the ComboBox (it's list items) may change depending on the contents of other boxes, as I'll try to explain.

    On the worksheet I have two ComboBoxes: "cb_cpn" and "cb_company". The idea is that users can use these to select a row of data from another worksheet, by either campaign or company. So, if, at first, the user selects 'Campaign A' from "cb_cpn", they are then able to select companies 1 - 8. Conversely, if a user selects "Company 5" from the "cb_company" control, they may only select "Campaign A" or "Campaign C" from cb_cpn" (see image below). Additionally, the source data in the hidden sheet will be changed indirectly by the user throughout operation.

    cpn comp.png

    Does that make sense? If so, is there a better way to achieve the same (I must say that, throughout my own testing, I could not notice any lag -- that is, when it was working).
    Last edited by williams485; 07-17-2014 at 10:40 AM. Reason: added better description of functionality and incl. image.

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

    Re: GotFocus event won't trigger

    I thought that might be the case, and what I'd suggest is that if a combobox is dependent on another combobox/control put the code for populating the dependent combobox in the 'parent' combobox/control.

    So in your case you would put the code to populate cb_company in the change event of cb_cpn.

    PS Hope I got that the right way round.

  7. #7
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: GotFocus event won't trigger

    OK; thanks, Norie. I'll try that. What if, however, it is useful for both to be 'parent' controls, where if a user selects a company, it gives the available campaign options.

    However, I'm still not sure what changed to make the event stop calling, given that it worked fine up until a point (unfortunately, I've been unable to trace that point!). Effectively, neither control currently gets focus. Any ideas as to what might have affected this?

    thanks

  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: GotFocus event won't trigger

    Sorry I've don't really know what could have caused that to happen.

    Has anything changed in the sheet?

    Is it protectd/locked in any way?

    As for both comboboxes being 'parent' controls, I don't see a problem with that.

  9. #9
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: GotFocus event won't trigger

    There have been a few changes in the sheet but none that I can think of that would change the behaviour/responsiveness of events. Interestingly, I've just noticed that my 'Worksheet_SelectionChange' event no longer works. I don't think it's the worksheet module, because a have a couple of button controls that still work as intended.

    There is no protection / lock on the sheet.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: GotFocus event won't trigger

    Do you use code like this anywhere?

    Please Login or Register  to view this content.
    Otherwise I would restart excel and see if any event capture returns.
    Cheers
    Andy
    www.andypope.info

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

    Re: GotFocus event won't trigger

    If SelectionChange isn't working then it could mean events are disabled, which isn't something I would have thought would affect controls.

    Anyway, try enabling events by entering this in the Immediate Window in the VBE.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    08-21-2012
    Location
    Crewe, England
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: GotFocus event won't trigger

    Thanks, Andy and Norie. I decided to close and re-open Excel (incidentally, before I'd read your latests posts) and found all of my code to be working as expected. I'm pretty certain I haven't used any code to disable events but I will keep an eye out.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: GotFocus event won't trigger

    GotFocus is one of the few ActiveX events that is affected by EnableEvents.
    Remember what the dormouse said
    Feed your head

+ 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] Trigger Change Event
    By alienware in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2012, 07:25 AM
  2. GotFocus event for Frames
    By owainl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2008, 08:26 AM
  3. Event trigger in Excel?
    By Tim Miller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2006, 04:10 PM
  4. [SOLVED] Event Trigger
    By lobo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-16-2005, 04:35 PM
  5. [SOLVED] Trigger Event Code
    By Shawn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2005, 10:05 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