ExcelBanter - Userform Show

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Userform Show (https://www.excelbanter.com/excel-programming/372244-userform-show.html)

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

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

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

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