0

I have an issue. I'm working on VBA macros in MS Project 2013, that can automatically fill and change lookup table, which linked with local custom field in project professional. I have these code sections on VBA for:

-adding entries 

Set objStateEntry = objOutlineCode.LookupTable.AddChild(entryName)

-changing descrption of entries

objStateEntry.Description = "some description"

-changing level of entires

objStateEntry.level = entryLevel

But I can't find how to programmatically move entries up/down in lookup table. In other words I need to use marked in the screenshot buttons programmatically. Please help me. Thank you!

the screenshot

aduguid
  • 3,099
  • 6
  • 18
  • 37
Александр Велиляев
  • 1
  • 1

2 Answers2

0

Try something like this:

Private Sub SpinButton1_SpinDown()
On Error Resume Next
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
 With Me.ListBox1
     .ListIndex = .ListIndex + 1
 End With
End Sub


Private Sub SpinButton1_SpinUp()
On Error Resume Next
If ListBox1.ListIndex = 0 Then Exit Sub
  With Me.ListBox1
       .ListIndex = .ListIndex - 1
  End With
 End Sub

goodluck

jane
  • 1
0

I tried recording a macro while using the spin button you highlighted, but this indicates that VBA can't control the spin button... So it seems the only way to change the index of a lookup table entry by VBA is to first delete the entry then add it back, for example moving entry at index=4 up to index=2 (after saving the name and description of entry index=4):

Dim lteName As String
Dim lteDesc As String
lteName = Application.CustomFieldValueListGetItem(pjCustomResourceOutlineCode2, pjValueListValue, 4)
lteDesc = Application.CustomFieldValueListGetItem(pjCustomResourceOutlineCode2, pjValueListDescription, 4)
Application.CustomFieldValueListDelete FieldID:=pjCustomResourceOutlineCode2, Index:=4
Application.CustomFieldValueListAdd FieldID:=pjCustomResourceOutlineCode2, Value:=lteName, Description:=lteDesc, Index:=2

Two caveats: 1: It appears you can't do the above in the same macro that you are adding the lookup table entries. It only works in another macro run after adding the entries. 2: At least in my version of MS Project, the index number is flaky (it should be consecutive but sometimes index numbers repeat or there are gaps but then it corrects itself!), no doubt due to deleting and adding entries like I am suggesting. The code won't work if the index numbers that VBA is looking for don't match what is displayed in the lookuptable window. Oh dear... wish MSProject was perfect! Noted same behaviour on another SO thread here.

Stephen Richards
  • 23
  • 5