[Hola
Pega esto en el módulo del Userform en donde quieres el efecto. Para cerrar dicho Userform solo dale doble clic. OJO, hazlo en un Userform nuevo como para que entiendas como funciona:
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) 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 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 Const GWL_STYLE = (-16)
Private Const WS_CAPTION = &HC00000 '// WS_BORDER Or WS_DLGFRAME
Private Const WS_BORDER = &H800000
Private Enum ESetWindowPosStyles
SWP_SHOWWINDOW = &H40
SWP_HIDEWINDOW = &H80
SWP_FRAMECHANGED = &H20 '// The frame changed: send WM_NCCALCSIZE
SWP_NOACTIVATE = &H10
SWP_NOCOPYBITS = &H100
SWP_NOMOVE = &H2
SWP_NOOWNERZORDER = &H200 '// Don't do owner Z ordering
SWP_NOREDRAW = &H8
SWP_NOREPOSITION = SWP_NOOWNERZORDER
SWP_NOSIZE = &H1
SWP_NOZORDER = &H4
SWP_DRAWFRAME = SWP_FRAMECHANGED
HWND_NOTOPMOST = -2
End Enum
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Dim FrmWndh As Long
Dim lStyle As Long
Dim tR As RECT
Private Sub UserForm_Activate()
'// NB: Must be in the Activate Event!
'// Get Forms window handle
FrmWndh = FindWindow(vbNullString, Me.Caption)
'// Modify whether title bar will be visible:
lStyle = GetWindowLong(FrmWndh, GWL_STYLE)
'// Now mask the Winstyle caption
lStyle = lStyle And Not WS_CAPTION
SetWindowLong FrmWndh, GWL_STYLE, lStyle
'// Ensure the style takes and make the window the
'// same size, regardless that the title bar
'// is now a different size:
SetWindowPos FrmWndh, _
0, _
tR.Left, _
tR.Top, _
tR.Right - tR.Left, _
tR.Bottom - tR.Top, _
SWP_NOREPOSITION Or SWP_NOZORDER Or SWP_FRAMECHANGED Or WS_BORDER
'// Update Form
Me.Repaint
End Sub
Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'// Leave this here as your backdoor, as you have NO CLOSE BUTTON
'// otherwise put in a close button
Unload UserForm1
End Sub
Por cierto, el código no es mío, lo tenía en el "baúl de los recuerdos".