view and subtracting textbox from cell

[commo27]

I have made this userform shown below and I cant figure out how to code this correctly.

Combox1 is a drop down of column C of my "parts" sheet which is already working.

When a selection in combobox1 is made, i would like to see the value of column J in Listbox1

IE: combobox1 value is "123" as shown on row 2 of the parts sheet, then "10" from Column J Row 2 will be shown in Listbox1

A user enter a number into a number into textbox1 and when they click "remove", it should subtract the value in Listbox1 from Column J

Column J should also not go into the negative if this is even possible.

I appreciate all the help with this. This is my last and final step to this parts database.

 

[bensonsearch]

something like (i would use textbox not listbox but thats me)

under combobox.change event

if combobox1.value = "123" then
listbox1.value = thisworkbook.sheets("Parts").range("J2").value
end if

under remove button click event

dim count as integer
dim counta as integer
dim total as integer
 
count = listbox1.value
counta = textbox1.value
total = count - counta
if total <= 0 then
msgbox("Error less than 0")
else
 
if combobox1.value = "123" then
thisworkbook.sheets("Parts").range("J2").value = total
end if
end if

i havent tried the code but should be able to adapt it. you would have to elseif the other entries eg:456 in collum C. this is based on my assumption the parts dont change in how many you have (9 in the sheet below)

 

[commo27]

Thank you so much! It seems like i am almost there.

So the problem is parts do change all the time and this database will be filled with a few thousand parts

The combobox is set up that it will see all items in column C

When selecting a item in the combo box, the item in the same row in Column J should show up in the list box which i did change into a text box.

What you have works great but only for Row 2. How do i make it for any row that the combobox references?

 

[commo27]

Ok, I am very close now, it seems like the calculation is the only thing not working. This is what i have

Private Sub ComboBox1_Change()
    TextBox2.Value = ThisWorkbook.Sheets("Parts").Range("J" & ComboBox1.ListIndex + 2)
End Sub

This above parts works great...

I am not getting any errors on this next part but it wont subtract the amount.

Private Sub removebutton_Click()
Dim count As Integer
Dim counta As Integer
Dim total As Integer
count = TextBox2.Value
counta = TextBox1.Value
total = count - counta
If total <= 0 Then
MsgBox ("Error less than 0")
Else
 
If ComboBox1.Value = "" Then
ThisWorkbook.Sheets("Parts").Range("J" & ComboBox1.ListIndex + 2).Value = total
End If
End If
Unload Me
successfulremove.Show
End Sub

 

[commo27]

Ok, I have made another change.

They can remove until -1 and there is a successful userform that pops ups

I still can not get it to subtract.

Private Sub removebutton_Click()
Dim count As Integer
Dim counta As Integer
Dim total As Integer
count = TextBox2.Value
counta = TextBox1.Value
total = count - counta
    If total <= -1 Then
        MsgBox ("Error: You are removing more than there is in stock")
    Else

    If ComboBox1.Value = "*" Then
        ThisWorkbook.Sheets("Parts").Range("J" & ComboBox1.ListIndex + 2).Value = total
    End If
        removepart.Hide
        successfulremove.Show
    End If
End Sub

 

[bensonsearch]

Ok, I have made another change.

They can remove until -1 and there is a successful userform that pops ups

I still can not get it to subtract.

Private Sub removebutton_Click()
Dim count As Integer
Dim counta As Integer
Dim total As Integer
count = TextBox2.Value
counta = TextBox1.Value
total = count - counta
    If total <= -1 Then
        MsgBox ("Error: You are removing more than there is in stock")
    Else
 
    If ComboBox1.Value = "*" Then
        ThisWorkbook.Sheets("Parts").Range("J" & ComboBox1.ListIndex + 2).Value = total
    End If
        removepart.Hide
        successfulremove.Show
    End If
End Sub

i would remove the second if statement. simply

Private Sub removebutton_Click()
Dim count As Integer
Dim counta As Integer
Dim total As Integer
count = TextBox2.Value
counta = TextBox1.Value
total = count - counta
    If total <= -1 Then
        MsgBox ("Error: You are removing more than there is in stock")
    Else
 
    
        ThisWorkbook.Sheets("Parts").Range("J" & ComboBox1.ListIndex + 2).Value = total
    
        removepart.Hide
        successfulremove.Show
    End If
End Sub

give that a try (the only reason it was there was to know which part to minus from. you have that now in the combobox listindex so no need for it)

 

[commo27]

Thats perfect!!! Thank you soo much..I played around with that for about 6 hours on and off and i was soo close

 

[bensonsearch]

Thats perfect!!! Thank you soo much..I played around with that for about 6 hours on and off and i was soo close

Not a problem at all. Glad to help whenever and where ever I can but yes you were very close which is good.