Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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). -- Regards, Tom Ogilvy Randal W. Hozeski wrote in message news:50SEb.93812$8y1.301650@attbi_s52... 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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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" wrote: 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- -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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. -- Regards, Tom Ogilvy Randal W. Hozeski wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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. -- Regards, Tom Ogilvy Randal W. Hozeski wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Question
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 Tom Ogilvy wrote in message ... 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. -- Regards, Tom Ogilvy Randal W. Hozeski wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox Question | Excel Discussion (Misc queries) | |||
Listbox Question | Excel Worksheet Functions | |||
ListBox question | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
listbox question | Excel Programming |