+ Reply to Thread
Results 1 to 2 of 2

Sequential Numbering on UserForm

  1. #1
    Registered User
    Join Date
    01-13-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    9

    Sequential Numbering on UserForm

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-13-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    9

    Re: Sequential Numbering on UserForm

    Hello

    Just to say this is now fixed. A colleague pointed out that the

    .Value = Format(Date, "yy/mm/") & Format(Right(refno, 1) + 1, "0000")

    refno, 1 needed to be refno, 4!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sequential Numbering
    By chipd512 in forum Excel General
    Replies: 1
    Last Post: 11-25-2015, 03:39 PM
  2. [SOLVED] Vba code for Userform Automatic and Sequential Numbering
    By newqueen in forum Excel General
    Replies: 23
    Last Post: 10-05-2014, 04:18 PM
  3. Sequential Numbering
    By izzorac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2014, 10:15 PM
  4. sequential numbering
    By mulanfox in forum Excel General
    Replies: 0
    Last Post: 07-26-2011, 11:18 PM
  5. Sequential numbering
    By taylorackley in forum Excel General
    Replies: 2
    Last Post: 08-26-2010, 02:19 PM
  6. sequential numbering
    By super in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2005, 10:39 AM
  7. Sequential Numbering.
    By Rodney in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-02-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1