I have a workbook that is essentially a template. The template needs to remain intact so I am trying to write a code that on each occurrence the Workbook is opened, it generates a new reference number and assigns that reference to a specific field. I have the vba code which generates a reference number on opening the WorkBook (AH5) and then merges 2 cells (AH4 & AH5) for my reference in H4.

AH5 is formatted in excel as "0000" as the reference should be a 4 digit number starting at 1. However, when merged with AH4 I lose the formatting so my result is then LR-1 and not LR-0001. Here is my current code;

Private Sub Workbook_Open()

Range("AH5").Value = Range("AH5").Value + 1
Range("H4").Value = Range("AH4") & Range("AH5")

End Sub

How do I keep the formatting for AH5 so my result in H4 looks like LR-0001

Thanks.

I have a workbook that is essentially a template. The template needs to remain intact so I am trying to write a code that on each occurrence the Workbook is opened, it generates a new reference number and assigns that reference to a specific field. I have the vba code which generates a reference number on opening the WorkBook (AH5) and then merges 2 cells (AH4 & AH5) for my reference in H4.

AH5 is formatted in excel as "0000" as the reference should be a 4 digit number starting at 1. However, when merged with AH4 I lose the formatting so my result is then LR-1 and not LR-0001. Here is my current code;

Private Sub Workbook_Open()

Range("AH5").Value = Range("AH5").Value + 1
Range("H4").Value = Range("AH4") & Range("AH5")

End Sub

How do I keep the formatting for AH5 so my result in H4 looks like LR-0001

Thanks.

• excel
• vba

• Range("H4").Value = Range("AH4") & Format(Range("AH5"), "0000") – CDP1802 Commented Jan 20 at 14:48
• How do you keep the last used reference number and remain the template intact? – rotabor Commented Jan 20 at 15:27
• @rotabor My reference is in Cell AH5. The first part of the code looks at that cell and then adds 1. I am having to manually save while developing but my hope is to find the right code that will save the file once the first 2 steps have completed. – Paul Royle Commented Jan 20 at 15:44

1 Answer 1

First things first: You should specify the sheet. Range("AH5") will refer to the active sheet. As the code runs in the Workbook-Open event, the active sheet is always a sheet of that workbook, but that may be any sheet of that workbook (the one that was active when the last store took place).

Even if you have maybe only one worksheet: You can never be sure that you don't have another in the future. So always write qualified ranges (note the dots)

With ThisWorkbook.Sheets(1)
    .Range("AH5").Value = .Range("AH5").Value + 1
End With

(Note that the following code pieces assume that you use a With-clause).
Okay, that said: To get the formatted value of a cell, you can use the property Text:

.Range("H4").Value = .Range("AH4") & .Range("AH5").Text

However, that has a drawback: If the column is too narrow to display the text, Excel will display something like ###, and the Text-property will return those ### instead of the formatted number.

Alternative is to format the number with VBA using the format-function:

.Range("H4").Value = .Range("AH4") & format(.Range("AH5"), "0000")