How to force leading zero in a format for a form's caption

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
501
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
When my userform initializes, I am using the following format in the form's caption P4-2021-##. This starts in range "A6". However, when this form is first created Range("A6") is empty and therefore should start with "P4-2021-01". When a value is entered in Range("A7") the ## should increment by one, so it would be "P4-2021-02" and so on. How can I have the code look at the last two numbers of the previous cell and add one to the last two digits using vba. Like this, but this is wrong.
VBA Code:
Private Sub UserForm_Initialize()
   
    Dim Numbers As String
   
    Numbers = "01"
    MsgBox CLng(Format(Numbers, "##"))
     
    Me.Caption = ("P4" & "-" & Format(Now(), "yyyy")) & "-" & Format(Numbers, "##")
End Sub
How do I force a leading zero until the number reaches 10?

Thank you
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,829
Office Version
  1. 2010
Platform
  1. Windows
I can't tell what you're trying to do, but maybe this will help:

VBA Code:
Sub Init()
  Dim i As Long
  
  i = 100 * Year(Now())
  Debug.Print NextCaption(i)
  Debug.Print NextCaption(i)
  Debug.Print NextCaption(i)
End Sub

Function NextCaption(ByRef i As Long) As String
  i = i + 1
  NextCaption = Format(i, "P4-0000-00")
End Function
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,610
Messages
5,765,412
Members
425,285
Latest member
andypandypoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top