Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Combo Box data disappears after first use

    Hello Experts-

    I have a user form with 3 combo boxes and some text boxes

    The combo boxes are row sourced to the setup sheet

    The problem I need help with is with the combo boxes.
    After I enter data, I have VBA code to clear the text and combo boxes so a new row of data can be entered.
    Everything works fine for the first row, but when I start to enter the second row of data the combo boxes are empty.

    Is there code that will solve this problem?

    Any help provide is appreciated. Many hours searching for solution without success.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-07-15 at 16:36.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Try changing ctl.Text = "" to ctl.Value = "" and you should be good

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Excelnewbie (2015-07-15)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maudible-

    Once again, you solved what for me was a difficult problem. All with one word! THANKS

    BTW, if you see a better way to code a form with ComboBoxes than the way I am doing it, please let me know.
    Last edited by Excelnewbie; 2015-07-15 at 20:47.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Excel,

    Your form code is very efficient. Nicely done! The only thing that I could add is a naming convention where the controls have the same name plus an index (ctln) where n is the index so you could cycle through them.

    Example:

    From
    Code:
    'Moves data entered in form to worksheet
    ws.Cells(newRow, 1).Value = Me.cbLocation.Value
    ws.Cells(newRow, 2).Value = Me.txtAmount.Value
    ws.Cells(newRow, 3).Value = Me.txtAccountNumber.Value
    ws.Cells(newRow, 4).Value = Me.cbAccountType.Value
    ws.Cells(newRow, 5).Value = Me.cbTaxStatus
    ws.Cells(newRow, 6).Value = Me.txtInterestRate.Value
    ws.Cells(newRow, 7).Value = Me.txtWeissRating.Value
    ws.Cells(newRow, 8).Value = Me.txtNotes.Value
    To
    Code:
    'Moves data entered in form to worksheet
    For I = 1 To 8
        ws.Cells(newRow, I) = Me.Controls("ctl" & I).Value
    Next
    Where your textboxes and ComboBoxes are named ctl1 through ctl8

    HTH,
    Maud

  6. #5
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud,

    I desire to understand what you are teaching. Could you add more detail so I can change the code to what your have in mind.

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Excel,

    I like to name my controls using a schema with the same name followed by sequential number so that I am able to loop through them to perform various repetitive functions. ex ctrl1, ctrl2, ctrl3, etc..

    In your above code, you wanted to write the value of each control to the same row but adjacent columns. You can write a line of code for each control as you did or you can loop through them with the code I provided

    Code:
    'Moves data entered in form to worksheet
    For I = 1 To 8
        'INCREMENT COLUMNS,      INCREMENT CONTROL
        ws.Cells(newRow, I) = Me.Controls("ctl" & I).Value
    Next
    In the above code, for each value of I, the column that the value is placed in is defined as well as the name of the control. This is equivalent to your lines of code that increment the column using the next control's value.

    Code:
    'LOOPING CODE TRANSLETES TO....
    ws.Cells(newRow, 1).Value = ctl1.Value 'I=1
    ws.Cells(newRow, 2).Value = ctl2.Value 'I=2
    ws.Cells(newRow, 3).Value = ctl3.Value 'I=3
    ws.Cells(newRow, 4).Value = ctl4.Value 'I=4
    ws.Cells(newRow, 5).Value = ctl5.Value 'I=5
    ws.Cells(newRow, 6).Value = ctl6.Value 'I=6
    ws.Cells(newRow, 7).Value = ctl7.Value 'I=7
    ws.Cells(newRow, 8).Value = ctl8.Value 'I=8
    Hope that explains it better,
    Maud

  8. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    155
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud-

    Thank you for clarifying things.

    In your last post, I was reading "ct1" as "ctl", confusing the 1 and l.
    Last edited by Excelnewbie; 2015-07-16 at 20:00.

Posting Permissions

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