vb6 form with listbox

[RB Smissaert]

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 Bovey]

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]

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