+ Reply to Thread
Results 1 to 19 of 19

Refresh a dynamically created listbox object on a dynamically created userform

  1. #1
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Refresh a dynamically created listbox object on a dynamically created userform

    Hi all

    First time poster so please forgive any toe treading that happens.

    The attached sheet shows a snippet of the worksheet I'm using to create a userform on the fly.

    The code creates the objects on the form according to the Type in Row 1. If the Type is a listbox, the values are populated using the contents of a named range in row 2. This range is dynamic and will grow every time an Org_List entry is added.
    The code also creates a set of events dynamically so I can trap a MyListBox_Change() event for the "Referred From" listbox.

    This allows me to populate the Named Range value for "RFCOtoKpoc", "Referred_From_Calling_Org_to_Key_point_of_contact". This works fine, populating the Value with Contacts_Org2 when Org2 is Selected in the "Referred From" list box.

    On entry to the "Key point of contact" listbox, I'm able to get the "Contacts_Org2" value from RFCOtoKpoc, as indicated by the "LinksFrom" row. This is then set to the MyListBox.RowSource. All good so far. I've also used direct Range() references in case there's an issue with setting that property.

    What I am unable to do is refresh the list in the "Key Point Of Contact" listbox to reflect the contents of the Contacts_Org2 list from that named range.

    When in the procedure where I'm attempting the refresh, I can successfully get MyListBox.Name, MyListBox.RowSource, and any number of properties. Trying any listbox method such as MyListBox.Clear, fails with

    Run-time error '-2147467259 (80004005)':

    Unspecidied error

    The code snippet is below.

    I'm currently resorting to contemplating re-building the second list box or rebuilding the form entirely. I don't yet know how to remove the second list box and re-building the form completely seems very messy.

    Any and all comments appreciated.


    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by mhwalmsley; 08-02-2024 at 03:53 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: How to refresh a dynamically created listbox object on a dynamically created userform

    Welcome to the Forum mhwalmsley !

    The file you attached is not macro-enabled, and the code is just a fragment so we can't run it. I would suggest attaching the full file you are working with so we can see the form and all of the code.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi Jeff

    I'm trying to send you an update but am not able to add attachments to the reply. In fact the Postings Permissions pane tells me

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

    which makes things a little tricky.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Something is definitely wrong there. I'll contact support. You should be able to do everything except post live links.

  5. #5
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi

    Have the support guys made any progress with my account settings? I'd like to post the full file with all the sheets and macros to help you investigate my question.

    Thanks

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    My posting permissions pane says exactly the same thing - it's wrong, so ignore it. I see you have already successfully attached a workbook, so what follows may be moot.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

    If the workboiok is too big (over 1MB), then either save it as .xlsb or ZIP it.
    Last edited by AliGW; 08-03-2024 at 03:37 AM. Reason: Additional information added.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    As Ali says, that message does not reflect reality. Support told me there is no issue with your account. I'll follow up about why the permissions say that (for everybody?) but meantime it does seem that you posted a file.

  8. #8
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Thanks Ali for confirming that the "Postings Permissions" status values are erroneous.

    I am writing this in the "Reply To Thread" dialog, as confirmed by the instructions on
    attachments. When using the Manage Attachments process, I am still getting an error, specifically

    Please Login or Register  to view this content.

    Consequently I've created a share link for the file, but I'm getting a message that I can't post links.

    Is there any alternate way I can get you these data. I'm tryinh to break the message with spaces to see if it will get through.
    https://www.dropbox.com/scl/fi/zuo3f...8nt0zot3i&dl=0
    I hope this is sufficient. If not, I'm happy to hear suggestions.

    Many thanks

    Martyn
    Last edited by 6StringJazzer; 08-03-2024 at 05:49 PM. Reason: converted URL to live link

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    I have converted your URL to live link, but Dropbox prompts to request access. You may want to change permissions to "anyone with a link".

    I don't know why you got that error. If I can get access to the file I'll attach it for you.

  10. #10
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi Jeff

    I've granted you access to the file. Thanks for the offer to attach the file.

    Link removed - workbook attached. AliGW

    Thanks for your help.

    Best regards

    Martyn
    Last edited by AliGW; 08-04-2024 at 05:37 AM. Reason: URL added.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,072

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Here's the workbook.
    Attached Files Attached Files

  12. #12
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2407
    Posts
    25,974

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    I was able to reproduce your error by manually invoking the code. This approach of dynamically creating a UserForm then using classes to manage the events is a fairly complicated bit of business. It seems clear enough but it creates multiple layers that present challenges when diagnosing an error like this.

    I am also a little puzzled that you have a class called MyEvents and a Collection called MyEvents. You are adding objects of class MyEvents to the Collection called MyEvents. This is confusing at best.

    Sub Workbook_Open() is not in the ThisWorkbook module so I'm not sure how you plan to invoke it.

    The "Unspecified error" has me stumped. I'm not sure how to debug this since it's kind of a "virtual" listbox for an event handler and not a physical listbox. I'll take a further look at the code and see if there something else that could be affecting this that isn't at the point where the error occurs.

  13. #13
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi Jeff

    There are things which are not as per the final intended solution here (Sun Workbook_Open() ...etc).

    I've been able to evidence, through Debug.Print statements and adding a watch on MyListBox, that the RowSource is updating as expected.

    If I can find a solution for listbox 2 population to reflect the change to listbox 1 so that the user can pick from the contact list with the associated organisation.

    Regards

    Martyn

  14. #14
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,780

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    If you set a rowsource, you can't use Clear or List without clearing the rowsource first.
    Everyone who confuses correlation and causation ends up dead.

  15. #15
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi RomperStomper

    Thanks for your reply.

    It doesn't, unfortunately, solve for my problem, unless I'm (quite possibly) missing something.

    I have two ListBox controls, "Organisation" and "Contacts", lbOrganisation and lbContacts respectively. lbOrganisation has a 2 column Named Range associated with it but displays only Column 1. Column 1 contains the name of the organisation, column 2 the name of the Named Range containing the list of contacts for that organisation.

    So
    Organisation
    Org1_Name Org1_Contact_List
    Org2_Name Org2_Contact_List
    Org3_Name Org3_Contact_List

    The Org1_Contacts_List to Org3_Contact_List values are the names of Named Ranges thus

    Org1_Contact_List
    Contact 1 at Org 1
    Contact 2 at Org 1
    Contact 3 at Org 1

    Org2_Contact_List
    Contact 1 at Org 2
    Contact 2 at Org 2

    Org3_Contact_List
    Contact 1 at Org 3
    Contact 2 at Org 3
    Contact 3 at Org 3
    Contact 4 at Org 3
    Contact 5 at Org 3

    If I select Org2_Name in lbOrganisation, I want to display Org2_Contact_List in lbContacts.

    I'm able to set the lbContacts.RowSource with the new Named Range I need.

    My challenge(!) is getting the lbContacts control on the form to display

    Contacts
    Contact 1 at Org 2
    Contact 2 at Org 2

    The default value for lbOrganisation is Org1_Name and lbContacts displays the Org1_Contact_List as expected on entry to the form. I cannot find any way of changing the displayed list in lbContacts when changing the selection lbOrganisation e.g. setting lbOrganisation "Org2_Name" causing lbContacts to display the contents of the Org2_Contact_List.

    Selecting "Contact 2 at Org 1" on lbContacts having changed lbOrganisation to Org2_Name and submitting the form saves "Contact 2 at Org 2" to the underlying sheet. The correct RowSource is being set on changing lbOrganisation and the correct list index value is being saved.

    The only thing lacking is being able to display the contents of Org2_Contact_List to the user so that they can see what they are selecting.

    If you listen very carefully, you may hear this distant echo of me banging my head against the nearest wall over this.

    Any help you or anyone in the community can offer is deeply appreciated.

    Best regards

    Martyn

  16. #16
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,399

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi Martyn, there are far simpler methods to achieve two co-related lists than using the construct of a dynamic form.
    Rarely is there any reason/benefit in building a form 'on the fly' other than learning the process - the vast majority of cases can be solved more simply by constructing the form at 'design time'.
    The code construct and comments leads me to believe this is from a 'tutorial', can you supply a link to this if this is the case.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  17. #17
    Registered User
    Join Date
    07-30-2024
    Location
    Tunbridge Wells, England
    MS-Off Ver
    Professional Plus 2021
    Posts
    8

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi torachan

    Thanks for your reply. You're absolutely right about the ability to create data links at design time and, in other circumstances, this is what I've done. However, as a solution, that delivers a static form and, since requirements change over time, the sheets and, therefore, the forms supporting them, would need updating from time to time.

    I'm trying to provide a mechanism which will allow the user, a small, financially constrained charity, to have control over what I'm delivering for them. The intent is that they will be able to add additional columns to the sheets on which they will be storing their data which will be reflected in the 'on-the-fly' generated forms. This extensible solution will future proof them against having to pay for additional time from myself and de-risk me not being available.

    The code is all of my own making, although I've been researching the process as I've been building it and have drawn inspiration from a number of threads from this and other forums. In that sense, there is no singular source. The delivered solution will have around 10 different forms, reflecting the customer's various data needs and I merely extracted a subset of data from one sheet to evidence the issue I've been having.

    Short of doing a .Remove, to take the initial "Key point of contact" out of the Controls collection for the form and then re-adding it, which then picks up the newly assigned RowSource but feels clunky, I've not found a way to display the new list of values in the listbox, even though they are assigned an any changes in list position does select for and save those data.

    A fun challenge.

    I understand your aversion to merged cells. They REALLY mess with automation in so may ways, and don't even get me started on their impact on .Find() instructions!!!

  18. #18
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,399

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Hi Martyn, I have contributed to the link below - some of the content could be useful to your particular query.
    The attached file is one from my archive - it constructs multiple listboxes 'on the fly' at initialisation stage - a technique that may answer your problem - to see the expansion work create another structured table in column 'CM' - this will dynamically create an extra tab on the multipage with a listbox containing the extra data.
    Also I do not use 'rowsource' in any of my apps as it is the most unstable method of populating data to and from sheet (especially if you have two or more workbooks open).
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,524

    Re: Refresh a dynamically created listbox object on a dynamically created userform

    Quote Originally Posted by torachan View Post
    Also I do not use 'rowsource' in any of my apps as it is the most unstable method of populating data to and from sheet .
    That's probably what's causing the unspecified error.

+ 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] Control dynamically created Userform controls via class module
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-16-2018, 01:12 PM
  2. [SOLVED] Exit event for Dynamically created textbox in a userform
    By Arkadi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2018, 01:29 PM
  3. [SOLVED] Userform - Cannot transfer value of textbox to range when created dynamically
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-15-2016, 10:15 AM
  4. place dynamically created controls in row and column into the userform with in the frame
    By gokulkrishi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2016, 01:38 AM
  5. Replies: 0
    Last Post: 06-22-2014, 04:18 PM
  6. [SOLVED] userForm -> Frame 'top' value limited. Dynamically created controls,
    By kropeck in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 08-19-2013, 09:04 AM
  7. Assigning macros to a dynamically created userform
    By moosemaster23 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-19-2013, 07:42 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