getting the value of the second column

[olorin]

Hi people (Happy New Year to All)

I have a user form to gather information and place it in relevant sheets.
All works fine (with the excellent help from this forum).
The trouble is that when it puts the value of the listbox into the "monthly" sheet, It just puts the value of the first column from the listbox into the sheet.
I wish it to put the value of SECOND column of the listbox into the sheet.
How can I do this?
I have tried a number of things to no avail. Below is the part of the code that i need to modify;

With Worksheets("monthly")
FinalRow = Sheets("monthly").Range("A65536").End(xlUp).row
EntryRow = FinalRow + 1
.Range("A" & EntryRow).Value = Sheets("main").Range("produpdate").Value
.Range("B" & EntryRow).Value = ProdIn.ListBox1.Value
.Range("C" & EntryRow).Value = ProdIn.ListBox1.Value
.Range("D" & EntryRow).Value = ProdIn.productionfigure.Value
End With

It's the Second "ProdIn.ListBox1.Value" that needs changing to be able to select the value of the second column of the listbox and not as it does now, the value of the first column.
I have tried putting "ProdIn.ListBox.Column2.Value"
but I can't get that to work.
Any help is, as always, much appreciated.

 

[BrianB]

This code initialises a 2 column listbox from a worksheet.
No doubt you will see how to do it the other way

'- initialise form
Private Sub UserForm_Initialize()
    Set DataSheet = ThisWorkbook.Worksheets("data")
    Set MyList = DataSheet.Range("FileList")
    Rw = 1
    FileListBox.Clear
    While MyList.Cells(Rw, 1).Value <> ""
        FileListBox.AddItem
        FileListBox.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
        FileListBox.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
        Rw = Rw + 1
    Wend
End Sub

 

[olorin]

still can't get this right

Hi Brian,
Thanx for your reply.
I am still floundering here though.
I have tried a number of combinations with your previous advice, but still can't get it to work.
Here is the whole code of the relevant button on the userform;

Private Sub ProdFigOK_Click()
If productionfigure.Value = "" Or IsNumeric(productionfigure.Value) = False Then
MsgBox ("YOU DIDN'T ENTER A NUMBER!!")
productionfigure.Value = ""
ProdIn.productionfigure.SetFocus
Exit Sub
End If
Sheets("produced").Range("A" & ListBox1.ListIndex + 1) _
.End(xlToRight).Offset(0, 1) = productionfigure.Value
With Sheets("main").Range("produpdate")
.Value = Now()
.NumberFormat = "mmm/dd"
End With
With Worksheets("monthly")
FinalRow = Sheets("monthly").Range("A65536").End(xlUp).row
EntryRow = FinalRow + 1
.Range("A" & EntryRow).Value = Sheets("main").Range("produpdate").Value
.Range("B" & EntryRow).Value = ProdIn.ListBox1.Value
.Range("C" & EntryRow).Value = ProdIn.ListBox1.Value
.Range("D" & EntryRow).Value = ProdIn.productionfigure.Value
End With
productionfigure.Value = ""
ProdIn.productionfigure.SetFocus
End Sub

First it checks if a number was entered before carrying on,
Then it puts the number entered into the "produced" sheet at the end of the relevant item selected from the list box.
Then it puts the date of the action into a cell, then it copies the date into column A in the monthly sheet. It then puts the value of column1 from the listbox into column B of the monthly sheet.
It is the next line that's giving me trouble, I wish it to put the value of the SECOND column of the listbox into column C of the monthly sheet.

Thanx for your time.

 

[Norie]

So you want the value from the second column of the listbox in column B?

Try this.

.Range("C" & EntryRow).Value = ProdIn.ListBox1.Column(1)

 

[olorin]

so it's "column 1" because it starts from 0?
Brilliant !!
Thanx a million

 

[olorin]

It never ceases to amaze me............ the time that people take to help out on here

 

[Norie]

so it's "column 1" because it starts from 0?
Brilliant !!
Thanx a million

Yep's that's right. I think..

I wish I could remember, or somebody could make up their mind, what is zero-indexed and what's one-indexed.