Userform retains focus while procedure runs 5

[RP1America]

I have created a userform that includes a progress bar. How do I keep this userform in the foreground while a procedure which is opening and closing documents runs?

I know you can keep a msgbox in the foreground with vbMsgBoxSetForeground. Is this possible with a userform?

Thanks!

 

[TonyJollans]

Set ScreenUpdating to False so that opening and closing Documents doesn't affect the UI - which is good practice anyway.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RP1America]

Didn't work.

Isn't screenupdating only going to affect what's going on inside of the document?

What about application.visible = false?
Is there anyway to keep a userform visible when the application is not?

 

[fumei]

Isn't screenupdating only going to affect what's going on inside of the document?"

No, screenupdating affects screenupdating. It has nothing at all to do with "what's going on inside of the document".

Is your userform Modal or non-Modal?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

Modal

 

[TonyJollans]

What processing are you doing - the opening and closing of documents - that affects - or needs to affect - the UI? And what action triggers the userform to go to the background?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

Tony, please see the other threads the poster has posted.

RP1, it is indeed a best practice to turn off ScreenUpdate.

Could you elaborate on (assuming you have set ScreenUpdate=false) "Didn't work"?

What - exactly - is happening?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[TonyJollans]

Thanks, Gerry. Although I have replied in some of them, I hadn't made the connection.

RP1, I wonder if you need to take a step back and look at the entirety of what you are doing, and the way in which you are doing it, and maybe slow down a little and focus a little more. Sometimes solving one problem can make others go away. What is it you are trying to achieve overall?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

I agree. It is almost always better to "chunk" things.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

I realize that some of you may be getting quite frustrated with the way I (try to) explain things. Please just keep in mind that I am extremely new to VB and am truely trying to learn, not just do.

I also really appreciate that you guys have stuck with me this long.

At this point, I hope it is clear that the overall idea I am going for is that once the first userform's 'OK' click event is executed...all (if possible) of the following occur:

(keeping in mind that the first userform's 'OK' click event executes the opening, populating of bookmark text, saving file as, and closing 246 Word documents as well as shows the second userform - progress/abort)

1) userform2, which contains a progress bar and a command button titled: abort, shows and retains focus throughout process
2) progress bar works properly.
3) abort button to be programmed so that the process stops if the user presses it.

Currently my biggest hurdle is overcoming number 1 above. Without me being able to see the userform2 (progress bar) when testing, I cannot test numbers 2 and 3.

I do understand that I am missing code as well as probably have incorrect code.

Any thoughts as to how to correct it? Thanks!!

'OK' Click event of UserForm 1 (frmProductCost)

Private Sub cmdCreate_click()

Me.Hide

'show progress bar / abort userfrom
frmProgressBar.Show

Application.ScreenUpdating = False

'create "out" folders
Call CreateFolders

'open, merge, save, close all docs
Call Proposal1
Call Proposal2
Call Proposal3
Call Proposal4
Call Proposal5

Application.ScreenUpdating = True

'close document if more Word documents are open, otherwise close Word application
If Documents.Count > 1 Then
   '  more than one doc open
   ActiveDocument.Close
Else  ' count = 1
   ActiveDocument.Close
   Application.Quit
End If


End Sub

All code from UserForm 2 (frmProgressBar)

Private Sub UserForm_Activate()

Dim lDocCount As Long
Dim i As Integer
Dim doc As Document
Dim lMaxProgressBarWidth As Long
Dim sIncrement As Single

' Resize the UserForm
Me.Width = 240
Me.Height = 120

' Resize the label
Me.Label1.BackColor = wdColorBlue

lMaxProgressBarWidth = 200
lDocCount = 246
sIncrement = lMaxProgressBarWidth / lDocCount
i = 1

For Each doc In Documents
    Me.Label1.Width = Format(Me.Label1.Width + sIncrement, "#.##")
    Me.Caption = "Creating " & CStr(i) & " of " & CStr(lDocCount)
    Me.Repaint

i = i + 1
Next doc

Unload Me

End Sub

Private Sub cmdAbort_Click()

