Can a userform be displayed within a single worksheet?

[evilrtc]

I want my userform for dataentry to be displayed staticly on a specific worksheet so that my employees can click that tab in the workbook when they want to enter data. I'm very new to vba and excel.

Currently when the userform is open you have to close it to go to other worksheets.

[JR@SGC]

Hi

In your userform properties in VBA find a field called:

ShowModal

Select false from the menu, this will alow you to click other cells or tabs and move around instead of freezing it.


Hope it helps

[evilrtc]

You've been so much help. One other question, is there a way to put up at the right hand corner a minimize button instead of just the x?

[JR@SGC]

Hi

Add these two peices of code in the module in your userfom at the very top( if you already have code in), otherwise it will debug. Dont worry about the blanked out minimise box you will not need it.


code to min/max:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const GWL_STYLE As Long = (-16)


Private Sub UserForm_Initialize()
Dim lngMyHandle As Long, lngCurrentStyle As Long, lngNewStyle As Long
If Application.Version < 9 Then
lngMyHandle = FindWindow("THUNDERXFRAME", Me.Caption)
Else
lngMyHandle = FindWindow("THUNDERDFRAME", Me.Caption)
End If
lngCurrentStyle = GetWindowLong(lngMyHandle, GWL_STYLE)
lngNewStyle = lngCurrentStyle Or WS_MINIMIZEBOX
SetWindowLong lngMyHandle, GWL_STYLE, lngNewStyle
End Sub


code to disabale close button:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub





hope it helps