utilizzare userform con excel in background

Question

salve a tutti, sto quasi completando il mio progetto grazie al vostro aiuto

adesso vorrei sapere se è possibile utilizzare la userform che ho creato con la finestra di Excel chiusa o ad icona 

quindi avere la userform libera sul desktop

Answer 1

Ciao man_83,

salve a tutti, sto quasi completando il mio progetto grazie al vostro aiuto

adesso vorrei sapere se è possibile utilizzare la userform che ho creato con la finestra di Excel chiusa o ad icona 

quindi avere la userform libera sul desktop

Forse, qualcosa del genere:

Nel modulo del codice dellla Useform. prova il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub UserForm_Initialize()

    Application.Visible = False

End Sub

'--------->>

Private Sub UserForm_Terminate()

    Application.Visible = True

End Sub

'<<=========

Potresti scaricare il mio file di prova Man20170221.xlsm a:

https://www.dropbox.com/s/19em6iqsqs5yyhw/Man20170221.xlsm?dl=0

===

Regards,

Norman

Answer 2

Ciao man_83,

Se tu volessi anche minimizzare e massimizare la Userform, senza chiuderla, e di avere una icona per la Userform sulla barra delle applicazioni, sostituisci il codice precedente con la seguente versione:

'=========>>

Option Explicit

'--------->>

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long

Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

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 SetFocus Lib "user32" (ByVal hWnd As Long) As Long

Private Const GWL_STYLE As Long = -16

Private Const GWL_EXSTYLE As Long = -20

Private Const WS_CAPTION As Long = &HC00000

Private Const WS_MINIMIZEBOX As Long = &H20000

Private Const WS_MAXIMIZEBOX As Long = &H10000

Private Const WS_POPUP As Long = &H80000000

Private Const WS_VISIBLE As Long = &H10000000

Private Const WS_EX_DLGMODALFRAME As Long = &H1

Private Const WS_EX_APPWINDOW As Long = &H40000

Private Const SW_SHOW As Long = 5

'--------->>

Private Sub UserForm_Activate()

    Application.Visible = False

    Application.VBE.MainWindow.Visible = False

    Dim lngHwnd As Long

    Dim lngCurrentStyle As Long, lngNewStyle As Long

    If Val(Application.Version) < 9 Then

        lngHwnd = FindWindow("ThunderXFrame", Me.Caption)  'XL97

    Else

        lngHwnd = FindWindow("ThunderDFrame", Me.Caption)  'XL2000, XP, 2003?

    End If

    'Set the Windows style so that the userform has a minimise and maximise button

    lngCurrentStyle = GetWindowLong(lngHwnd, GWL_STYLE)

    lngNewStyle = lngCurrentStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX

    lngNewStyle = lngNewStyle And Not WS_VISIBLE And Not WS_POPUP

    SetWindowLong lngHwnd, GWL_STYLE, lngNewStyle

    'Set the extended style to provide a taskbar icon

    lngCurrentStyle = GetWindowLong(lngHwnd, GWL_EXSTYLE)

    lngNewStyle = lngCurrentStyle Or WS_EX_APPWINDOW

    SetWindowLong lngHwnd, GWL_EXSTYLE, lngNewStyle

    ShowWindow lngHwnd, SW_SHOW

End Sub

'--------->>

Private Sub UserForm_Terminate()

    Application.Visible = True

End Sub

'<<=========

Ho aggiornato il mio file di prova Man20170221.xlsm a:

https://www.dropbox.com/s/19em6iqsqs5yyhw/Man20170221.xlsm?dl=0

per dimostrare entrambi gli approcci.

===

Regards,

Norman

Answer 3

ti mando il mio progetto epurato da dati sensibili

https://www.dropbox.com/s/knrtw1f5dyt829p/cassapro1.xlsm?dl=0

Answer 4

ottima la prima opzione, 

la seconda da errore debug in

Application.VBE.MainWindow.Visible = False