''''MISSING CODE - HOW TO STOP PROCESS FROM RUNNING

Dim PropFolder1 As String
Dim PropFolder2 As String
Dim PropFolder3 As String
Dim PropFolder4 As String
Dim PropFolder5 As String

On Error Resume Next
PropFolder1 = "I:\Ryan Plew\Cost Pages Templates\1Proposal - 401(k) Cost pages\401(k) FS LS Cost Pages"
PropFolder2 = "I:\Ryan Plew\Cost Pages Templates\2Proposal - 401(a) 403(b) Cost pages"
PropFolder3 = "I:\Ryan Plew\Cost Pages Templates\3Proposal - 401(a) 457(b) Cost pages\401(a) 457(b) FS LS Cost Pages"
PropFolder4 = "I:\Ryan Plew\Cost Pages Templates\4Proposal - 403(b) Cost pages\403(b) FS LS Cost Pages"
PropFolder5 = "I:\Ryan Plew\Cost Pages Templates\5Proposal - 457(b) Cost pages\457(b) Voluntary Cost Pages"
Kill PropFolder1
Kill PropFolder2
Kill PropFolder3
Kill PropFolder4
Kill PropFolder5

End Sub

 

[RP1America]

Could you elaborate on (assuming you have set ScreenUpdate=false) "Didn't work"?

ScreenUpdating is set to False. The Progress Bar (userform) shows for a moment then goes away.

My (underdeveloped) code is shown above. Any thoughts?

Thanks!

 

[fumei]

The Progress Bar (userform) shows for a moment then goes away."

Of course it does.

Private Sub UserForm_Activate()

' yadda yadda
[b][COLOR=red]Unload me[/color red][/b]
End Sub

So your Activate activates...and then unloads.

Let's look at your list:

1) userform2, which contains a progress bar and a command button titled: abort, shows and retains focus throughout process
2) progress bar works properly.
3) abort button to be programmed so that the process stops if the user presses it.

#1 is not possible, if it is on a separate userform. Here is your code for the first userform.

Private Sub cmdCreate_click()

Me.Hide

'show progress bar / abort userfrom
frmProgressBar.Show

Application.ScreenUpdating = False

'create "out" folders
Call CreateFolders

'open, merge, save, close all docs
Call Proposal1
Call Proposal2
Call Proposal3
Call Proposal4
Call Proposal5

The problem is that:

Private Sub cmdAbort_Click()

''''MISSING CODE - HOW TO STOP PROCESS FROM RUNNING

can not do that. While THIS userform (frmProgressBar) is live, there are no processing running!

Private Sub cmdCreate_click()

Me.Hide

'show progress bar / abort userfrom
frmProgressBar.Show
[COLOR=red]' frmProgressBar has focus...until it unloads
' and while it DOES have focus, the following process are not executed
' thus the ABORT button can not do "stop" anything!
' nothing has started yet.
[/color red]
Application.ScreenUpdating = False

'create "out" folders
Call CreateFolders

'open, merge, save, close all docs
Call Proposal1
Call Proposal2
Call Proposal3
Call Proposal4
Call Proposal5

You must read up on Scope.

Plus, have you actually tried Stepping through your code? I strongly suggest you bring that into your standard debugging tools. If you step-through you can see for yourself, that when frmProductCost is hidden (.Hide) and frmProgressBar gets shown, yup, there is your label, but your Abort will not work, because there are no process executing. They will not until focus has been returned to the set of instructions in frmProductCost. And THAT will not happen until you hide/unload frmProgressbar.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[combo]

One thought about ScreenUpdating: it refers to host application (word, excel) and does not affect vba userform.
Concerning breaking the code, I would create a variable and test it successively after DoEvents (global scope if should affect code in module):

 Private Cancel As Boolean

Private Sub CommandButton1_Click()
Cancel = False
For i = 1 To 100000
    DoEvents
    Label1.Caption = i
    If Cancel Then Exit For
Next i
MsgBox i
End Sub

Private Sub CommandButton2_Click()
Cancel = True
End Sub

BTW, be careful with ‘Activate’ event, it fires when you unhiding the form too. The ‘Initialize’ is more secure if the code is to be executed once.

combo

 

[TonyJollans]

I don't think ScreenUpdating is going to help in this situation - there is too much going on that can override it. I don't know what you are doing with all your documents but I would try opening them with Visible:=False

I must disagree with Gerry - your list, 1 2 3, is easy enough to achieve. Here is a rough and ready example ...

Create a UserForm called [blue]MainForm[/blue]. For testing I have a single OK button, called [blue]OKButton[/blue]. Add this code to it:

[blue]Option Explicit

Private Sub UserForm_Initialize()
    Set UF2 = New ProgressForm
    UF2.Show
End Sub

Private Sub OKButton_Click()
    Unload Me
End Sub[/blue]

Now create another UserForm, called [blue]ProgressForm[/blue]. Add a label to this form, call it [blue]Label[/blue], remove the caption from it and give it a coloured BackColor (to make it visible against the UserForm background). Also add a button and call it [blue]AbortButton[/blue]. Add this code to it:

[blue]Option Explicit

Private Sub UserForm_Activate()
    Me.Label.Width = 0
    Me.Repaint
    Dim i As Long, j As Long
    Dim TempDoc As Document
    For j = 1 To 10
        Set TempDoc = Documents.Add(Visible:=False)
        For i = -10 ^ 8 To 10 ^ 8: Next [green]' Artificial delay[/green]
        TempDoc.Close wdDoNotSaveChanges
        UF2.Label.Width = UF2.Label.Width + 20
        DoEvents
        If Aborted Then Exit For
    Next
    Unload Me
End Sub

Private Sub AbortButton_Click()
    If MsgBox("Really Abort?", vbQuestion + vbYesNo) = vbYes Then
        Aborted = True
    End If
End Sub[/blue]

Finally, add a Module and put this code in it:

[blue]Option Explicit

Public UF2 As ProgressForm
Public Aborted As Boolean

Sub DoIt()
    Dim UF1 As MainForm
    Aborted = False
    Set UF1 = New MainForm
    If Not Aborted Then UF1.Show
    Unload UF1
    Set UF1 = Nothing
    Set UF2 = Nothing
