Counter Macro Problem

[razor22]

I am trying to use a simple macro that will add "1" to my counter each time the worksheet is saved. I can only figure out to add 1 when I keep the number in simple context (ie 1,2,3,4,5 ....) I am using this:

Private Sub Workbook_Open()
Sheets("Sheet1").Range("K4") = Val(Sheets("Sheet1").Range("K4")) + 1
End Sub

I need for the counter to take my form number (HA000) and each time it is saved , to add 1 (ie HA001, HA002, HA003, HA004)

Thank you for your help in advance. If you need any further clarification, I will be online.

 

[Joe4]

Welcome to the Board!

Try this variation:

Sheets("Sheet1").Range("K4") = Left(Sheets("Sheet1").Range("K4"), 2) & Format(Right(Sheets("Sheet1").Range("K4"), 3) + 1, "000")

 

[njimack]

Something like this maybe:

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("K4")
.Value = "HA" & RIGHT(.Value,3) + 1
End With
End Sub

 

[razor22]

Starting to work ....

Private Sub Workbook_Open()
With Sheets("Sheet1").Range("K4")
.Value = "HA" & RIGHT(.Value,3) + 1
End With
End Sub

However it is saving now as "HA1,HA2,HA3"

I would really like for it to display "HA001,HA002,HA003"

Once again, thank you!

 

[Joe4]

I am trying to use a simple macro that will add "1" to my counter each time the worksheet is saved

Another note. You have selected the Workbook_Open event. That increments the counter every time the workbook is opened. Obviously, that incrementer won't get saved unless the save the workbook, so that should work in your scenario. But just also be aware that there is a "Workbook_BeforeSave" event procedure also, which could be used.

The big difference is, with the "Workbook_BeforeSave" event procedure, it will get updated every time the file is saved. So if people hit save multiple times in a single session, it will increment the counter that many times. Whereas if it is in the "Workbook_Open" event, it will only increment once, per time that it is opened, and if nothing is saved, the incremented value will not be saved either.

Just important to be aware of the different options so you can pick the one that works best for you.

 

[razor22]

Joe 4:

Thanks for the welcome. Your variation has worked flawlessly. I really appreciate everyone help. I will be on these threads all day now!

 

[Joe4]

njimack's solution will also work with one minor edit. Just need to add the FORMAT function to it, i.e.

Private Sub Workbook_Open()
    With Sheets("Sheet1").Range("K4")
        .Value = "HA" & Format(Right(.Value, 3) + 1, "000")
    End With
End Sub

With my solution, you can also hard-code the first part as "HA" if you like. I chose to pull the existing two character value from the existing value. This makes it a little more dynamic so it will work with any two character prefix without having to update the code.

 

[njimack]

Yeah, forgot to format it. Thanks for correct Joe.