Number Formatting when joining two strings

[Mike]

Thanks in advance for any help.

I have the following code.

Dim AA As Integer
Dim bb
Dim cc
Dim ee
AA = ActiveCell.Offset(0, 1)
bb = ActiveCell.Offset(0, -3)
cc = ActiveCell.Offset(1, -3)
ee = " "
ActiveCell = AA & ee & bb & ee & cc

AA is the employee ID. bb and cc are first and last names.
I am trying to join the three into one cell. If the
employee ID is 123 I want it to appear as 00123 in the
newly formed string.

Currently it appears as "123 Joe Smith"
I want it to be "00123 Joe Smith"

Does anyone know how to fix this? I am using Excel 2000.
Thanks again.

 

[Guest]

AA = Format(ActiveCell.Offset(0, 1), "00###")

 

[Tom Ogilvy]

Expanding on Ben's solution:

ActiveCell = format(AA,"00###") & ee & bb & ee & cc