Help with leading zeros for User input box

[xlhelpme]

I have a form that populates a sheet (Registry) and assigns a reference number to column A, I then select the second form (Update) to update the row defined by the reference number in Column A this then writes the data from form (Update) to the registry, Column A is made up of text ISS-3D8-2011_ and the input number from the Input box(varUserInput) this is then stored as Update_No.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
My problem is that if you don't have the leading 0’s in the input box (varUserInput) then the cell to update in the registry document cannot be located. Is there any way to define leading 0’s to give a six digit number i.e. 000123 rather than 123 that is entered.<o></o>
<o></o>
I have attached a section of the code below, I am using excel 2000 with XP. <o></o>
<o></o>

Thanks very much in advance.
<o></o>
<o></o>
<!-- / message -->Excel novice <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 12pt; HEIGHT: 15.75pt" id=_x0000_i1025 alt="" type="#_x0000_t75"><v:imagedata o:href="http://www.mrexcel.com/forum/images/smilies/confused.gif" src="file:///C:/DOCUME~1/ADMINI~1/LOCALS~1/Temp/msoclip1/01/clip_image001.gif"></v:imagedata></v:shape><o></o>

<o></o>

Set rng = Selection<o:p></o:p>
'<o:p></o:p>
'   Get Data from Registry sheet and populate Update Form<o:p></o:p>
'<o:p></o:p>
' Select the Issue Number to update<o:p></o:p>
On Error GoTo Canceled<o:p></o:p>
Start:<o:p></o:p>
 varUserInput = Application.InputBox _<o:p></o:p>
     (Prompt:="Enter Issue Number to Update:" & vbCrLf _<o:p></o:p>
     & "i.e.: Number only " & vbCrLf _<o:p></o:p>
     & "000001,000002, 000055 etc" & vbCrLf _<o:p></o:p>
     & "" & vbCrLf _<o:p></o:p>
     & "", _<o:p></o:p>
     Title:="Locate Issue to Update", _<o:p></o:p>
     Default:="", _<o:p></o:p>
     Type:=2) 'Type 2 = String<o:p></o:p>
 If varUserInput = "False" Then Exit Sub<o:p></o:p>
 If varUserInput = "" Then<o:p></o:p>
     MsgBox "Enter a Valid Issue Number or press Cancel"<o:p></o:p>
     GoTo Start:<o:p></o:p>
 End If<o:p></o:p>
  Update_No = "ISS-3D8-2011-" & varUserInput<o:p></o:p>
     GoTo Find:<o:p></o:p>
  Exit Sub<o:p></o:p>
Canceled:<o:p></o:p>
 MsgBox "No Issue with the number " & vbCrLf _<o:p></o:p>
 & "        " & varUserInput & " Exists"<o:p></o:p>
 GoTo Start:<o:p></o:p>
' Locate 1st cell in Registry sheet<o:p></o:p>
Find:<o:p></o:p>
Dim Firstaddress<o:p></o:p>
Sheets("Register").Visible = True<o:p></o:p>
 Sheets("Register").Select<o:p></o:p>
 With Worksheets("Register").Range("A3:A500")<o:p></o:p>
 Set c = .Find(Update_No, LookIn:=xlValues)<o:p></o:p>
 If Not c Is Nothing Then<o:p></o:p>
     Firstaddress = c.Address<o:p></o:p>
     Do<o:p></o:p>
         c.Value = Update_No<o:p></o:p>
         Set c = .FindNext(c)<o:p></o:p>
     Loop While Not c Is Nothing And c.Address <> Firstaddress<o:p></o:p>
 End If<o:p></o:p>
 Range(Firstaddress).Select<o:p></o:p>
 End With<o:p></o:p>
' copy from register<o:p></o:p>
 Application.CutCopyMode = False<o:p></o:p>
 Update.TextBox3.Value = ActiveCell.Value<o:p></o:p>
 ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate<o:p></o:p>
 Update.TextBox4.Value = ActiveCell.Value<o:p></o:p>
<o:p></o:p>

 

[Jerry Sullivan]

Hello,

If you insert the 2 lines below shown in blue font between the existing two lines shown,
the code should add the leading zeros that you want.

    End If
    While Len(varUserInput) < 6
     varUserInput = "0" & varUserInput
     Wend
    Update_No = "ISS-3D8-2011-" & varUserInput

 

[AlphaFrog]

This is another method...

Update_No = "ISS-3D8-2011-" & Format(varUserInput, "000000")

 

[Jerry Sullivan]

This is another method...

varUserInput = Format(varUserInput, "000000")

Thanks AlphaFrog. Yours is probably better in its handling of non-numeric inputs (if that is something the OP would have).

If varUserInput = "A123", then
my statement results in "00A123"
your statement results in a value ending in "A123"

Most likely "A123" would be the desired result.

 

[AlphaFrog]

The input should probably be checked if it's numeric if that is the type of desired input.

 If varUserInput = "False" Then Exit Sub
[COLOR="Red"] If Not IsNumeric(varUserInput) Then[/COLOR]
     MsgBox "Enter a Valid Issue Number or press Cancel"
     GoTo Start:
 End If
 Update_No = "ISS-3D8-2011-" & Format(varUserInput, "000000")

Or change the InputBox to Type:=1 'Number

 

[xlhelpme]

Have used
Update_No = "ISS-3D8-2011-" & Format(varUserInput, "000000")
Worked perfectly will now have a look at the If Not IsNumeric(varUserInput) Then or change the InputBox to Type:=1 'Number options as I am still working through the error checking in the complete code but again great support from this forum Thanks <!-- / message --><!-- sig -->