vb6 form with listbox

Trying to show a VB6 form in Excel with a populated listbox, but not much
success sofar.
I started with an example from the book Professional Excel Development and
now have the following code:

In VB6:
---------------

A normal form with a listbox and a commandbutton with the code:

Private Sub cmdList_Click()
PopulateList
End Sub


A class module with the code:

Option Explicit
Private Const GWL_HWNDPARENT As Long = -8
Private mxlApp As Excel.Application
Private mlXLhWnd As Long
Private Declare Function FindWindowA _
Lib "user32" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As
Long
Private Declare Function SetWindowLongA _
Lib "user32" (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

Public Property Set ExcelApp(ByRef xlApp As Excel.Application)
Set mxlApp = xlApp
mlXLhWnd = FindWindowA(vbNullString, mxlApp.Caption)
End Property

Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub

Public Sub ShowVB6Form()
Dim frmHelloWorld As FHelloWorld
Set frmHelloWorld = New FHelloWorld
Load frmHelloWorld
SetWindowLongA frmHelloWorld.hwnd, GWL_HWNDPARENT, mlXLhWnd
frmHelloWorld.Show 0
Set frmHelloWorld = Nothing
End Sub


A module with the code:

Sub PopulateList()
Dim i As Long
For i = 1 To 3
FHelloWorld.List1.AddItem "item " & i
MsgBox "item " & i 'this shows fine
Next
FHelloWorld.List1.Refresh
End Sub

This will compiled to an ActiveX dll, AFirstProject.dll


In Excel:
------------------------

The .dll is referenced

A normal module with the code:

Public Sub DisplayDLLForm()

Dim clsHelloWorld As AFirstProject.CHelloWorld
Set clsHelloWorld = New AFirstProject.CHelloWorld
Set clsHelloWorld.ExcelApp = Application
clsHelloWorld.ShowVB6Form
Set clsHelloWorld = Nothing

End Sub

This Sub will load the form.
Clicking the button will show all the VB6 messages, but the listbox doesn't
get populated.
There is no error, but no items will show.
I must be overlooking something simple and fundamental here, but I can't see
it.
The idea of this is to get a form in Excel with a listbox that will scroll
with the mouse wheel.
Thanks for any advice.


RBS

Hi RB,

The problem is that your PopulateList procedure is not referencing the
form that you're showing. And there isn't any way it can reference the form
you're showing as the code is currently structured because the variable that
holds a reference to the form is local to the ShowVB6Form procedure.

The PopulateList procedure should really be located inside the form's
code module and modified as shown below:

Sub PopulateList()
Dim i As Long
For i = 1 To 3
List1.AddItem "item " & i
Next
List1.Refresh
End Sub

You should then have no trouble calling it from command button click event.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%239nWX$6ZFHA.4000@TK2MSFTNGP10.phx.gbl...
> Trying to show a VB6 form in Excel with a populated listbox, but not much
> success sofar.
> I started with an example from the book Professional Excel Development and
> now have the following code:
>
> In VB6:
> ---------------
>
> A normal form with a listbox and a commandbutton with the code:
>
> Private Sub cmdList_Click()
> PopulateList
> End Sub
>
>
> A class module with the code:
>
> Option Explicit
> Private Const GWL_HWNDPARENT As Long = -8
> Private mxlApp As Excel.Application
> Private mlXLhWnd As Long
> Private Declare Function FindWindowA _
> Lib "user32" (ByVal lpClassName As String, _
> ByVal lpWindowName As String) As
> Long
> Private Declare Function SetWindowLongA _
> Lib "user32" (ByVal hwnd As Long, _
> ByVal nIndex As Long, _
> ByVal dwNewLong As Long) As Long
>
> Public Property Set ExcelApp(ByRef xlApp As Excel.Application)
> Set mxlApp = xlApp
> mlXLhWnd = FindWindowA(vbNullString, mxlApp.Caption)
> End Property
>
> Private Sub Class_Terminate()
> Set mxlApp = Nothing
> End Sub
>
> Public Sub ShowVB6Form()
> Dim frmHelloWorld As FHelloWorld
> Set frmHelloWorld = New FHelloWorld
> Load frmHelloWorld
> SetWindowLongA frmHelloWorld.hwnd, GWL_HWNDPARENT, mlXLhWnd
> frmHelloWorld.Show 0
> Set frmHelloWorld = Nothing
> End Sub
>
>
> A module with the code:
>
> Sub PopulateList()
> Dim i As Long
> For i = 1 To 3
> FHelloWorld.List1.AddItem "item " & i
> MsgBox "item " & i 'this shows fine
> Next
> FHelloWorld.List1.Refresh
> End Sub
>
> This will compiled to an ActiveX dll, AFirstProject.dll
>
>
> In Excel:
> ------------------------
>
> The .dll is referenced
>
> A normal module with the code:
>
> Public Sub DisplayDLLForm()
>
> Dim clsHelloWorld As AFirstProject.CHelloWorld
> Set clsHelloWorld = New AFirstProject.CHelloWorld
> Set clsHelloWorld.ExcelApp = Application
> clsHelloWorld.ShowVB6Form
> Set clsHelloWorld = Nothing
>
> End Sub
>
> This Sub will load the form.
> Clicking the button will show all the VB6 messages, but the listbox
> doesn't get populated.
> There is no error, but no items will show.
> I must be overlooking something simple and fundamental here, but I can't
> see it.
> The idea of this is to get a form in Excel with a listbox that will scroll
> with the mouse wheel.
> Thanks for any advice.
>
>
> RBS
>
>

Rob,


Thanks. I am not sure I fully understand, but it works with your suggested
alteration.
About the sub not referencing the form, I thought this line:

FHelloWorld.List1.AddItem "item " & i

should reference the form, but I understand now that:

Dim frmHelloWorld As FHelloWorld
Set frmHelloWorld = New FHelloWorld

Load frmHelloWorld

means that it is not FHelloWorld is loaded, but frmHelloWorld an instance of
the class.

I think a VB6 form is the way forward to get a mouse listbox scroll as the
API method seems very troublesome.


RBS


"Rob Bovey" <Rob_Bovey@msn.com> wrote in message
news:%23vDXg87ZFHA.3072@TK2MSFTNGP10.phx.gbl...
> Hi RB,
>
> The problem is that your PopulateList procedure is not referencing the
> form that you're showing. And there isn't any way it can reference the
> form you're showing as the code is currently structured because the
> variable that holds a reference to the form is local to the ShowVB6Form
> procedure.
>
> The PopulateList procedure should really be located inside the form's
> code module and modified as shown below:
>
> Sub PopulateList()
> Dim i As Long
> For i = 1 To 3
> List1.AddItem "item " & i
> Next
> List1.Refresh
> End Sub
>
> You should then have no trouble calling it from command button click
> event.
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%239nWX$6ZFHA.4000@TK2MSFTNGP10.phx.gbl...
>> Trying to show a VB6 form in Excel with a populated listbox, but not much
>> success sofar.
>> I started with an example from the book Professional Excel Development
>> and now have the following code:
>>
>> In VB6:
>> ---------------
>>
>> A normal form with a listbox and a commandbutton with the code:
>>
>> Private Sub cmdList_Click()
>> PopulateList
>> End Sub
>>
>>
>> A class module with the code:
>>
>> Option Explicit
>> Private Const GWL_HWNDPARENT As Long = -8
>> Private mxlApp As Excel.Application
>> Private mlXLhWnd As Long
>> Private Declare Function FindWindowA _
>> Lib "user32" (ByVal lpClassName As String, _
>> ByVal lpWindowName As String) As
>> Long
>> Private Declare Function SetWindowLongA _
>> Lib "user32" (ByVal hwnd As Long, _
>> ByVal nIndex As Long, _
>> ByVal dwNewLong As Long) As Long
>>
>> Public Property Set ExcelApp(ByRef xlApp As Excel.Application)
>> Set mxlApp = xlApp
>> mlXLhWnd = FindWindowA(vbNullString, mxlApp.Caption)
>> End Property
>>
>> Private Sub Class_Terminate()
>> Set mxlApp = Nothing
>> End Sub
>>
>> Public Sub ShowVB6Form()
>> Dim frmHelloWorld As FHelloWorld
>> Set frmHelloWorld = New FHelloWorld
>> Load frmHelloWorld
>> SetWindowLongA frmHelloWorld.hwnd, GWL_HWNDPARENT, mlXLhWnd
>> frmHelloWorld.Show 0
>> Set frmHelloWorld = Nothing
>> End Sub
>>
>>
>> A module with the code:
>>
>> Sub PopulateList()
>> Dim i As Long
>> For i = 1 To 3
>> FHelloWorld.List1.AddItem "item " & i
>> MsgBox "item " & i 'this shows fine
>> Next
>> FHelloWorld.List1.Refresh
>> End Sub
>>
>> This will compiled to an ActiveX dll, AFirstProject.dll
>>
>>
>> In Excel:
>> ------------------------
>>
>> The .dll is referenced
>>
>> A normal module with the code:
>>
>> Public Sub DisplayDLLForm()
>>
>> Dim clsHelloWorld As AFirstProject.CHelloWorld
>> Set clsHelloWorld = New AFirstProject.CHelloWorld
>> Set clsHelloWorld.ExcelApp = Application
>> clsHelloWorld.ShowVB6Form
>> Set clsHelloWorld = Nothing
>>
>> End Sub
>>
>> This Sub will load the form.
>> Clicking the button will show all the VB6 messages, but the listbox
>> doesn't get populated.
>> There is no error, but no items will show.
>> I must be overlooking something simple and fundamental here, but I can't
>> see it.
>> The idea of this is to get a form in Excel with a listbox that will
>> scroll with the mouse wheel.
>> Thanks for any advice.
>>
>>
>> RBS
>>
>>
>
>