Passing variable from module to user form

Question

I've searched a lot of pages and don't have a clear answer to this.

I have VBA code which does a lot of processing of Word revisions and I'd like to display a userform to inform the user of what's happening.

I track two variables (TotalRevisionCount and RevisionIndex) which count the total number of revisions being processed, and the individual revision number respectively. I've declared these as public variables in my main module.

I'd like my userform to display "Processing revision 2 of 36". I think the code to do this is really simple but I just can't join the dots.

Help?

***UPDATE This is a cut down version of the calling code:

Sub TestSub()
updateTotal = 10000

PlsWaitForm.Show (False)

For i = 1 To 10000
    UpdateNum = i
    PlsWaitForm.Repaint
Next i
Unload PlsWaitForm

End Sub

...and this is what I have in my userform:

Sub DisplayStuff()
    PlsWaitText.Caption = "Currently processing " & UpdateNum & " of " UpdateTotal & " records."
End Sub

Since you have declared TotalRevisionCount and RevisionIndex as public variables in your module, you should be able to use it from your Userform directly. If there are public variables sharing the same name across multiple modules (which is a bad practice) or you have a module-level (or even sub-level) variable of the same name (also a bad practice) in your Userform, then you can be specific by calling your module name followed by the variable e.g. Module1.TotalRevisionCount — Raymond Wu
– Raymond Wu, Commented Oct 1, 2021 at 14:50
So what does the userform code look like? I tried just displaying a messagebox with the two parameters, and then repainting the form from the main module, but that did nothing. — Stuart L
– Stuart L, Commented Oct 1, 2021 at 15:11
Are you calling the code from a Module or in the Userform? and how do you want to display the message? Like in a Label control? the Userform's Caption? You need to give more specific details to help you properly or else all you can get from us is generic answer. @StuartL — Raymond Wu
– Raymond Wu, Commented Oct 1, 2021 at 15:17
If all you want to display is simply a message to show the user its progress, you can consider using Application.Statusbar word.tips.net/… @StuartL — Raymond Wu
– Raymond Wu, Commented Oct 1, 2021 at 15:21
Please edit your question and put your code there. As you can see, it's usually difficult to read codes off comment. — Raymond Wu
– Raymond Wu, Commented Oct 1, 2021 at 15:28

Raymond Wu · Accepted Answer · 2021-10-01 15:39:52Z

1

Your code is not calling DisplayStuff in the loop, if you step through your code you would notice that the code did not reach DisplayStuff at all and thus, unable to update the Caption.

Code in Module1:

Public UpdateTotal As Long
Public UpdateNum As Long

Sub TestSub()
    UpdateTotal = 10000
    
    PlsWaitForm.Show (False)
    
    For i = 1 To 10000
        UpdateNum = i
        PlsWaitForm.DisplayStuff
    Next i
    
    Unload PlsWaitForm

End Sub

Code in PlsWaitForm:

Public Sub DisplayStuff()
    Me.Caption = "Currently processing " & UpdateNum & " of " & UpdateTotal & " records."
End Sub

Note: To reiterate what I said in my comment, if all you want to display is this message to your user of its progress, you can consider using Application.StatusBar instead.

answered Oct 1, 2021 at 15:39

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stuart L Over a year ago

Thankyou for that. Yes, I considered Application.StatusBar but I wanted something a bit more in the user's face. This is very nearly what I want, but I have a label on my form that I want to hold the updated text. I thought I could just use PlsWaitForm.PlsWaitText.Caption = but that just results in a grey box. How do I update the label caption, rather than the form caption?

Raymond Wu Over a year ago

Try replacing Me.Caption to PlsWaitText.Caption. @StuartL

Raymond Wu Over a year ago

@StuartL Good to know, please accept the answer since it resolves your problem, thanks

JohnRC · Accepted Answer · 2021-10-01 15:45:59Z

0

On the form, implement a function to update the displayed information on the form, like this:

Public Function UpdateDisplayInfo()

   labCurrentSelectedCell.Caption = Sheet1.selectedCellAddress 

   If Not Me.Visible Then
      Me.Show False
   End If

   DoEvents

End Function

In code doing the processing, insert a call to UserFormName.UpdateDisplayInfo() at a convenient point in the processing loop, like this:

    Public selectedCellAddress as string

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        Me.selectedCellAddress = Target.Address

        UserForm1.UpdateDisplayInfo

    End Sub

edited Oct 1, 2021 at 15:45

answered Oct 1, 2021 at 15:32

JohnRC

1,3711 gold badge11 silver badges13 bronze badges

Comments

Stuart L · Accepted Answer · 2021-10-02 05:55:53Z

0

The missing piece was the RePaint statement. So the final module code looks like this:

Sub TestSub()
updateTotal = 100000

PlsWaitForm.Show (False)

For i = 1 To 1000000
    UpdateNum = i
    PlsWaitForm.DisplayStuff
Next i
Unload PlsWaitForm

End Sub

...and the final form code looks like this:

Public Sub DisplayStuff()

    PlsWaitForm.PlsWaitText.Caption = "Currently processing " & UpdateNum & " of " & updateTotal & " records."
    PlsWaitForm.Repaint
    
End Sub

Thanks for all your help and patience, everyone.

answered Oct 2, 2021 at 5:55

Stuart L

18111 bronze badges

Collectives™ on Stack Overflow

Passing variable from module to user form

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related