Listbox column help

CJamesUK · Oct 2nd 2020

newbie to vba im having trouble with listboxs and populating them with the info i need in the correct format.

So i already have 1 listbox (lstResults) which the information in it is taken from a selected item from a combobox (cboxType)

from lstResults i click an item and them in a separate textbox i want to add an amount eg 10 then with a commandbutton add the lstResults information and textbox amount to another listbox in different columns in the same row and then be able to add more with more selections.

ive nearly got it working but it just over writes the last entry.

Code

Private Sub cmdAddItem_Click()
If txtamount.Value = "" Then
MsgBox "Please enter a value"
Exit Sub
Else
' Code if not empty
End If


With Me.ListBox1
.AddItem Me.lstResults.Value
.AddItem Me.txtamount.Value
End With


End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cboxType_Click()
Dim X As Integer


X = cboxType.ListIndex


Select Case X
Case Is = 0
lstResults.RowSource = "Hinge"
Case Is = 1
lstResults.RowSource = "FlushBolt"
Case Is = 2
lstResults.RowSource = "Lock"
Case Is = 3
lstResults.RowSource = "IntumescentJacket"
Case Is = 4
lstResults.RowSource = "Escutcheon"
Case Is = 5
lstResults.RowSource = "Cylinder"
Case Is = 6
lstResults.RowSource = "PullHandle"
Case Is = 7
lstResults.RowSource = "RTDHandle"
Case Is = 8
lstResults.RowSource = "DoorCloser"
Case Is = 9
lstResults.RowSource = "PushPlate"
Case Is = 10
lstResults.RowSource = "KickPlate"
Case Is = 11
lstResults.RowSource = "Signage"
Case Is = 12
lstResults.RowSource = "ThumbTurnIndicator"
Case Is = 13
lstResults.RowSource = "DigitalLock"
Case Is = 14
lstResults.RowSource = "CylinderPull"
Case Is = 15
lstResults.RowSource = "FloorSocket"
Case Is = 16
lstResults.RowSource = "DoorStop"
End Select


