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.
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")