MS Project VB Loop Statement

[remeng]

Hi all,

I have a form in MS Project 2010 that I am trying to create. I am thinking the Loop statement might be the way to go for this, but I'm not sure since I haven't used VB in a while. Here is the scenario; I want to have a series of check boxes that will allow the user to select different tests to perform. I already have created a single check box scenario that works, but I don't know if there is a better way to go through 20 different check boxes since I don't want to repeat common code (checking status of the next box). I want to see if it the check box is either True or False and to take a similar action to the ElseIf statement then go to the next check box status.

Example:

[ ] Test 1
[x] Test 2
[x] Test 3
[ ] Test 4

returns to MS Project:

Test 2 is added to the time line.
Test 3 is added to the time line.

Test 1 is ignored.
Test 4 is ignored.

Private Sub CommandButton1_Click()

Dim ckeck
Dim bob
Dim test_identifier

check = CheckBox1


If check = False Then

bob = test_identifier_value

    SelectTaskField Row:=0, Column:="Name"
        SetTaskField Field:="Name", Value:=bob
        SelectTaskField Row:=1, Column:="Name"
        OutlineIndent

ElseIf CheckBox1.Value = True Then

bob = "test identifier"

    SelectTaskField Row:=0, Column:="Name"
        SetTaskField Field:="Name", Value:=bob
        SelectTaskField Row:=1, Column:="Name"
        OutlineIndent

End If

End Sub

Thank you,

Mike

 

[Andrzejek]

Please, look at this post of how to loop thru controls on the Form

Have fun.

---- Andy

 

[remeng]

I read the article but it doesn't really make all that much sense to me. I'm not a power user. Can you please walk me through it or recommend another method that might be a little easier even if it is longer? Thank you

 

[Andrzejek]

You can loop thru controls on the Form like it is stated in the link I gave you, but that not necessary mean it will go thru them in the order you want.

What you can do is name your checkboxes like chk1 to chk20:[tt]
[ ] Test 1 - chk1
[x] Test 2 - chk2
[x] Test 3 - chk3
[ ] Test 4 - chk4
...
.......... - chk20[/tt]

And you can then do something like this:

Dim i As Integer

For i = 1 To 20
   If Me.Controls("chk" & i).Value Then
       [green]'chkX is selected[/green]
       ...
   Else
       [green]'chkX is not selected[/green]
       ...
   End If
Next i

Have fun.

---- Andy

 

[remeng]

Hi Andy,

I'm sorry I didn't mention something in the original post. Each field is pre-named by another person. The have names like Bob, George, Frank so that they can easily be identified. This is why I didn't think that I would be able to do a simple loop like you said. Should I code it so that Bob = 1 and so on or is there another solution that would prevent me from adding conversion steps?

 

[strongm]

>The have names like Bob, George, Frank so that they can easily be identified

Er ... identified as what?

 

[remeng]

Frank = Bob1
Amy = Bob2
George = Bob3

 

[Andrzejek]

Bummer!

OK, another approach. Use Tag property of your checkboxes. Set Bob's to 1, George's to 2, Frank's to 3, etc.

Dim c As msforms.Control
Dim i As Integer

For i = 1 To 20
  For Each c In UserForm1.Controls
    If TypeOf c Is msforms.CheckBox Then
      If c.Tag = i Then
        If c.Value Then[green]
          'Checkboc is checked[/green]
          ...
        Else[green]
          'Checkbox is UNchecked[/green]
          ...
        End If
        Exit For
      End If
    End If
  Next c 
Next i

Have fun.

---- Andy

 

[remeng]

So would it look something like this?

Private Sub CommandButton1_Click()

dim bob
dim frank
dim george
dim i=Integer

bob = 1
frank = 2
george = 3

For i = 1 To 20
  For Each c In UserForm1.Controls
    If TypeOf c Is msforms.CheckBox Then
      If c.Tag = i Then
        If c.Value Then
          'Checkboc is checked
          ...
        Else
          'Checkbox is UNchecked
          ...
        End If
        Exit For
      End If
    End If
  Next c 
Next i

 

[Andrzejek]

No.

Delete this from your code:
[tt]dim bob
dim frank
dim george
dim i=Integer

bob = 1
frank = 2
george = 3
[/tt]

In design mode, click on your Bob checkbox, in Properties find Tag property, type "1" in it, hit Enter. Click on Frank checkbox, in Tag property type "2", etc.

