User Form controls still executing after Unload
Results 1 to 4 of 4

Thread: User Form controls still executing after Unload
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default User Form controls still executing after Unload

    I am using a UF that the user enters details on that need to be appended to a range in a worksheet.

    Once the data has been added and pasted into a holding worksheet, the UF is closed via an Unload commande but when some of the non UF code is executing, the code for some controls are executing.

    E.g.
    Code:
    strCategory = rngGoodsOut.Offset(0, 0)
       strPerson = rngGoodsOut.Offset(0, 1)
       dtDateOut = rngGoodsOut.Offset(0, 2)
       strItem = rngGoodsOut.Offset(0, 3)
       lngQty = rngGoodsOut.Offset(0, 4)
    All this is in a module and not behind the UF and each of the variables refer to items that were addedin the UF so strPerson was added via a combobox, dtDateOut was added via a textbox.

    Now when each of these lines of code execute the combobox control for selecting the person executes 'cbPerson_Change'.

    I have no clue as to why this is happening and wondered if anyone else has experienced this odd behavior.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User Form controls still executing after Unload

    If I read what you are describing correctly, it appears that you initialized variables located in a public code module with values entered via a UF. Those variables do not "unload" when the UF unloads. Only the variables in the UF code module are destroyed when the form unloads. So the varibles in a public code module will hold the value until they are assigned new values or the procedure ends, whichever comes first.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: User Form controls still executing after Unload

    Ah!!!

    So if I am needing to assign a control value to a variable (CbPersoron to strPerson), how do I do that without declaring strPerson as public?

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,952
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: User Form controls still executing after Unload

    Quote Originally Posted by mikeymay View Post
    Ah!!!

    So if I am needing to assign a control value to a variable (CbPersoron to strPerson), how do I do that without declaring strPerson as public?
    I am not sure what you are asking, but this is how it works.
    Variables assigned inside a macro live and die with the macro.
    Variable values can be passed from one macro to another and will live in the receiving macro until it completes at which point the varibles die.
    Public variables can be used by any macro and their values are determined based on how they are declared, eg. some values of public variables might change during runtime of macros, but constants do not change during runtime of macros.
    Values for variables passed from one code module to another must be passed by reference.

    Values for controls on a UF can be used in variables in pulbic marcros if the UF is open during the runtime of a public macro,

    Don't know how much help this will be, but it is the basic guide as I understand it.
    Last edited by JLGWhiz; Jul 23rd, 2019 at 11:27 AM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •