none
How to clear a field/cell ? RRS feed

  • Question

  • Hi guys,

    I wrote some VBA code to find the total number of resources assigned to each task and it works great, but it puts a 0 down for the summary task. What I want it to do is actually clear out or blank the cell for the summary taks.

    here is my code:


    Sub ResCount()
    Dim t As Task
    Dim assign As Assignment
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Text1 = 0
            For Each assign In t.Assignments
                t.Text1 = t.Text1 + assign.Units
            Next assign
            If t.Text1 = 0 Then
                t.Text1 = Null  <---- this doesnt work, neither does "Nothing"
            End If
        End If
    Next t
    End Sub

     

     

    thanks for the help!

    Friday, January 7, 2011 3:25 PM

Answers

  •  

     

    ok, got it

     

    Sub ResCount()

    Dim t As Task
    Dim assign As Assignment

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Not t.Summary Then
                t.Number1 = 0
                For Each assign In t.Assignments
                    t.Number1 = t.Number1 + assign.Units
                Next assign
                t.Text1 = CStr(t.Number1)
            End If
           End If
        Next t
    End Sub


    thanks for all the help guys!
    Friday, January 7, 2011 6:28 PM

All replies

  • Hi Jason,

    You should use t.Text1 = ""

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Friday, January 7, 2011 3:30 PM
    Moderator
  • that didnt work....it give me "the argument value is not valid"

     

    i changed the code a little bit so that the result goes to the number1 variable:

     

    Sub ResCount()
    Dim t As Task
    Dim assign As Assignment
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Number1 = 0
            For Each assign In t.Assignments
                t.Number1 = t.Number1 + assign.Units
            Next assign
            If t.Number1 = 0 Then
                t.Number1 = ""  <---- still doesnt work
            End If
        End If
    Next t
    End Sub

     

    still don't know how to clear out the cell

    Friday, January 7, 2011 3:48 PM
  • I also tried to set the Custom Field->Formula to:

    IIf(([Number1]=0),'',[Number1])

     

    but that still didn't work, gives me #ERROR when no resources are assigned and still gives me 0 on the summary tasks.

     

    why the heck is this so hard to do...... it should be simple like t.Number1.CLEAR

    Friday, January 7, 2011 4:52 PM
  • Setting a custom field value to "" only works for Text fields.

    The 'empty' value for a Number field is 0 (for a date field it is "NA"). So you cannot 'clear' the field the way you want. Empty means 0.
    The only option you could try is to use a graphical indicator for the number field to 'hide' the 0 value by assigning it an empty indicator. Or use a text field.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Friday, January 7, 2011 5:25 PM
    Moderator
  • If its Just about counting the Resource Count On Each Task, i am using the below mentioned macro, thought you would want to check this

    Sub ResourceCount()
        Dim ts As Tasks
        Set ts = ActiveProject.Tasks
        Dim t As Task
       
        For Each t In ts
            If Not t Is Nothing Then
                If Not t.Summary Then
                    MsgBox (t.Resources.Count())
                     't.Text2 = CStr(t.Resources.Count())
                End If
           End If
        Next t
     End Sub

    Hope this helps


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Friday, January 7, 2011 5:27 PM
    Moderator
  • Thanks for the replies.

    Hans, I will try to mess with some text fields and see what I can do.

    Sunil, I don't need a MsgBox to pop up with the resource count. I did try the Resource.Count() method, but it only does whole numbers. I had some people assigned 20% or 50% so I wanted the number to come out to 2.2 or 1.5, etc...

     

    I will report back in a bit.

    Friday, January 7, 2011 5:37 PM
  • Sorry Just missed to highlight that by including If Not t.Summary  you wouldn't have problem of replacing Summary with null or ""

     Sub ResourceCount()
        Dim ts As Tasks
        Set ts = ActiveProject.Tasks
        Dim t As Task
       
        For Each t In ts
            If Not t Is Nothing Then
                If Not t.Summary Then
                    'MsgBox (t.Resources.Count())
                     t.Text2 = CStr(t.Resources.Count()) 'Replace with whatever you want
                End If
           End If
        Next t
     End Sub


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Friday, January 7, 2011 5:43 PM
    Moderator
  •  

     

    ok, got it

     

    Sub ResCount()

    Dim t As Task
    Dim assign As Assignment

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If Not t.Summary Then
                t.Number1 = 0
                For Each assign In t.Assignments
                    t.Number1 = t.Number1 + assign.Units
                Next assign
                t.Text1 = CStr(t.Number1)
            End If
           End If
        Next t
    End Sub


    thanks for all the help guys!
    Friday, January 7, 2011 6:28 PM
  • one further question, I want my field to be automatically updated when the resources are changed. I see this: ProjectBeforeResourceChange, but that is before, I want it to happen AFTER. How would I do that?
    Friday, January 7, 2011 6:50 PM