Can not get code to work

JohnsonK · Oct 12th 2020

Hi,

I have a userform with a few textboxes and comboboxes some that self populate from a data base, what I need it to do is if they select OUT from combobox 1 the textbox self populates from a database they then choose an amount from the combobox 2 lets say 5 then when the command button is clicked it then finds a match from the worksheet named Data that matched the text from textbox 1 and adds the value from combobox 2 in to the worksheet. I have attached a little mock up and filled the values in so you only need to click the command button. I can not seem to get it to work, any help would be great.

Hope this make sense..

Code

Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim m As Variant
With ThisWorkBook.WorkSheets ("Data")
m = Application.Match(Val(Me.TextBox1.Text), .Columns(1), 0)
If Not IsError(m) Then
With .Cells(CLng(m), "D")
If ComboBox1.Value = "OUT" Then
.Value = .Value + Val(ComboBox2.Value)
Else
With .Cells(CLng(m), "E")
If ComboBox1.Value = "IN" Then
.Value = .Value - Val(ComboBox2.Value)
End If
End With
End If
End With
End If
End With
Application.DisplayAlerts = True
End Sub

Display More

dangelor · Oct 12th 2020

Possibly...

Code

Private Sub CommandButton1_Click()
Dim m As Variant
'update Data
With ThisWorkbook.Worksheets("Data")
m = WorksheetFunction.Match(TextBox1.Text, .Columns(1), 0)
If ComboBox1.Value = "OUT" Then
With .Cells(CLng(m), "D")
.Value = .Value + ComboBox2.Value
End With
ElseIf ComboBox1.Value = "IN" Then
With .Cells(CLng(m), "E")
.Value = .Value - ComboBox2.Value
End With
End If
End With
End Sub

Display More

JohnsonK · Oct 13th 2020

dangelor,

That works perfect thank you so much.

JohnsonK · Oct 13th 2020

Hi,

Just one more thing if you don't mind, if the user selects IN from combobox 1 and when textbox 1 has auto populated how would I go about showing the row that it has matched in a list box. Would it go something like

Code

Private Sub TextBox1_Change()
Dim m As Variant
'update Data
With ThisWorkbook.Worksheets("Data")
m = WorksheetFunction.Match(TextBox1.Text, .Columns(1), 0)
if ComboBox1.Value = "IN" Then
'I need to show that matched row of data in ListBox1
End Sub

Display More

dangelor · Oct 13th 2020

Not that familiar with UserForms, but possibly...

Code

Private Sub TextBox1_Change()
Dim m As Variant, mArray As Variant
'update Data
With ThisWorkbook.Worksheets("Data")
m = WorksheetFunction.Match(TextBox1.Text, .Columns(1), 0)
If ComboBox1.Value = "IN" Then
mArray = Application.Index(.Cells(m, 1).Resize(, 6).Value, 1, 0)
ListBox1.List = mArray
End If
End With
End Sub

Display More

If this isn't a solution, I suggest starting a new thread.

JohnsonK · Oct 13th 2020

dangelor,

First of all thank you for all your help so far.

It works but the only problem is it just keeps showing me the data from the last row all the time no matter if I choose IN or OUT, it is not showing the data for the match from textbox 1

dangelor · Oct 13th 2020

As I suggested, start a new thread so those more familiar with UserForms may help.

JohnsonK · Oct 13th 2020

Ok thank you.

Can not get code to work

Share

Similar Threads

Closing a Word document without saving

Replace Col reference with Variable using "Countif formula" in For Loop

Loop through data validation list

Userform Listbox header display

AutoFilter Multiple Sheets, Copy, New Book, Paste to Multiple Sheets