Excel VBA Won't Keep Leading Zeroes

All below deliberations were about writing to a cell, not reading from it. Changing the Numberformat doesn't work that way.See if you can figure out how this code works:

dim v as integerv = val(Sheets("Sheet1").Cells(2, 2))dim cid as stringcid = format(v, "000")

You should never use Select and ActiveSheet that way, it is not necessary; and never use Cells without the sheet it should be referring to.
(Val can be omitted if the cell is really already numeric and not text).

Earlier answer... :

This code works perfectly fine for me

Worksheets(1).Columns("A").NumberFormat = "@"Worksheets(1).Cells(1, "A").Value = "00023"

And also does

Worksheets(1).Columns("A").NumberFormat = "000000"Worksheets(1).Cells(1, "A").Value = "0023"

(Here the string is converted to a number and displayed with 6 digits)

EDIT:
If that fails - although I could not explain that - I'd still bet that the ' should really work:
(No numberformat required.)

Dim s As Strings = "0002"Worksheets(1).Cells(1, "A").Value = "'" & s

Setting the format to text or "00000" should work. The small snippet below gives the results expected. With the first storing a value of 0001 and the second storing a value of 1 that is formatted to look like 0001.

This also works if the cell value was stored in a string variable.

Sub set_value()Range("A1").NumberFormat = "@"Range("A1").Value2 = "0001"Range("B1").NumberFormat = "0000"Range("B1").Value2 = "1"End Sub