Passing variable from module to user form-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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