List box value returns the row number of the list box range row

[Tim at MBS]

Hi, I was hoping someone could help me figure out what I am doing wrong.

I have a list box set up on a worksheet and I want the user to select a
value from the list box, and then have that value entered into a cell when a
button is clicked.

When I run/step through the macro, it takes a value and places it into the
appropriate cell. The problem is that the value which is placed in the cell
is not the value from the listbox, but the row number of the value that was
chosen. Using the below row numbers and values as an example; if I select
value 7 from the list box, the value that is returned is 2, not 7. If I
select 9A, the value returned is 4. The listbox value range is on different
worksheet than the worksheet where I want to place the listbox value. The
listbox range is currently one column with about 200 rows.

Worksheet Row Number Value
1 6
2 7
3 8
4 9A
5 10B

The condensed version of the code (excluding how I choose the cell) is:

Dim listvalue As String
Dim rowcounter As Integer
Dim EOBpointer As Range

Worksheets("Timer").Activate
ActiveSheet.Shapes("List Box 8").Select

rowcounter = 35

listvalue = Selection.Value

Set EOBpointer = Range("c" & rowcounter)
EOBpointer.Select
Selection.Value = listvalue

 

[John Bundy]

You are either doing some other strange stuff that is not needed, or i don't
understand what the heck you are doing, but changing
listvalue = Selection.Value
to
listvalue = ListBox1.Value
i think will solve your issue. You are selecting an object and then asking
for its value, the object has no value. Alternatively you could just say
Range("c" & rowcounter)=ListBox1.Value

 

[Dave Peterson]

There are two listboxes you can place on a worksheet.

The first is from the Forms toolbar. Its value is an index into its list.

The second is from the Control toolbox toolbar. Its value is the value you see
in the listbox.

If your listbox (either type) is a single selection, then you don't need to use
a macro to get the value.

If the listbox is from the forms toolbar:
rightclick on the listbox
Choose Format Control
Then on the Control tab, you can assign a cell link (say A1).

Then in B1 (the second cell), you can use that value/index in A1 to retrieve the
value from the list:

=if(a1=0,"",index(sheet2!a1:a11,a1)
where your list is on sheet2 in cells A1:A11


If your listbox is from the control toolbox toolbar, you can just go into design
mode (another icon on that toolbar) and then

rightclick on the listbox
Choose Properties
Scroll down to LinkedCell and type in the address of the cell you want to use.


If your listbox (either type) is multiselect, then this won't work and you will
need code.

=========
It sounds like you're using the listbox from the Forms toolbar.

If you have to use code (as part of a bigger routine), then you could use:

dim myLB8Val as string
with worksheets("timer")
with .listboxes("list box 8")
if .listindex = 0 then
mylb8val = "" 'not chosen
else
mylb8val = .list(.listindex)
end if
end with
end with

msgbox mylb8val

 

[Tim at MBS]

Thanks for the reply.

When I tried to change the code to what you suggested:
listvalue = ListBox8.Value

I got an (Object Required 424) error. As far as I can tell, I have changed
the name of the list box to ListBox8.

I even tried using: Set listvalue = ListBox8.Value, but still got the same
error.

Any ideas?

 

[Tim at MBS]

Thanks Dave,
I did use the form control list box. I'll try to see if I can use the code
you sent, but I don't need a msgbox with the value. I need to put the value
in the cell.

I would use the cell reference option, but the cell that the value needs to
go to will change everytime a value is chosen.

I was trying to write something quick that I could use for time studies to
capture a start and stop time. The list box is to select a code that
corresponds to the type of operation the person is doing. The data would be
organized like a record with code, start time and stop time on the same row,
but in separate columns. Each time the start button is pressed, the code
from the list box would be passed to the cell on the current row. The start
time would then be entered in the start time cell on that row. When the stop
button is pressed, it would populate the stop time, increment the row counter
and get ready for the next observation.

thanks,
Tim

 

[Dave Peterson]

I used the msgbox just to show that it was working ok.

msgbox mylb8val
could be
somecell.value = mylb8val
where somecell was decided elsewhere in your code.