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]

Select Developer menu, Design Mode, select your SpinButton, return to menu and select Properties
Change the Max property to 999

 

[s_macloskey]

Select Developer menu, Design Mode, select your SpinButton, return to menu and select Properties
Change the Max property to 999

View attachment 17967

Thank you for this

 

[RoryA]

A custom cell number format of:

\B00\M\K000

should achieve the numbers you see on your sheet. Since the cell's actual value is just a number, all you need to do is keep adding 1 to it.

 

[CephasOz]

Select the cells in question, then right-click one of the cells, click "Format Cells...", make sure the "Number" tab is showing, click on the "Custom" option, then in the "Type" field enter the following (including the double-quotes):
"X"00"XX"000
and click "OK". All of the cells will then display numbers with the interspersed "X"'s as you need. The spin button code is then easy. (If you want to display the data on the form with the interspersed "X"s, use "Cells(row,column).Text" instead of "Cells(row,column).Value").

 

[s_macloskey]

Select the cells in question, then right-click one of the cells, click "Format Cells...", make sure the "Number" tab is showing, click on the "Custom" option, then in the "Type" field enter the following (including the double-quotes):
"X"00"XX"000
and click "OK". All of the cells will then display numbers with the interspersed "X"'s as you need. The spin button code is then easy. (If you want to display the data on the form with the interspersed "X"s, use "Cells(row,column).Text" instead of "Cells(row,column).Value").

A custom cell number format of:

\B00\M\K000

should achieve the numbers you see on your sheet. Since the cell's actual value is just a number, all you need to do is keep adding 1 to it.

RoryA
CephasOz

Thank you both massively. These work so well!

CephasOz - Thanks for your reply on my other thread before it got deleted. I managed to try it out but when I went to reply to you, the thread was gone.

(Perhaps the threads were a bit similar but I'm not sure it warranted an instant deletion by a total jobsworth LMAO)

 

[CephasOz]

Glad to help.

 

[RoryA]

(Perhaps the threads were a bit similar but I'm not sure it warranted an instant deletion by a total jobsworth LMAO)

Speaking as said jobsworth, yeah, it did.

 

[s_macloskey]

Roger. Thank you for your advice nonetheless!

 

[RoryA]

Glad to help.