Excel VBA Won't Keep Leading Zeroes

Question

I can't seem to find a way to save leading zeros in my VBA code. The zeros are necessary since they correspond to unique IDs. I've tried changing the number format to text and 0000.... in excel and the same approach in my actual code: ActiveSheet.Cells(i, j).NumberFormat = "00000" Any other suggestions? (If I manually type these numbers into VBE it strips the leading zeros too). EDIT: ``` Sheets.Add.Name = "Temp" Sheets("Sheet1").Select ActiveSheet.Cells(2, 2).NumberFormat = "000" cid = Cells(2, 2) MsgBox cid Sheets("Sheet2").Select ActiveSheet.Cells(6, 1).NumberFormat = "00000" sid = Cells(6, 1) Sheets("Temp").Select Url = _ "URL;" & _ "http......asp?" & _ "X1=" & cid & "&" & _ "X2=" & sid & "&" ``` This is inside a loop ultimately but I'm debugging as individual iterations.

Accepted Answer

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 integer
v = val(Sheets("Sheet1").Cells(2, 2))
dim cid as string
cid = 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 String
s = "0002"
Worksheets(1).Cells(1, "A").Value = "'" & s
```

Excel VBA Won't Keep Leading Zeroes

Solution

Problem