End Sub[/blue]

And run the DoIt routine.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

My point was that {b]as the OP had it coded[/b] it was not possible.

Yes, it IS possible if you change the methods used, as you have done - adding the use of DoEvents, additional modules, and additional objects.

More importantly...the major change is that you have moved the processes (the procedures actioning) from the MainForm (the OP's frmProductCost) to the sub-form (the OP's frmProgressBar).

This is highly significant, and ties in exactly with what I was trying to say. Yes, your suggestions absolutely work (I just did them), but the reason it does is that the instructions to BE aborted have been moved.

To be more specific:

In your example, the button of the first userform does one thing...unloads the userform.

Private Sub OKButton_Click()
    Unload Me
End Sub

In the OP's userform it is THAT button (and the focus required) that does the actions.

Private Sub cmdCreate_click()

Me.Hide

'show progress bar / abort userfrom
frmProgressBar.Show

Application.ScreenUpdating = False

'create "out" folders
Call CreateFolders

'open, merge, save, close all docs
Call Proposal1
Call Proposal2
Call Proposal3
Call Proposal4
Call Proposal5

People put progress bars on the userform that is performing the actions. Which is what you have done by having those instructions on the second userform. Thus, they are in Scope. If your actions:

    For j = 1 To 10
        Set TempDoc = Documents.Add(Visible:=False)
        For i = -10 ^ 8 To 10 ^ 8: Next ' Artificial delay
        TempDoc.Close wdDoNotSaveChanges
        UF2.Label.Width = UF2.Label.Width + 20

are in the first userform (where the OP has it, or the equivalent) and THAT userform is .Hide, with the second as .Show, then the instructions will not be executed.

This is what I have repeatedly mentioned. Scope. You changed the Scope by putting the progress bar on the SAME userform as the instructions to BE aborted. Thus Abort is...in Scope, and can work. If they are not in Scope, then...they are not.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[RP1America]

If I make frmProgressBar modeless, then the code does excecute that is in frmProductCost (with frmProgressBar.Show just prior to that code that starts creating the documents). However, now frmProgressBar shows as a userform that is completely white (blank). I have read on other forums to use either .repaint or DoEvents, yet I cannot seem to get either to display the form correctly.

frmProductCost

Private Sub cmdCreate_click()

Me.Hide

Application.ScreenUpdating = False
Application.Visible = False

'show progress bar / abort userfrom
Load frmProgressBar
frmProgressBar.Show

'create "out" folders
Call CreateFolders

'open, merge, save, close all docs
Call Proposal1
Call Proposal2
Call Proposal3
Call Proposal4
Call Proposal5
Call Proposal6

Application.Visible = True

MsgBox "File Creation Complete.", vbOKOnly, "Complete"

'close document if more Word documents are open, otherwise close Word application
If Documents.Count > 1 Then
   '  more than one doc open
   ActiveDocument.Close
Else  ' count = 1
   ActiveDocument.Close
   Application.Quit
End If


End Sub

frmProgressBar

Private Sub UserForm_Activate()

Dim lDocCount As Long
Dim i As Integer
Dim doc As Document
Dim lMaxProgressBarWidth As Long
Dim sIncrement As Single

' Resize the UserForm
Me.Width = 240
Me.Height = 120

' Resize the label
Me.Label1.Caption = ""
Me.Label1.Width = 0
Me.Label1.BackColor = wdColorBlue

lMaxProgressBarWidth = 200
lDocCount = 287
sIncrement = lMaxProgressBarWidth / lDocCount
i = 1



For Each doc In Documents
    Me.Label1.Width = Format(Me.Label1.Width + sIncrement, "#.##")
    Me.Caption = "Creating " & CStr(i) & " of " & CStr(lDocCount)
    Me.Repaint

    
i = i + 1
Next doc


Unload Me

End Sub

 

[TonyJollans]

Gerry, as always, you analyse the original code far more thoroughly than I do - I tend to skim it and hope I've got the gist of it right.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[TonyJollans]

RP1,

It's really hard to see what you're doing. Amongst other things, although you set 'doc count' to 287, that's hard coded and not (I hope) the number of documents you have open, and your loop is based on the number of documents you do have open.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[RP1America]

although you set 'doc count' to 287, that's hard coded and not (I hope) the number of documents you have open, and your loop is based on the number of documents you do have open.

That is the number of documents that open, populate bookmarks within (based on user input), save as, and close in succession. Therefore they are not all open at one time, yet one directly after the other.

If the code I have for that loop is based on how many docs are open at one time, how could I change that to how many docs are run through the process stated above?

Also, any ideas on the modeless form showing as white(blank)?

Thanks!

 

[TonyJollans]

Just like any other loop, you identify what ever controls how many times it is done. It's difficult when your code doesn't show what really happens but something, somewhere in your code, either knows how many documents there are, and what they're called, or where to find them, or some way to either count them or otherwise know when it's finished - you tell me what it is, I don't know.

The point I was trying to make was that you probably don't see much effect on your progress bar from the loop you posted - how many times does it actually run (and increment the bar width by 200/287)?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website