Refer a cell value

[Sajit]

I have a Gantt chart of employees' leave planning dates created by data
copied from excel. Apart from the Leave_start and Leave_finish date columns,
there are text fields, such Empl_name, Empl_no etc. I want to make the font
of the repeating rows of the text data as white, so that it does not appear.

I had intially prepared this list in excel for pasting into project, where I
had done the formatting of the text to white. But on pasting the selection on
to project. The format in excel was not taken, the rows of text appear.

I am trying to create the same macro in project, but got stuck as to how to
refer to the value in a text cell.

This is the code that I am trying to run,

Sub Macro3()
' Macro Macro3
' Macro Recorded Tue 31/10/06
'Font Color:=7

Dim empl_no1 As String


SelectTaskField Row:=1, Column:="Text3", RowRelative:=False
empl_no1 = Text3.Value

i = 0
While empl_no <> ""

If i = 0 Then
SelectTaskField Row:=0, Column:="Text1", RowRelative:=False
Else
SelectTaskField Row:=1, Column:="Text1", RowRelative:=True
End If

If ActiveCell.Value = "" Then
SelectTaskField Row:=0, Column:="Text1", RowRelative:=False,
Width:=4, Extend:=True
Font Color:=7
End If

SelectTaskField Row:=0, Column:="Text3", RowRelative:=True
empl_no1 = ActiveCell.Value

i = i + 1
Wend


End Sub

 

[John]

I have a Gantt chart of employees' leave planning dates created by data
copied from excel. Apart from the Leave_start and Leave_finish date columns,
there are text fields, such Empl_name, Empl_no etc. I want to make the font
of the repeating rows of the text data as white, so that it does not appear.

I had intially prepared this list in excel for pasting into project, where I
had done the formatting of the text to white. But on pasting the selection on
to project. The format in excel was not taken, the rows of text appear.

I am trying to create the same macro in project, but got stuck as to how to
refer to the value in a text cell.

This is the code that I am trying to run,

Sub Macro3()
' Macro Macro3
' Macro Recorded Tue 31/10/06
'Font Color:=7

Dim empl_no1 As String


SelectTaskField Row:=1, Column:="Text3", RowRelative:=False
empl_no1 = Text3.Value

i = 0
While empl_no <> ""

If i = 0 Then
SelectTaskField Row:=0, Column:="Text1", RowRelative:=False
Else
SelectTaskField Row:=1, Column:="Text1", RowRelative:=True
End If

If ActiveCell.Value = "" Then
SelectTaskField Row:=0, Column:="Text1", RowRelative:=False,
Width:=4, Extend:=True
Font Color:=7
End If

SelectTaskField Row:=0, Column:="Text3", RowRelative:=True
empl_no1 = ActiveCell.Value

i = i + 1
Wend


End Sub

Sajit,
Although the header says you recorded the macro, you must have manually
edited it since several lines are invalid. And foreground processing
(i.e. selecting objects) is very inefficient unless you need to do
certain operations, such as applying font characteristics. However, even
though that is what you say you want to do, let me ask a question. Why
do you want to change the font to white? Could the data in those
repeated fields just be deleted instead?


It would help immensely to see the structure of your Project file. Could
you give us a sample? But don't post the actual file, just show a few
rows of sample data with the columns in the correct order. Then using
the sample data explain exactly what you want to do. Then we will be
better able to help you.

John
Project MVP

 

[Sajit]

John,
Yes, the code was created using the recorder. Only the statement 'Font
Color:=7
is not needed. The rest is required.
The reason for making the data invisible is to allow filter of the rows. If
it is deleted the filter will not happen correct.

Tried pasting the data in here, but the arrangement is going off.
I can send an excel file illustrating the rows and columns of data to your
email. Pls. send me an address to (e-mail address removed)

 

[John]

John,
Yes, the code was created using the recorder. Only the statement 'Font
Color:=7
is not needed. The rest is required.
The reason for making the data invisible is to allow filter of the rows. If
it is deleted the filter will not happen correct.

Tried pasting the data in here, but the arrangement is going off.
I can send an excel file illustrating the rows and columns of data to your
email. Pls. send me an address to (e-mail address removed)

Sajit,
I'll write to you direct.

John