Add +1 to a cell value (plus complications!)

[s_macloskey]

Good morning all,

I have another VBA/AcitveX/Button query that I hope someone can help me to solve!

Also please forgive me as I don't 100% know the QUESTION I am asking, I just know the result I am after.

(I have also have to redact some of the information becuase of where I work.)

Context:

Until it 'broke', we use(d) a shared sheet to generate new "case numbers" (see pictures)

I am creating a new sheet because somewhere along the line something went wrong and it is broken (and it looks like the last IT guy used Unviewable+VBA so I can't get into the VBA so see what he used)

Desired result:

(-) and (+) buttons that adds (and subtracts) 1 to a cell value (from 000 - 999)

then IDEALLY (see picture) once past 999 to go from X00XX999 to X01XX000 (then X01XX001, 002, 003...)

Now I don't know how exactly so explain this.... The last IT guy made it so that somehow the cell value displayed as e.g X00XX123, even though in the formula bar it just displayed as 123 and I have absolutely no idea how he did this... Ive attached a picture to add context.


I tried an ActiveX spin button with:

Private Sub SpinButton1_Change()
If ("C3") = vbNullString Then SpinButton1.Value = 1
Range("C3").Value = SpinButton1.Value
End Sub

But this only goes up to 100. I need it to go from 000 - 999



Any help or advice whatsoever is appreciated.



Thank you all,

S Macloskey

 

[DanteAmor]

Thank you for this

Glad we could help & thanks for the feedback