DH Templates

Auto-increment last 2 digits of alphanumeric cell upon open excel document?

Hello there, I'm making an invoice template and need some VB trick that auto-increment by 1 the last 2 digits of an alphanumeric code e.g. "A010010010100000010" the first 17 characters have to remain fixed but the last 2 digits are the ones that I need to increment each time I open the workbook. P.D. since the cell containing the code (B9) have both text and numbers, "[B9] = [B9] + 1" didn't worked. HELP!

Public Comments

1. Assuming your sheet containing the code is 'Sheet1', you can use the following WorkBook_Open event handler. Copy the following event handler to the clipboard: Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tVal tVal = Val(Right(Range("B9"), 2)) + 1 Range("B9").Value = Left(Range("B9").Value, 17) & tVal End Sub Press ALT + F11 Double click 'This Workbook' in the Microsoft Excel Objects in the upper left quadrant. Paste the event handler into the editing area to the right. Close the VBE and return to the worksheet. Save the workbook. Each time it is opened henceforth, cell B9 will be incremented by 1. Note: Once B9 reaches 99, the next time you open it, your field length will 20 characters, ending in 100. If you wish to retain the field length at 19 characters, you will have to modify the code to test the last two characters for '99' and adjust from there.