End Sub
Private Sub lstResults_Click()
'dim the variables
Dim i As Integer
Dim fPath As String
Dim Pic As String
'set the file path
fPath = ThisWorkbook.Path & "\" & "Ironmongery Images"
i = Me.lstResults.ListIndex
On Error Resume Next
'display the picture
Me.Image1.Picture = LoadPicture(fPath & "\" & Me.lstResults.Column(0, i) & ".jpg")
'If picture is not available
If Err = 53 Then
Me.Image1.Picture = LoadPicture(fPath & "\" & "Ironmongery Images" & "\" & "NoPicture.jpg")
End If
'reset error handler
On Error GoTo 0
End Sub
Private Sub txtamount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(txtamount.Value) = "" And Me.Visible Then


MsgBox "Cannot Be Empty", vbCritical, "Error"
Cancel = True
txtamount.BackColor = vbRed
Else
txtamount.BackColor = vbWhite
End If


End Sub
Private Sub UserForm_Initialize()
cboxType.RowSource = "ProductType"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close button!"
End If
End Sub

Display More

**royUK** · Oct 2nd 2020

You cannot add amounts in TextBoxes they are by default Strings. They need to be converted to a numeric value.

Attach an example workbook and I'll take a look.

CJamesUK · Oct 2nd 2020

Hi Roy

Unfortunately i cant as some parts are linked to my local and network

**royUK** · Oct 2nd 2020

Without seeing what you are trying to do it's very difficult to help. I don't understand what your code is doing, e.g. what is "Hinge"?

Code

lstResults.RowSource = "Hinge"

RowSource is not the best way to load a ListBox

CJamesUK · Oct 2nd 2020

so ive had no VBA training im learning as i go along, this is a brand new sheet ive been working on.

Ok the jist of what im trying to achieve at this point form PickForm

I want to be able to select a type of ironmongery from the combobox which then gives me all the options in the listbox underneath when you click on an item it will bring up a picture, in the amount box i want to be able to enter the amount of this item that i need which then will transfer down to the bottom list box which will become my pick list that i will send to a sheet within excel.

id like the bottom box to look like this

Product Code Description Per Unit Price Supplier Amount

As ive said im totally new to this kinda stuff so other parts of what im doing maybe wrong but it all seems to work so far.

**royUK** · Oct 2nd 2020

cboType

You cannot put a source in the RowSource Property manually and set it on Initialize. I have removed the manual entry and changed the initialize event to load it using the List Property, by using CurrentREgion this will be dynamic, so you can add product types when necessary, or remove them, without changing the code.

Why have you set the BoundColumn to 2? It's only a one column ComboBox. the BoundColumn determines which column returns the ComboBoxes Value

ListBox1

AddItem will add an item, i.e a new row. So your product and amount are appearing on separate rows.

It's a matter of choice but unless there is a need to force users to use the Close Button I find it very annoying.

You might find my DataBaseForm useful follow the link in my signature below. There's also some useful articles there and I will be adding more as I get time

CJamesUK · Oct 2nd 2020

As i have said im learning as i go along so some things are either getting missed or changed by error.

im very greatful for the help ty

CJamesUK · Oct 2nd 2020

Right im done for the week, i will be back on Tuesday for sure

**royUK** · Oct 2nd 2020

Everyone has to learn, my comments were only meant to be helpful.

Post back if you aren't sure about what I have changed.

CJamesUK · Oct 8th 2020

Quote from royUK

Everyone has to learn, my comments were only meant to be helpful.

Post back if you aren't sure about what I have changed.

Hi Roy,

Only just got round to having a look at this again as im picking it up in between my normal workload.

Again thanks for helping out with this.

So a couple of things as what you have changed im having a bit of trouble understanding.

The amount is now in the next column as asked but when you add another item and amount the item lists below as required but the amount just stays with the first item and just changes it.

Also id like to get the whole line of text from above not just the first item.

**royUK** · Oct 8th 2020

Which userform is this?

CJamesUK · Oct 8th 2020

pickform

**royUK** · Oct 8th 2020

Check this

Code

Private Sub cmdAddItem_Click()
If txtamount.Value = "" Then
MsgBox "Please enter a value"
Exit Sub
Else
'       Code if not empty
End If


With Me.ListBox1
.AddItem Me.lstResults.Value
.List(.ListCount - 1, 1) = Me.txtamount.Value
Me.txtamount.Value = Empty
End With


End Sub

Display More

CJamesUK · Oct 8th 2020

Also is there any way i can get column headers in lstresults box?

**royUK** · Oct 8th 2020

Not without a source to pull from, the ListBox isn't connected to any data

**royUK** · Oct 8th 2020

Also, why has ListBox1 got 6 columns when you are only using two?

**royUK** · Oct 8th 2020

You can use Labels.

CJamesUK · Oct 8th 2020

Quote from royUK

Check this

Code

Private Sub cmdAddItem_Click()
If txtamount.Value = "" Then
MsgBox "Please enter a value"
Exit Sub
Else
'       Code if not empty
End If


With Me.ListBox1
.AddItem Me.lstResults.Value
.List(.ListCount - 1, 1) = Me.txtamount.Value
Me.txtamount.Value = Empty
End With


End Sub

Display More

Thats works perfect thank you

CJamesUK · Oct 8th 2020

Quote from royUK

Not without a source to pull from, the ListBox isn't connected to any data

Ok maybe ill have to rethink the whole form then i must have the full product code, description, price and so on in the bottom list so i can then send to a sheet to print of, email etc etc i just cant get my head round why it will only pick up the product code from my pick list??

Anyway that's why there is 6 columns i was trying to fill with all the data i need.

**royUK** · Oct 8th 2020

Ok, I'll look at that tomorrow as soon as I get time. I think a different approach will make it easier.

Listbox column help

Participate now!

Share