On the side note,
What if Bob wins big in Las Vegas and leaves the job, and Frank gets fired? George gets Bob's job, and new hired Susie gets Frank's job. Tom from other Dept gets George position. Your code will be a mess to maintain. :-(


Have fun.

---- Andy

 

[remeng]

Andy,

Bob isn't really an issue since Bob is really a sub for a testing process name. Since you mentioned it though, is there a way to pull the check box names from a table? I also want to be able to show the testing time in hours in a text box to the right. If possible as I adjust the Gantt chart times, it would be nice if the form could be updated too.

 

[Andrzejek]

I am getting lost here - you said: "Each field is pre-named by another person", now it looks like you want to name your controls with the data from the data base:

"is there a way to pull the check box names from a table?" Well, yes and no.
Check box name - No, check box CAPTION (what you and users see on the Form) - Yes, and you should do that.

BTW - Do you know the difference between .Name and .Caption (or .Text) property of the control on the Form? I don't want to insult anybody here, just asking.

Have fun.

---- Andy

 

[remeng]

As I understand it, the caption is the text that appears next to the box. The name is what VB references (this is what the other person named). What I am thinking is Since they might and or remove tests, that it might be a good idea to create a second form to pull from or edit. Could this be done like how you can create a hidden tab in Excel that you can pull from and edit?

 

[strongm]

>now it looks like

Hence my earlier question concerning bob, frank and george.

>>"is there a way to pull the check box names from a table?" Well, yes and no.
>Check box name - No, check box CAPTION (what you and users see on the Form) - Yes, and you should do that.

Actually you CAN pull a checkbox name from a table and then use it, but is there any point here.

>Bob is really a sub for a testing process name

So put bob in the tag ... if the checkbox is true, use the tag ...

 

[Andrzejek]

Looks like we understand .Name and .Caption the same way. Great.

If you want to loop thru your check boxes on the Form in certain order, there must be some numbering schema (in my opinion) either in the Name of the checkboxes or in their Tag (look at the previous code examples). Unless you don't care in which order your checkboxes will be proccessed.

Yes, you can retrieve the names of employees from MSProject table if you have them stored in any MSProject table and use them as Caption of your check boxes.

Strongm,
Let me clarify my “Check box name - No” in my previous post. The ‘No’ part was really ‘not recommended, really bad idea’. In theory – you are right, Yes you can, but why would anyone wanted to do that?


Have fun.

---- Andy

 

[strongm]

> ‘not recommended, really bad idea’

Why? Seems perfectly OK to me. Just not really necessary here, as far as I can tell.

Very little difference from your earlier suggestion of building the checkbox name:

>If Me.Controls("chk" & i).Value Then

So if I read "Textbox1" into a variable such as CopntrolName...

Controls(ControlName).Text = "Very straightforward"

> certain order

Not sure that is quite the requirement; just one way of solving it.

 

[remeng]

Hey guys lets just use the KISS method. I'm going to look at this and talk with some people to see which direction with the fields I will need to go. I'll get back with you when I have some more info.

Thanks,

Mike

 

[strongm]

>KISS

Quite so. That's where I'm trying to get. For example, if your ElseIf is genuinely representative of what you need to do, and the selection of a particular checkbox simply defines the specific test identifie that will be added to the taks list, then you could simply put the task identifier in the tag of the checkbox, and use code this simple:

[blue]
Private Sub CommandButton1_Click()
    Dim checkresult As Control
    For Each checkresult In Controls
        If TypeOf checkresult Is MSForms.CheckBox Then
            Select Case checkresult.Value
                Case True
                    InsertNamedTask checkresult.Tag
                Case Else
            End Select
        End If
    Next
End Sub

Public Sub InsertNamedTask(strName As String)
     SelectTaskField Row:=0, Column:="Name"
        SetTaskField Field:="Name", Value:=strName
        SelectTaskField Row:=1, Column:="Name"
        OutlineIndent
End Sub[/blue]

 

[remeng]

Update: I broke MS Project lol.

This is currently on hold since I can't create the form or have it save. I'll keep you posted to the progress.

Mike

 

[Andrzejek]

Strongm, you are right. OP never stated the check boxes have to be processed in any particular order. I guess sometimes I see ‘stuff’ that is not there (it’s only in my head) and stick to it. :-(

Have fun.

---- Andy