VBA syntax to find first available free row

[Brian Lukanic]

I'm writing a form that accepts some input by the user and then proceeds to
build out a task based on those variables. At some point in the script it
will need to go to the first available free line to enter the task. Can
anyone help me with the syntax? I've got Rod's book but can't seem to find
that code anywhere.

 

[Rod Gill]

Hi,

ActiveProject.Tasks.Add automatically adds the new Task to the end of the
schedule (first empty row) unless you specify a Before parameter.
Thanks for buying the book!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Brian Lukanic]

Answered by the man himself. Thanks, Rod. Very good book, btw.

 

[Rod Gill]

Thank you!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Brian Lukanic]

Rod,

A followup question: I'm now able to create a task via a form the way I
want, but what I REALLY want to do is create an entire work package from this
form. That means the first task would be given a name and serve as a summary
task, and then four child tasks would get created. My original goal was to
simply record a macro to help me with the remaing syntax for the four
subtasks. But the problem is that although I can create a new task at the
first available free row, the active task is really still wherever the curser
happens to be, so relative cell references won't work. So I think there are
two options, but I need assistance.

Option 1: Is there a way to "set" the active cell to be the newly created
task while in the middle of the macro, thereby allowing me to create the
remaining tasks via relative references?
Option 2: Should I instead continue to use ActiveProject.xxx.xxx to create
the subtasks?

Reason I hope to use relative references is because somewhere in the macro I
want to do other things such as copy a cell and then paste the link into
another cell (long story as to why I want to do this,) which is something I
doubt I can do programmatically without using relative references to the
tasks I just created.

Below is my current code. Assume a simple form exists already.


Option Explicit

Private Sub UserForm_Initialize()
'Macro that runs the first time the form opens
MyTextBox.Value = ""
MyTextBox.SetFocus
End Sub

Private Sub RunMyMacro_Click()
Dim tskNew As Task
Set tskNew = ActiveProject.Tasks.Add
tskNew.Name = MyTextBox
MyUserForm.Hide
End Sub

 

[Rod Gill]

There is no need to ever move the cursor and you can turn relative tasks off
(see help). I think everything you want to do can be done, try recording
macros of it all to get you started.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Brian Lukanic]

Well I figured out everything and got it to work exactly as I need, and
without using cursor movements. Rod, I am now a beleiver in the fact that
it's best to NOT try to move the cursor in the script, as it's a much more
efficient set of code to do it without using the cursor. I wrote the script
in both options and it was much better to do it as listed below (Which I am
listing here just to close the door in this thread in case anyone else ever
is curious.)

There's probably some efficiencies that can be put into my code, but I am
happy with it as it is. Thanks again Rod!


Option Explicit

Private Sub UserForm_Initialize()
'Dim MyControl As Object
'Dim ControlsIndex As Integer
'Dim MyButtonLeft As Variant
'Dim MyButtonRight As Variant

OptionButton1.Value = True

'Macro that runs the first time the form opens
MyTextBox.Value = ""
MyTextBox.SetFocus
End Sub

Private Sub UserForm_Load()
'Macro that runs the first time the form opens
MyTextBox.Value = ""
MyButtonLeft.Value = 0
MyButtonRight.Value = 0
MyTextBox.SetFocus
End Sub

Private Sub RunMyMacro_Click()
Dim tskNewSummary As Task
Dim tskNewChild1 As Task
Dim tskNewChild2 As Task
Dim tskNewChild3 As Task
Dim tskNewChild4 As Task


If OptionButton1.Value = True Then

Set tskNewSummary = ActiveProject.Tasks.Add
Set tskNewChild1 = ActiveProject.Tasks.Add
Set tskNewChild2 = ActiveProject.Tasks.Add
Set tskNewChild3 = ActiveProject.Tasks.Add
Set tskNewChild4 = ActiveProject.Tasks.Add

tskNewSummary.Name = MyTextBox
tskNewSummary.OutlineLevel = "1"
tskNewChild1.OutlineLevel = "2"
tskNewChild2.OutlineLevel = "2"
tskNewChild3.OutlineLevel = "2"
tskNewChild4.OutlineLevel = "2"
tskNewChild1.Estimated = "No"
tskNewChild2.Estimated = "No"
tskNewChild3.Estimated = "No"
tskNewChild4.Estimated = "No"
tskNewChild1.Name = "Analysis for " & MyTextBox
tskNewChild2.Name = "Signoff for " & MyTextBox
tskNewChild3.Name = "Development for " & MyTextBox
tskNewChild4.Name = "QA for " & MyTextBox
tskNewChild1.ResourceNames = "Generic"
tskNewChild2.ResourceNames = "Generic"
tskNewChild3.ResourceNames = "Generic"
tskNewChild4.ResourceNames = "Generic"

'This procedure copies the Analyst to the summary row
SelectTaskField Row:=tskNewChild1.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text17"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False
'This procedure copies the Developer to the summary row
SelectTaskField Row:=tskNewChild3.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text18"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False
'This procedure copies the QA Engineer to the summary row
SelectTaskField Row:=tskNewChild4.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text19"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False

Else
Set tskNewSummary = ActiveProject.Tasks.Add
Set tskNewChild1 = ActiveProject.Tasks.Add
Set tskNewChild2 = ActiveProject.Tasks.Add
Set tskNewChild3 = ActiveProject.Tasks.Add

tskNewSummary.Name = MyTextBox
tskNewSummary.OutlineLevel = "1"
tskNewChild1.OutlineLevel = "2"
tskNewChild2.OutlineLevel = "2"
tskNewChild3.OutlineLevel = "2"
tskNewChild1.Estimated = "No"
tskNewChild2.Estimated = "No"
tskNewChild3.Estimated = "No"
tskNewChild1.Name = "Analysis for " & MyTextBox
tskNewChild2.Name = "Signoff for " & MyTextBox
tskNewChild3.Name = "Development/QA for " & MyTextBox
tskNewChild1.ResourceNames = "Generic"
tskNewChild2.ResourceNames = "Generic"
tskNewChild3.ResourceNames = "Generic"

'This procedure copies the Analyst to the summary row
SelectTaskField Row:=tskNewChild1.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text17"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False
'This procedure copies the Developer to the summary row
SelectTaskField Row:=tskNewChild3.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text18"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False
'This procedure copies the QA Engineer to the summary row
SelectTaskField Row:=tskNewChild3.ID, RowRelative:=False,
Column:="Resource Names"
EditCopy
SelectTaskField Row:=tskNewSummary.ID, RowRelative:=False,
Column:="Text19"
EditPasteSpecial Link:=True, Type:=2, DisplayAsIcon:=False
End If

MyUserForm.Hide
MyTextBox.Value = ""
MyTextBox.SetFocus

End Sub