Listboxes (and other objects) in Excel VBA

[feloniouslemon]

Greetings,

This may be a fantastically stupid question. I was once an experienced VB programmer, but haven't coded for some time. I'm trying to write some VBA within an Excel workbook, and stumped by referencing some of the objects in the worksheet.

I can get to the workbook, worksheet, and cells. But I can't get to the listbox I've added to the worksheet.

So, for example:

Dim i as int
Dim wb as Workbook
Dim ws as Worksheet

Set wb = ThisWorkbook
Set ws = ThisWorkbook.ActiveSheet

i = ws.Cells(1,1)

This all works. But I have a listbox called lb, and I would like to get the value the user has selected from it, so "A" from a listbox showing A, B, C.

How do I dimension that variable, and set it to equal the listbox (lb) in the worksheet? I would expect to set it, and then have properties like .Value or something along those lines.

ANY help is greatly appreciated - I feel like I'm missing the obvious, but I just don't see it.

Cheers,
geek

 

[SkipVought]

If it's a FORMS object, look at the Shapes collection of the Worksheet Object...

s = activesheet.shapes("lb").oleformat.object.value

If it's a Control Toolbox Object...

s = activesheet.lb.value

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feloniouslemon]

Skip,

Thanks for the code snippets. I'm seeing:

s = activesheet.lb.value
Object doesn't support this property or method. (Can't see other properties for it either, in Add Watch)

s = activesheet.shapes("lb").oleformat.object.value
Invalid procedure call or argument

I started the project in Excel 2003, and grabbed the listbox from there - not sure if it's a control box object or a shape. I've sinced tried it in both 2003 and 2007, same errors for both.

Any guidance is greatly appreciated!

Cheers,
lemon

 

[SkipVought]

"I started the project in Excel 2003, and grabbed the listbox from there - not sure if it's a control box object or a shape. "

Do you have Sheet Events for the object? Look in the SHEET code window.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feloniouslemon]

ugh, i'm sorry, i'm not seeing this - what do you mean by 'sheet code window'? do you mean the properties of the worksheet? i cannot find the object anywhere in the code - not in object browser or under sheet properties. am i misunderstanding?

thanks-
fl

 

[SkipVought]

If there are no sheet events, your control is NOT a Control Toolbox object.

You must know what kind of object you are working with!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feloniouslemon]

yeah, you would think - apparently, i'm just not used to this format. i drug it to the excel sheet from the excel toolbox - i'm not sure what type of object that is - but i'm not doing anything outside of 'normal' excel - isn't there some sort of standard for listboxes/controls shown in the excel toolbox?

what do you mean 'sheet events'? should include a screenshot of the window? i'm sure i'm just missing something in terminology. i can work with other aspects of the worksheet, cells, etc., just can't figure out how to access the listbox.

any other advice? skip? anyone? bueller???

 

[SkipVought]

"what do you mean 'sheet events'?"

Right-click the sheet tab and select View Code

In the Upper Left of the Sheet Code Window is the Object drop down. If there are sheet control objects, they will appear in this drop down, along with the Worksheet object.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]