Multi Column Listbox add data to 3rd column

[waynerenaud]

Hi, I have a multicolumn listbox on userform1 that gets populated from the userform activate event.
The list has 3 columns (Col0,Col1,Col2)ie:

Col0 Col1 Col2
Item Cost Qty
Eggs $1.99
Bacon $4.50

Col2 (Qty) is blank.
If a user selects an item I want to change the entry in Col2 to a 1 for the selected item, (or preferably have a method that defaults to 1, but alows them to put in a greater quantity if required)
I have tried the following code (commented out the line that doesn't work)

Private Sub ListBox1_Change()
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
'ListBox1.Column(2, i) = 1 'Doesn't work
ShopValue = ListBox1.Column(1, i)
RunningTotal = RunningTotal + ShopValue
UserForm1.Label1.Caption = "$" & RunningTotal
End If
Next i
End Sub

Is it possible to write an entry to a column of a selected item in a listbox and if so how??. In frustration I also tried the deleteitem and additem method but couldnt figure out how to capture the existing data in col0 & col1 to add col2 data to it. (Also couldn't understand the syntax at all for the additem & deleteitem). Any Help would be greatly appreciated as I have been pulling my hair out for hours & hours. Thanks

 

[Zathras]

You neglected to say whether this is in Excel or Access, but since you don't subscribe to one of the Access fora, I will assume you are working in Excel.

If your list is populated by being linked to a spreadsheet range (the easiest way to do it), then something like the code below could be used.

Notice that I have put two command buttons on the form. One is for adding 1 to the selected item's quantity and the other is for subtracting 1. A little friendlier for your user rather than arbitrarily adding 1 every time an item is selected.
[blue]

Option Explicit
Public nCurrentSelection As Integer

Private Sub CommandButton1_Click()
  With Range("MyItems")
    .Cells(nCurrentSelection + 1, 3) _
      = .Cells(nCurrentSelection + 1, 3) + 1
  End With
End Sub

Private Sub CommandButton2_Click()
Dim nQuantity As Integer
  With Range("MyItems")
    nQuantity = .Cells(nCurrentSelection + 1, 3)
    If nQuantity > 0 Then
      .Cells(nCurrentSelection + 1, 3) = nQuantity - 1
    End If
  End With
End Sub

Private Sub ListBox1_Change()
  nCurrentSelection = ListBox1.ListIndex
End Sub

[/color]
In my test data, the range "MyItems" is defined as H13:J15 and the worksheet is set up like this:
[blue]

H12: 'Item
H13: 'Eggs
H14: 'Bacon
H15: 'Cheese
I12: 'Cost
I13: 1.99
I14: 4.50
I15: 2.50
J12: 'Qty

[/color]
Column I is formatted for Currency (2 decimals)
The "RowSource" property of the list box is MyItems

 

[waynerenaud]

Hi Zathras, I really appreciate your help

I am having trouble setting MyItems to a range.

In general declarations i have:
Option Explicit
Public MyItems As Range

in userform activate i have
EndRow1 = [a65536].End(xlUp).Row
MyItems = Range("A2", "C" & EndRow1) 'Errors here
UserForm1.ListBox1.RowSource = MyItems

but I get 'object variable or with block variable not set' on the MyItems line. Help :-(

 

[waynerenaud]

Ignore my previous post, now got MyItems to work. Next challenge is how can I launch the commandbutton2_click by right mouse clicking on a selected item.
ie left click increase qty by 1, right click decreases by 1
Thanks again

 

[Zathras]

Using left-click and right-click the way you propose is very unlike any other windows application with which I am familiar. Most users will expect a left-click to select something and a right-click to pop up a short menu of possible things to do in the particular context. I'm afaid if you're going to insist on setting it up that way, you will have to to get someone else to help you. I cannot in good conscience be a party to such an interface design.

One final piece of advice. Before you proceed too far with hanging your action on the ListBox1_Change event, do some testing to see what happens when you use the cursor up/down keys in the list box.

 

[waynerenaud]

Thanks Zathras, Comments accepted.
I have been able to set it up with right click as I want and ackowledge that this is not normal practise, but this was not a normal circumstance. Rather than doing the normal developement for my employers interaction with spreadsheets, this was far more important......it was for the wife to be able to create a shopping list by selecting items from a listbox that then prints out the selection along with expected costs for the shopping. Being the wife, an abnormal approach needed to be adopted so that she cnat get it wrong
Thanks for all the help.
Wayne<--------A happy man with a happy wife (and extra sausages because that always defaults to 2)