Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Show
Hi
I have 2 workbooks which compare the data on them to make sure everything has been returned. As this takes up half a day due to the size of the data I have created a Userform which I would like to have showing while the checks are being actioned behind this Userform. Is there away to make this work as I keep loosing the focus of the userform when the macro is run. Thanks Noemi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Show
Try this, create a userform and place the following codes in the userform -
Private Declare Function FindWindow Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetWindowLong Lib "user32" Alias _ "GetWindowLongA" (ByVal hwnd As Long, _ ByVal nIndex 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 DrawMenuBar Lib "user32" ( _ ByVal hwnd As Long) As Long Private Function HideCaptionBar() Dim lngHnd As Long Dim lngStyle As Long Dim lngH(1) As Long Const GWL_STYLE = (-16) Const WS_CAPTION = &HC00000 lngH(0) = Me.Height - Me.InsideHeight If Val(Application.Version) 8 Then lngHnd = FindWindow("ThunderDFrame", Me.Caption) Else lngHnd = FindWindow("ThunderXFrame", Me.Caption) End If lngStyle = GetWindowLong(lngHnd, GWL_STYLE) And Not WS_CAPTION SetWindowLong lngHnd, GWL_STYLE, lngStyle DrawMenuBar lngHnd lngH(1) = Me.Height - Me.InsideHeight Me.Height = Me.Height + lngH(1) - lngH(0) End Function Private Sub UserForm_Initialize() Call HideCaptionBar End Sub Private Sub UserForm_Activate() DoEvents UserForm.Label="Processing ......" Your codes here ... Unload Userform End Sub I hope this help. "Noemi" wrote: Hi I have 2 workbooks which compare the data on them to make sure everything has been returned. As this takes up half a day due to the size of the data I have created a Userform which I would like to have showing while the checks are being actioned behind this Userform. Is there away to make this work as I keep loosing the focus of the userform when the macro is run. Thanks Noemi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Show
Hi Francis
Thank you for the code I put it on to the forms code however the word Processing... does not appear on the screen only once my code has completed, any ideas how to get the label to be shown while processing the code??? I am not sure if the reason for this is because it switches between to workbooks! Thanks Noemi "Francis Ang" wrote: Try this, create a userform and place the following codes in the userform - Private Declare Function FindWindow Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetWindowLong Lib "user32" Alias _ "GetWindowLongA" (ByVal hwnd As Long, _ ByVal nIndex 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 DrawMenuBar Lib "user32" ( _ ByVal hwnd As Long) As Long Private Function HideCaptionBar() Dim lngHnd As Long Dim lngStyle As Long Dim lngH(1) As Long Const GWL_STYLE = (-16) Const WS_CAPTION = &HC00000 lngH(0) = Me.Height - Me.InsideHeight If Val(Application.Version) 8 Then lngHnd = FindWindow("ThunderDFrame", Me.Caption) Else lngHnd = FindWindow("ThunderXFrame", Me.Caption) End If lngStyle = GetWindowLong(lngHnd, GWL_STYLE) And Not WS_CAPTION SetWindowLong lngHnd, GWL_STYLE, lngStyle DrawMenuBar lngHnd lngH(1) = Me.Height - Me.InsideHeight Me.Height = Me.Height + lngH(1) - lngH(0) End Function Private Sub UserForm_Initialize() Call HideCaptionBar End Sub Private Sub UserForm_Activate() DoEvents UserForm.Label="Processing ......" Your codes here ... Unload Userform End Sub I hope this help. "Noemi" wrote: Hi I have 2 workbooks which compare the data on them to make sure everything has been returned. As this takes up half a day due to the size of the data I have created a Userform which I would like to have showing while the checks are being actioned behind this Userform. Is there away to make this work as I keep loosing the focus of the userform when the macro is run. Thanks Noemi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform Show
Hi Noemi,
Try moving these lines - DoEvents Userform.Label="Processing ...." and place it just before you call the useform. "Noemi" wrote: Hi Francis Thank you for the code I put it on to the forms code however the word Processing... does not appear on the screen only once my code has completed, any ideas how to get the label to be shown while processing the code??? I am not sure if the reason for this is because it switches between to workbooks! Thanks Noemi "Francis Ang" wrote: Try this, create a userform and place the following codes in the userform - Private Declare Function FindWindow Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long Private Declare Function GetWindowLong Lib "user32" Alias _ "GetWindowLongA" (ByVal hwnd As Long, _ ByVal nIndex 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 DrawMenuBar Lib "user32" ( _ ByVal hwnd As Long) As Long Private Function HideCaptionBar() Dim lngHnd As Long Dim lngStyle As Long Dim lngH(1) As Long Const GWL_STYLE = (-16) Const WS_CAPTION = &HC00000 lngH(0) = Me.Height - Me.InsideHeight If Val(Application.Version) 8 Then lngHnd = FindWindow("ThunderDFrame", Me.Caption) Else lngHnd = FindWindow("ThunderXFrame", Me.Caption) End If lngStyle = GetWindowLong(lngHnd, GWL_STYLE) And Not WS_CAPTION SetWindowLong lngHnd, GWL_STYLE, lngStyle DrawMenuBar lngHnd lngH(1) = Me.Height - Me.InsideHeight Me.Height = Me.Height + lngH(1) - lngH(0) End Function Private Sub UserForm_Initialize() Call HideCaptionBar End Sub Private Sub UserForm_Activate() DoEvents UserForm.Label="Processing ......" Your codes here ... Unload Userform End Sub I hope this help. "Noemi" wrote: Hi I have 2 workbooks which compare the data on them to make sure everything has been returned. As this takes up half a day due to the size of the data I have created a Userform which I would like to have showing while the checks are being actioned behind this Userform. Is there away to make this work as I keep loosing the focus of the userform when the macro is run. Thanks Noemi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform.show question? | Excel Programming | |||
show userform | Excel Programming | |||
How to Show a userform from XLA sub | Excel Programming | |||
show a userform | Excel Programming |