Hello, new to the forum. Hoping someone can help me. I have two problems I can't seem to fix, but one at a time!
Firstly, I have a sequential numbering that appears under the Job Reference when the Opportunity UserForm is initiated. This is in the format yy/mm/0000.
I have got this to work from 0001-0010 however after this point it reverts back to 0001 instead of 0011 and I cannot work out why.
I am using the following code (spreadsheet also attached):
Private Sub UserForm_Initialize()
On Error Resume Next
Dim counting As Integer
counting = 1
Dim refno As String
refno = Sheets("Opportunities").Range("A65536").End(xlUp).Text
With txtRef2
If refno = "jobRef" Then
.Value = Format(Date, "yy/mm/") & Format(0, "0000")
Else
.Value = Format(Date, "yy/mm/") & Format(Right(refno, 1) + 1, "0000")
End If
.Enabled = False
End With
End Sub
And on submission:
Private Sub cmdAdd_Click()
'Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Opportunities")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 2).Value = Me.txtContract.Value
.Cells(lRow, 11).Value = Me.MonthViewComplete.Value
.Cells(lRow, 10).Value = Me.txtValue.Value
.Cells(lRow, 3).Value = Me.CboCategory2.Value
.Cells(lRow, 1).Value = UCase(txtRef2.Text)
.Cells(lRow, 4).Value = Me.CboCategory.Value
.Cells(lRow, 5).Value = Me.txtOwner.Value
.Cells(lRow, 8).Value = Me.CboProcType.Value
.Cells(lRow, 6).Value = Me.txtBus.Value
.Cells(lRow, 7).Value = Me.txtSource.Value
.Cells(lRow, 9).Value = Me.CboPlanned.Value
End With
'Clear input controls.
Me.txtContract.Value = ""
Me.txtValue.Value = ""
Me.CboPlanned.Value = ""
Me.CboProcType.Value = ""
Me.CboCategory.Value = ""
Me.CboCategory2.Value = ""
Me.txtOwner.Value = ""
Me.CboProcType.Value = ""
Me.txtBus.Value = ""
Me.txtSource.Value = ""
txtRef2.Value = Format(Date, "yy/mm/") & Format(Right(txtRef2.Value, 1) + 1, "0000")
Any help would be appreciated
Thanks
Bookmarks