getting the value of the second column

olorin

Board Regular
Joined
Feb 7, 2005
Messages
87
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
This code initialises a 2 column listbox from a worksheet.
No doubt you will see how to do it the other way
Code:
'- 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

Board Regular
Joined
Feb 7, 2005
Messages
87
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
So you want the value from the second column of the listbox in column B?

Try this.
Code:
.Range("C" & EntryRow).Value = ProdIn.ListBox1.Column(1)
 

olorin

Board Regular
Joined
Feb 7, 2005
Messages
87

ADVERTISEMENT

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

olorin

Board Regular
Joined
Feb 7, 2005
Messages
87
It never ceases to amaze me............ the time that people take to help out on here
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
olorin said:
so it's "column 1" because it starts from 0?
Brilliant !!
Thanx a million
Yep's that's right.:) I think.. :eek:

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

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,501
Members
412,670
Latest member
Khin Zaw Htwe
Top