Need help with the VBA in a Combobox in a user form

Sudooth · Jan 27th 2021

Hi,

New on here and really need someone to help concerning this.

I have a Userform that has a combobox which is picking up values in my admin sheet for stock.

I am okay with VBA but do not have a clue how to include the combobox where you can choose the product and then either add a value or take one away.

This is the code I have so far but adds value to the whole range not the individual product cell.

Code

Private Sub AddVal_Click()
For Each r In Range("C2:C300")
r.Value = r.Value + 1
Next r
End Sub
Private Sub Allocatestock_Click()
For Each r In Range("C2:C300")
r.Value = r.Value - 1
Next r
End Sub
Private Sub Cls_Click()
Stockform.Hide
End Sub

Display More

I have attached the sheet, any help would be amazing thank you.

Thank you in advance.

**royUK** · Jan 27th 2021

That code adds the value to every item, including the empty cells to 300

Sudooth · Jan 27th 2021

Hi RoyUK,

Thank you for your quick reply.

Yeah I know, I just don't know how to write the vba to choose the product and add the value I choose to that product if that makes sense.

Thanks

**royUK** · Jan 27th 2021

I've amended your code

Code

Private Sub AddVal_Click()
With Sheet1.Cells(CInt(Me.Product.ListIndex) + 2, 3)
.Value = .Value + CInt(Me.qty.Value)
End With
End Sub
Private Sub Allocatestock_Click()
With Sheet1.Cells(CInt(Me.Product.ListIndex) + 2, 3)
.Value = .Value - CInt(Me.qty.Value)
End With
End Sub
Private Sub Cls_Click()
Me.Hide
End Sub

Display More

When working with UserForms, use Me not the UserForm name when referring to it in the UserForm's Code Module.

Sudooth · Jan 27th 2021

Thank you so much RoyUK it works perfectly.

Do you have any advice on how to learn VBA better or is it more learn by doing.

**royUK** · Jan 27th 2021

There's lots of Tutorials around, check out some of mine on my web site. There's some useful VBA articles to get you started and some examples to download. I've also posted many userforms here at Ozgrid

I learned by looking at examples and working my way through the code.

Sudooth · Jan 27th 2021

Thanks again RoyUK you have been a great help.

I will look at your website now and carry on looking at examples.

Thanks

**royUK** · Jan 27th 2021

Pleased to help. You can always ask questions here if theres anything you do not understand.

Sudooth · Jan 28th 2021

Hi RoyUk,

Thank you for yesterday,

Can i ask, do you know how to fix this issue below, I keep getting it everytime I try connect to teradata using vba?

**royUK** · Jan 28th 2021

I don't really know. I think your workbook imports data form the web. Check the query

Sudooth · Jan 28th 2021

Thank you I will have a look now.

Need help with the VBA in a Combobox in a user form

Share

Tags