Progress Meter Property Values

Home Programming VBA Topic Index

You cannot pass arguments from a code module directly into a Userform.

One of the ways to do this is to assign properties to the Userform.

I have renamed one of my Userforms to frmProgress and I have the following code at the top of the Userform.

Private strExt As String
Private strProcName As String
Private intResultsValue As Integer

Public Property Let Ext(strInputExt As String)
   strExt = strInputExt
End Property

Public Property Let ProcName(strInputProcName As String)
   strProcName = strInputProcName
End Property

Public Property Let intResults(intInputVal As Integer)
   intResultsValue = intInputVal
End Property

Public Property Get intResults() As Integer
   intResults = intResultsValue
End Property

Now in any code module I can enter

Private NewfrmProgress As frmProgress

as the top line in the module. The private variable will be available anywhere in that code module, but not in other modules. Declaring it Public instead of Private would make it available to all the modules.

I could now set the properties with the following code.

Sub ShowUserForm()
   Set NewfrmProgress = New frmProgress
   NewfrmProgress.Ext = "myExt"
   NewfrmProgress.ProcName = "myProcName"
   NewfrmProgress.Caption = "myCaption"
   NewfrmProgress.intResults = 123
   NewfrmProgress.Show
End Sub

If you have Intellisense enabled, the new properties (Ext, Procname and intResults) will appear along with all the old properties as soon as soon as you enter the dot after newfrmProgress.

If I had a CommandButton on newfrmProgress I could use the following to show the values.

Private Sub CommandButton1_Click()
   MsgBox strExt
   MsgBox strProcName
   MsgBox Me.Caption
   MsgBox intResults
End Sub

To user the properties in the code module, I could have used.

Sub ShowUserForm()
   Set NewfrmProgress = New frmProgress
   NewfrmProgress.Ext = "myExt"
   NewfrmProgress.ProcName = "myProcName"
   NewfrmProgress.Caption = "myCaption"
   NewfrmProgress.intResults = 123
   ShowResults
End Sub

The last line in the above procedure will call the next procedure.

Sub ShowResults()
   MsgBox NewfrmProgress.Caption
   MsgBox NewfrmProgress.intResults
End Sub

When ShowResults is called from ShowUserForm it will run properly.

If you try to run ShowResults by itself, it will fail because the new instance of NewfrmProgress has not been created. It was only created in the line Set NewfrmProgress = New frmProgress in the ShowUserForm procedure. The new instance of NewfrmProgress will end as soon as the procedures end.

Also note that the following is invalid.

Sub ShowResults()
   MsgBox NewfrmProgress.Ext
   MsgBox NewfrmProgress.ProcName
End Sub

The reason is that at the code at the top of this page that was placed in my UserForm included 3 Let properties but only one Get property. The Get property was for intResults and that is the only property accessible in the code module.

Adding Get properties for Ext and ProcName in the UserForm would have made those properties available in the Code Module.

Home Programming VBA Topic Index