howto make a VB6 subform modal with excel

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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.
 
L

Legacy 98055

Guest
"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.
Code:
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...
Code:
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
Code:
Private Sub Command1_Click()
    Unload Me
End Sub

Add frmModeless with commandbutton1, label1, timer1
Code:
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
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top