Search code examples
vbams-project

Can you have a run-time window instead of a MsgBox for output?

The code I currently works as follows: I type in a UID and then a message box shows the slack of a task. However, it's not possible to edit the Microsoft Project file while the message box is open.

Is there another object I can use in VBA to show the same output but allow me to work on the project file while having the output out? And, is it possible to have the output be in real time? In other words, if I make changes in my schedule, can I see the output constantly change if the slack changes as I make changes without having to run the application again?

Sub SlackFinder()
    Dim User_UID, User_ID As Integer
    Dim Slack As Variant
    Dim NewSlack As Variant

    User_UID = InputBox("Enter UID for slack:")
    If User_UID = "" Then Exit Sub
    On Error GoTo Error_Not_Found
    User_ID = ActiveProject.Tasks.UniqueID(User_UID).ID
    On Error GoTo Error_Collapsed
    Slack = ActiveProject.Tasks.UniqueID(User_UID).TotalSlack
    NewSlack = Slack / 480
    MsgBox "Total Slack: " & NewSlack
    Exit Sub

Error_Not_Found:
    MsgBox "UID " & User_UID & " not found in " & ActiveProject.Name
    Exit Sub

Error_Collapsed:
    MsgBox "UID is present but cannot be selected.  Perhaps it is collapsed?", vbOKOnly, "COLLAPSED UID?"
    Exit Sub
End Sub
Solution

  • You can show real-time slack using a modeless userform. Create a userform in VBA, for example something that has a textbox for entering the task UID and a label to display the Total Slack value:

    enter image description here

    Then add this code to the UserForm module:

    Private Sub UID_Change()
    UpdateTotalSlack
End Sub

Sub UpdateTotalSlack()
    On Error Resume Next
    Me.TSlack = "Total Slack = " & ActiveProject.Tasks.UniqueID(Me.UID).TotalSlack / 480
End Sub

    Add this to the Project module:

    Sub ShowSlack()
    UserForm1.Show False
End Sub

Private Sub Project_Change(ByVal pj As Project)
    UserForm1.UpdateTotalSlack
End Sub

    To start, call the ShowSlack procedure. This shows the user form modelessly (e.g. it floats above the MS Project window, allowing you to make changes in the schedule). Enter a Task UID in the textbox and the Total Slack will be displayed immediately and updated whenever changes are made to the schedule (thanks to the Change event code).