How to set focus on an ActiveX ListBox

[Rob Flott]

I am stymied trying to figure the code that sets the focus of an
ActiveX ListBox to the first name appearing in the ListBox. The
ListBox is on Sheet1.

I tried this code but get the error message "Compile Error Sub or
Function not defined"

I am using Excel 2007

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
OLEObject("ListBox1").Value = "ABC"
Application.ScreenUpdating = True
End Sub

Can anyone help me correct this code so when the workbook opens - it
displays the right sheet and the ListBox shows the focus on the first
name?

Thank you,

Rob

 

[isabelle]

hi Rob,

With Sheets("Sheet1").ListBox1
..ListIndex = 0
End With

--
isabelle



Le 2012-06-21 13:22, Rob Flott a écrit :

 

[Rob Flott]

Isabelle, thank you very much...this works great...

 

[isabelle]

Rob, you're welcome! ..feedback is always appreciated, thank to you!

--
isabelle




Le 2012-06-21 18:08, Rob Flott a écrit :

 

[Rob Flott]

Rob, you're welcome! ..feedback is always appreciated, thank to you!

--
isabelle

Le 2012-06-21 18:08, Rob Flott a écrit :






- Show quoted text -

Hi Isabelle....I spoke to soon...when I run the macro it works
fine...but when I 'call' the macro in my 'Workbook_Open()' sub I get
'Run-Time error '438' Object doesn't support this property or
method'. Ironically, when I click 'End' on this error message it runs
the macro without any problems. Any ideas as to why this happens?

Thanks Isabelle

 

[isabelle]

hi Rob,

i tried it with the Workbook_Open event and i have no error,
here is my test file: http://cjoint.com/?BFwruPnWL1q
can you try it?

--
isabelle



Le 2012-06-22 10:56, Rob Flott a écrit :

 

[Rob Flott]

Yes, your macros works fine...but when I try it in my workbook I keep
getting that run-time error...and when I clidk 'end' it goes ahead and
sets the focus accordingly...very strange...

But thank you for your help...

Rob

 

[isabelle]

the cause may be:

- You specified a Friend procedure which is called by late binding.

- The name of a Friend procedure must be known at compile time. It can not appear in a late-bound call.

without seeing your file i am unable to say more.

--
isabelle



Le 2012-06-22 12:44, Rob Flott a écrit :