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

    Update Data in Worksheet with Userform

    Hi Experts,

    Thanks for all that you do.

    I have data in a worksheet. I am using a form to edit the existing data in the worksheet. I am hoping to use code like this to make it happen. I think I am close, by so far "no cigar".

    Any help you could provide would be appreciated.


    Private Sub CbUpdate_Click()


    Dim Code As String
    Dim Currentrow As String
    Code = TbCodeExample.Text
    Cells(Currentrow, 2).Value = Code


    End Sub

    I've attached a file to help you see what I hope to accomplish.
    Attached Files Attached Files
    Last edited by Excelnewbie; 2015-11-16 at 17:54.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    You haven't initialized CurrentRow. If the cursor (active cell) is currently located in the current row you can replace CurrentRow with ActiveCell.Row in the assignment and ditch the Dim statement for CurrentRow.

    If the cursor isn't in the current row then you need to tell us how to find it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    RG-Sorry, I still haven't been able to make the code work. It may have something to with the combo box I'm using.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Excel,

    Change the code line in question from:

    Cells(Currentrow, 2).Value = Code

    to

    Cells(CBChooseCode.ListIndex + 2, 2).Value = Code

    When the user makes a selection from the combobox, the ListIndex is the order number of the selected choice starting with zero. So, if you select "Information", the listIndex=0 Plus 2 gives the current row to place the value of Code

    HTH,
    Maud

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

    I didn't do a good job explaining what I am attempting to do.

    I have two data fields on the worksheet.

    In Column A (Reference) I enter data to describle something general, like clothing.
    In Column B (Information) I enter details, like shirt, or pants.

    I need to be able to change or edit what is in column B after using the combo box to local data in column A.

    For example, I might need to add shirt sizes to what is already in column B

    Column A Column B
    Clothing Shirts for men

    After updating:

    Column A Column B
    Clothing Shirts for men, sizes Small to Extra Large

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Excel,

    You did explain it very well. The code change I presented is what you need.

    Maud
    Last edited by Maudibe; 2015-11-16 at 23:46.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud,

    I inserted the code you kindly provided. Thank you.

    When I click the update button, it moves the contents of column A into Column B. It should update column B with what is entered into the textbox (TbCodeExample) on the form.

    I tried changing the numbers in the code with the hopes of finding a solution, but so far nothing is working.

    Thanks for you patience with this thread.

    I attached another file.
    Attached Files Attached Files

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Excel,

    Here is what I am seeing when using your file in the opening post.

    Following the procedure you specified and replacing your code line with mine:
    1. Click VBA Code Form
    2. Use comb box to access worksheet data.
    3. Click Find Code to view data in form.

    Excel1.png

    4. Edit the data in the form by adding something to it, "like All good menů."
    5. Click the update button and it should change the data in b2

    Excel2.png

    Excel3.png

    Try widening column B and you will see the added data appended in B2

    Maud

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Excel,

    You changed the code so it will not function correctly.

    Original code (update-1.xlsm) with my code line replacing yours:
    Code:
    Private Sub CbUpdate_Click()
    
    Dim Code As String
    Dim Currentrow As String
    
    Code = TbCodeExample.Text
    Cells(CBChooseCode.ListIndex + 2, 2).Value = Code
    
    End Sub
    Your revised code (update-2.xlsm) with an altered line (blue):
    Code:
    Private Sub CbUpdate_Click()
    
    Dim Code As String
    Dim Currentrow As Long
    
    Code = CBChooseCode.Text
    Cells(CBChooseCode.ListIndex + 2, 2).Value = Code
    
    End Sub

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

    Excelnewbie (2015-11-17)

  11. #10
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Maud,

    I'm so sorry! Thanks for pointing out my error. I forgot I changed the code at some point while working with your code. Many wasted hours of work, and worse, I wasted your time too.

    I going to remember this error for a long time.

    I'm studying Excel. Fortunately, I'm not on a payroll. OK, problem solved. Back to work.

  12. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Np Excel. Your gains in vba are most impressive.

Posting Permissions

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