ExcelBanter - Want a leading zero on a number

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Want a leading zero on a number (https://www.excelbanter.com/excel-programming/426363-want-leading-zero-number.html)

Want a leading zero on a number

I have the following program that takes a date input from a combo box in a
userform and processes it.

Public Sub TribeNameServDate()
Unload frmFacil
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If
'#### How to force this to show a leadning zero for 1 digit nos.? ###
sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
Application.ScreenUpdating = True
ws.Range("A1").Select

End Sub

Later, the variable iPayrollMonth will be used to name the file (see
below), and I want it always as a 2 digit number, like 04 instead of 4. How
do I do that?

Public Sub FileNameandSave()
Dim sYear As String
Dim sFilename As String

ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _
& " " & sTribeNameUI & " TR"
End Sub

Thanks!

Want a leading zero on a number

Hi Salgud,

I'm assuming D3 is where you are putting your date from the userform, if so
then you can change the way the date is displayed by added this line:

ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"

After:

ws.Range("D3").Value = sServMonthUI

So that it looks like:

ws.Range("D3").Value = sServMonthUI
ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

Hope that helps

"salgud" wrote:

I have the following program that takes a date input from a combo box in a
userform and processes it.

Public Sub TribeNameServDate()
Unload frmFacil
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If
'#### How to force this to show a leadning zero for 1 digit nos.? ###
sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
Application.ScreenUpdating = True
ws.Range("A1").Select

End Sub

Later, the variable iPayrollMonth will be used to name the file (see
below), and I want it always as a 2 digit number, like 04 instead of 4. How
do I do that?

Public Sub FileNameandSave()
Dim sYear As String
Dim sFilename As String

ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _
& " " & sTribeNameUI & " TR"
End Sub

Thanks!

Want a leading zero on a number

On Wed, 1 Apr 2009 14:56:11 -0700, Derek Bliss wrote:

Hi Salgud,

I'm assuming D3 is where you are putting your date from the userform, if so
then you can change the way the date is displayed by added this line:

ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"

After:

ws.Range("D3").Value = sServMonthUI

So that it looks like:

ws.Range("D3").Value = sServMonthUI
ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

Hope that helps

"salgud" wrote:

I have the following program that takes a date input from a combo box in a
userform and processes it.

Public Sub TribeNameServDate()
Unload frmFacil
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If
'#### How to force this to show a leadning zero for 1 digit nos.? ###
sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
Application.ScreenUpdating = True
ws.Range("A1").Select

End Sub

Later, the variable iPayrollMonth will be used to name the file (see
below), and I want it always as a 2 digit number, like 04 instead of 4. How
do I do that?

Public Sub FileNameandSave()
Dim sYear As String
Dim sFilename As String

ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _
& " " & sTribeNameUI & " TR"
End Sub

Thanks!

Thaks for the reply. I'm getting a type mismatch error on the line marked
below:

ws.Range("D3").Value = sServMonthUI
ws.Range("D3").Select
ActiveCell.Formula = "=text(A1,""mm-dd-yyy"")"
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))<---TYPE MISMATCH
iPayrollMonth = iServMonth + 1

Did I miss something?

Want a leading zero on a number

D3 is already in the format ("mm-dd-yy") and that is the cause of error.

Either you can change it to
iServMonth = month(ws.Range("d3"))

OR

(as you have mentioned in your code)
iServMonth = Month(month(ws.Range("d3") & " 1,2009"))

The first one should be enough, right???

If this post helps click Yes
---------------
Jacob Skaria

"salgud" wrote:

On Wed, 1 Apr 2009 14:56:11 -0700, Derek Bliss wrote:

Hi Salgud,

I'm assuming D3 is where you are putting your date from the userform, if so
then you can change the way the date is displayed by added this line:

ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"

After:

ws.Range("D3").Value = sServMonthUI

So that it looks like:

ws.Range("D3").Value = sServMonthUI
ws.Range("D3").select
ActiveCell.Formula = "=TEXT(A1,""mm-dd-yyyy"")"
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

Hope that helps

"salgud" wrote:

I have the following program that takes a date input from a combo box in a
userform and processes it.

Public Sub TribeNameServDate()
Unload frmFacil
frmTribeNameSMCY.Show
Application.ScreenUpdating = False

Unload frmTribeNameSMCY

ws.Range("A1").Value = sTribeNameUI & " Turnaround Report"

ws.Range("D3").Value = sServMonthUI
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))
iPayrollMonth = iServMonth + 1

If iPayrollMonth 12 Then
iPayrollMonth = iPayrollMonth - 12
End If
'#### How to force this to show a leadning zero for 1 digit nos.? ###
sPayrollMonth = Format(28 * iPayrollMonth, "MMM") 'converts integer month
to text month
ws.Range("C3").Value = sPayrollMonth

If iPayrollMonth < 6 Then
sSFY = Right(sCYUI, 2)
Else
sSFY = Right(sCYUI + 1, 2)
End If

ws.Range("J3:K3").Select
Selection.NumberFormat = "@"
ws.Range("J3") = Right(sCYUI, 2)
ws.Range("K3") = sSFY
Application.ScreenUpdating = True
ws.Range("A1").Select

End Sub

Later, the variable iPayrollMonth will be used to name the file (see
below), and I want it always as a 2 digit number, like 04 instead of 4. How
do I do that?

Public Sub FileNameandSave()
Dim sYear As String
Dim sFilename As String

ActiveWorkbook.SaveAs Filename:="SFY" & sSFY & "." & iPayrollMonth _
& " " & sTribeNameUI & " TR"
End Sub

Thanks!

Thaks for the reply. I'm getting a type mismatch error on the line marked
below:

ws.Range("D3").Value = sServMonthUI
ws.Range("D3").Select
ActiveCell.Formula = "=text(A1,""mm-dd-yyy"")"
iServMonth = Month(DateValue(ws.Range("d3") & " 1,2009"))<---TYPE MISMATCH
iPayrollMonth = iServMonth + 1

Did I miss something?

Want a leading zero on a number

On Wed, 1 Apr 2009 21:14:01 -0700, Jacob Skaria wrote:

D3 is already in the format ("mm-dd-yy") and that is the cause of error.

Either you can change it to
iServMonth = month(ws.Range("d3"))
Not sure what I would change to this.

OR

(as you have mentioned in your code)
iServMonth = Month(month(ws.Range("d3") & " 1,2009"))
Already have this in there, but it's not working.

The first one should be enough, right???
The first what should be enough?

Sorry, am not clear what you're suggesting.

Want a leading zero on a number

On Thu, 2 Apr 2009 09:30:47 -0600, salgud wrote:

On Wed, 1 Apr 2009 21:14:01 -0700, Jacob Skaria wrote:

D3 is already in the format ("mm-dd-yy") and that is the cause of error.

Either you can change it to
iServMonth = month(ws.Range("d3"))
Not sure what I would change to this.

OR

(as you have mentioned in your code)
iServMonth = Month(month(ws.Range("d3") & " 1,2009"))
Already have this in there, but it's not working.

The first one should be enough, right???
The first what should be enough?

Sorry, am not clear what you're suggesting.

I tried changing
iServMonth = Month(month(ws.Range("d3") & " 1,2009"))
to
iServMonth = month(ws.Range("d3"))

Still getting a type mismatch error