Help with leading zeros for User input box

xlhelpme

Board Regular
Joined
Aug 17, 2010
Messages
53
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-com:office:office" /><o:p></o:p>
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:p></o:p>
<o:p></o:p>
I have attached a section of the code below, I am using excel 2000 with XP. <o:p></o:p>
<o:p></o:p>

Thanks very much in advance.
<o:p></o:p>
<o:p></o:p>
<!-- / 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" o:preferrelative="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><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><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>:confused:<o:p></o:p>

<o:p></o:p>
Rich (BB code):
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>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.

Rich (BB code):
    End If
    While Len(varUserInput) < 6
     varUserInput = "0" & varUserInput
     Wend
    Update_No = "ISS-3D8-2011-" & varUserInput
 
Upvote 0
This is another method...

Update_No = "ISS-3D8-2011-" & Format(varUserInput, "000000")
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
The input should probably be checked if it's numeric if that is the type of desired input.

Code:
 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
 
Upvote 0
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 -->
 
Upvote 0

Forum statistics

Threads
1,224,223
Messages
6,177,251
Members
452,765
Latest member
Erka Gizli

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
Back
Top