Update Data in Worksheet with Userform

Topic

New Reply

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.

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Excel,

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

Maud

Reply | Quote

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.

Reply | Quote

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.

42675-Excel1

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

42676-Excel2

42677-Excel3

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

Maud

Reply | Quote

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

[COLOR=”#0000FF”]Code = CBChooseCode.Text[/COLOR]
Cells(CBChooseCode.ListIndex + 2, 2).Value = Code

End Sub

Reply | Quote

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. :blush:

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

Reply | Quote

Np Excel. Your gains in vba are most impressive.

Reply | Quote