howto make a VB6 subform modal with excel

[lalbatros]

Hello,

I have remoted nearly everything from my excel application.
Nearly all modules, classes and forms are now in a VB6 dll.
I have also added some VB6 forms.

How can I make it such that the VB6 form become modal with excel?

Thanks

 

[Andrew Poulsom]

Does this help?

http://support.microsoft.com/kb/172059

 

[lalbatros]

Andrew,

This is interresting because I didn't know about arguments of the Show method.
However, I have tried to use

myForm.Show vbModal, xl.something

and I have not been able to get myForm as modal over Excel.
Actually, I have not found "something" for the second argument.
This statement occurs in a VB6-dll called from excel.
The called sub get a handle to the calling instance of excel, I called it simply "xl".
The microsoft documentation (http://msdn.microsoft.com/en-us/library/aa244181(VS.60).aspx) doesn't clearly tell us the type of the "ownerform" argument, but I guess it is a VB6 form. Therefore, probably there is no xl object valid to be used in this VB6 show method.

I also tried

myForm.Show vbModal

but this hangs my computer.
This is probably the behaviour described by microsoft: "When Show displays a modal form, no subsequent code is executed until the form is hidden or unloaded."

I will made more attempts.

 

[Legacy 98055]

"When Show displays a modal form, no subsequent code is executed until the form is hidden or unloaded."

That is the definition of modal. A better description would be:

"When Show displays a modal form, no subsequent code, outside of the modal form, is executed until the form is hidden or unloaded."

Basically stating that control is being handed to the form. I am suspecting that you may be trying to show a form as modeless and topmost, as opposed to modal. In either case, both are easy to do. See the attached that will extract a workbook, dll, and the vb projectfiles to a single folder. Open the workbook. Also shows how to dynamically register and unregister your dll.
The code assumes that the dll is in the workbook path.

<a href="http://home.fuse.net/tstom/VB.dll Form Example.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/VB.dll Form Example.zip">VB.dll Form Example.zip</a>

This code registers/un-registers a dll named "MyProject.dll" located in the workbook's path

Workbook code:
Code in workbook class.

Private Sub Workbook_Open()
    Call Shell("REGSVR32.EXE /s " & Chr(34) & ThisWorkbook.Path & "\MyProject.dll")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Shell("REGSVR32 /u /s " & Chr(34) & ThisWorkbook.Path & "\MyProject.dll")
End Sub

Place 2 commandbuttons with their default names on a worksheet...

Private Sub CommandButton1_Click()
    Dim mp As Object
    Set mp = CreateObject("MyProject.VB6FormExamples")
    mp.ShowMyFormModal
    MsgBox "Modal form unloaded..."
End Sub

Private Sub CommandButton2_Click()
    Dim mp As Object
    Set mp = CreateObject("MyProject.VB6FormExamples")
    mp.ShowMyFormModelessAndTopmost Application
    MsgBox "Modeless form allows code to continue..."
End Sub

VB6 Project Code:
Add frmModal with commandbutton1

Private Sub Command1_Click()
    Unload Me
End Sub

Add frmModeless with commandbutton1, label1, timer1

Private Type POINTAPI
    x As Long
    y As Long
End Type

Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Private pHost As Object

Private Sub Command1_Click()
    Unload Me
End Sub

Friend Property Set Host(arg As Object)
    Set pHost = arg
    Timer1.Enabled = True
End Property

Private Sub Form_Load()
    'this places the window on top
    SetWindowPos Me.hwnd, -1, 0, 0, 0, 0, 83
End Sub

Private Sub Timer1_Timer()
    Dim pt As POINTAPI, s As String
    On Error Resume Next
    GetCursorPos pt
    s = pHost.activewindow.rangefrompoint(pt.x, pt.y).address(0, 0)
    If Err.Number = 0 Then
        Label1.Caption = "The cursor is over range " & s
    Else
        Label1.Caption = "Move your cursor over some cells..."
    End If
End Sub

Add class VB6FormExamples

Public Sub ShowMyFormModal()
    frmModal.Show vbModal
End Sub

Public Sub ShowMyFormModelessAndTopmost(Host As Object)
    Set frmModeless.Host = Host
    frmModeless.Show vbModeless
End Sub

Name the project, "MyProject". Compile the dll to the path of your workbook.