ListBox Question

[Randal W. Hozeski]

I am using the following code to display results of a userform,
but for some reason the ListBox2 does not populate my sheet.
ListBox1 and TextBox1 come thru fine. The only difference
between the range in ListBox1 and ListBox2, is that ListBox1
has text and ListBox2 has a formula in it (Date)
field. Any idea why/suggestions?

NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(NextRow, 1) = ListBox1.Text
Cells(NextRow, 2) = ListBox2.Text (tried .Value too)
Cells(NextRow, 3) = TextBox1.Text

Also ListBox1 has 4 columns in it and it returns the value that
is in the first column. What if I want the value of Column 1 in
the result page first cell and the value of column 3 in another?

Thanks -Randy-

Thanks -Randy-

 

[Tom Ogilvy]

What do you mean by "has a formula in it (Date)"

What does listbox2 display as selected and what is entered in the cell.

Is listbox2 a multicolumn listbox - it is possible you have an empty column
and it is returning the empty value in that column.

the textcolumn is what is displayed to the user. The boundcolumn is what is
assigned as the value of the listbox.

You can reference other columns using either the list or column properties.

Cells(NextRow,8).Value = listbox1.List(listbox1.listindex,2)
would give you the value of column 3. (column 1 is zero).

 

[Dave Peterson]

I've got two guesses.

One there's nothing in listbox2. (not a good guess, huh?)

My second one is this:
you have an "On error resume next" line in your code.
Your formula is invalid. The error occurs and the resume next takes over.

When I'm dropping a formula into a cell, sometimes I make it text, then go back
to the worksheet and see what's invalid:

Cells(NextRow, 2) = "'" & ListBox2.Text

In fact, I like to be more explicit:

Cells(NextRow, 2).value = "'" & ListBox2.Text
or
Cells(NextRow, 2).formula = "'" & ListBox2.Text

Excel is pretty forgiving, but I find that it's a good way to document what I'm
doing (without adding more comments).

 

[Randal W. Hozeski]

Tom:
Thanks!!! the listindex was it (for the sub-issue)

The core issue on ListBox2 still remains.
Range of ListBox2 are formulas.

Cell A1 is: =NOW()-28-WEEKDAY(NOW())
A2: =NOW()-21-WEEKDAY(NOW())
A3: =NOW()-14-WEEKDAY(NOW())
A4: =NOW()-7-WEEKDAY(NOW())
A5: =NOW()-WEEKDAY(NOW())
A6: =NOW()+7-WEEKDAY(NOW())

Range = A1:A6 or I am calling it "Dates". ListBox2
shows. 11/15/03, 11/22/03, 11/29/03, 12/06/03
12/13/03 and 12/20/03.
ColumnCount confirmed @ 1 and no Error handling in
this sub.

I considered trying a SpinButton to display the dates
but this is just as good.

I bit more detail.... hope it help resolve.
Thanks -Randy-

..







*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

I set up a listbox2 on the worksheet and set the listfillrange property to
A1:A6. I put your formulas in A1:A6 and formatted the cells as date.

I put in a click event:

Private Sub ListBox2_Click()
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
Cells(NextRow, 2) = ListBox2.Text

End Sub

It placed the values in the cell with no problem (as long as I formatted the
cell as data, it appeared as the selected date).

This was a listbox from the control toolbox toolbar.

 

[Randal W. Hozeski]

Tom:
Thanks, I re-created this on a worksheet as well and it did work fine.
How you though created the same from a userform? I follow the exact
same logic and formats withing the userform and the destination cell
that the result is suppose to be, still ends up blank.

I noticed that the Listbox properties show the dates numeric value, but
even that does not make it thru.

I am willing to try a SpinButton with the dates as a workaround, but
would need some assistance with that.

Still hammering on it. -Randy-

..



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

There should be no difference with a userform. If you want to send me the
workbook I will take a look at it and see if I can figure out what the
problem is.

(e-mail address removed)

 

[Tom Ogilvy]

when you enter a value from Listbox1, it causes the workbook to be
recalculated. Since this changes the list for Listbox2 (the now() function,
used as the rowsource for the listbox, is volatile - so the cells that form
the source for the list are
recalculated - and even though their values do not actually change), this
causes the selection in Listbox2 to be cleared. So when you go to put it in
the cell - the value is "" or blank. I put in code to turn calculation to
Manual above where you write the values to the cell and at the end of the
routine, I set
it back to whatever the original setting was. This solves the problem.

--
Regards,
Tom Ogilvy