"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.