+ Reply to Thread
Results 1 to 5 of 5

Add to a multicolumn listbox using two textboxes and one command button

Hybrid View

  1. #1
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Angry Add to a multicolumn listbox using two textboxes and one command button

    Dear Excel-gurus,

    Having prowled the internet for well over an hour trying to find the solution, I come to you for help.

    The situation is as follows: users add data to a userform which, once all is completed, produces a PDF document. One of the parts is to fill in terms and abbreviations used in the document. A number of which are always required, even more can be selected by the user and finally the user has the option to add non-standard terms and abbreviations to the document.

    The userform therefore, amongst other buttons and things, has:
    a 2-column listbox which needs to be populated automatically for the standard T&As;
    a 2-column listbox for the optional T&As which the user selects using the multiselect option;
    a textbox for the non-standard T&A shortcut;
    a textbox for the non-standard T&A description;
    a button for adding the non-standard, user-defined T&A (which is typed into the two textboxes);
    a button for removing T&As from the top listbox.

    The idea is that the main listbox be the one copied by VBA to the final document.

    I have tried the commands .AddItem, .List, .Column, .RowSource, et cetera, but cannot find a working code. Like other people, when using RowSource, I get an access error.

    Here are a few things I've tried:

    Private Sub cmdTermsAbbsManualAdd_Click()
    
    'ListBoxTermsAbbs.Column(1).AddItem TextBoxTermsAbbs.Value
    'ListBoxTermsAbbs.List(ListBoxTermsAbbs.ListCount - 1, 0) = TextBoxTermsAbbs.Value
    'ListBoxTermsAbbs.Column(2).AddItem TextBoxTermsAbbsDescription.Value
    'ListBoxTermsAbbs.List(ListBoxTermsAbbs.ListCount - , 1) = TextBoxTermsAbbsDescription.Value
    'ListBoxTermsAbbs.AddItem (TextBoxTermsAbbs.Value)
    'ListBoxTermsAbbs.AddItem TextBoxTermsAbbsDescription.Value
    
    Dim MyArray(1, 2)
    MyArray(0, 1) = TextBoxTermsAbbs.Value
    MyArray(0, 2) = TextBoxTermsAbbsDescription.Value
    ListBoxTermsAbbs.List() = MyArray
    
    
    End Sub
    TermsAbbsUserform_edit.png
    Last edited by ChrisPatterson; 10-27-2014 at 06:38 AM. Reason: Added image

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

    Re: Add to a multicolumn listbox using two textboxes and one command button

    You need something like:
    Private Sub cmdTermsAbbsManualAdd_Click()
    
    With ListBoxTermsAbbs
       .AddItem TextBoxTermsAbbs.Value
       .List(.ListCount - 1, 1) = TextBoxTermsAbbsDescription.Value
    End With
    End Sub
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Add to a multicolumn listbox using two textboxes and one command button

    Romperstomper,

    Thanks again for another swift reply. Unfortunately I get the Run-time error '70': Permission denied. error, which, if I understand correctly, might have to do with having:

    Private Sub UserForm_Initialize()
    
    ListBoxTermsAbbs.RowSource = "TermsAbbsFixed"
    
    End Sub
    When the listbox is not automatically filled this way your code works.
    Last edited by ChrisPatterson; 10-27-2014 at 06:48 AM. Reason: Extra comment

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

    Re: Add to a multicolumn listbox using two textboxes and one command button

    Yes - you can't use Rowsource if you plan to add items - unless you add them to the source range.

  5. #5
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Add to a multicolumn listbox using two textboxes and one command button

    Fixed by not using RowSource.

+ 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. Multicolumn dynamic Listbox - Remove Duplicates in Listbox
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2014, 12:30 PM
  2. Having problems populating a multicolumn listbox changing Listbox column with a loop.
    By Aristizabal95 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2013, 12:41 PM
  3. Copy Selected items from multicolumn, multiselect listbox to another listbox
    By Willigb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2013, 11:27 AM
  4. Can a command button make textboxes dissapear from a user form
    By shakey775 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-19-2012, 03:01 AM
  5. Command button to calculate textboxes
    By zplugger in forum Excel General
    Replies: 1
    Last Post: 01-12-2012, 02:52 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