Announcement

Collapse
No announcement yet.

Automatically Generate Numbers In Sequence

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatically Generate Numbers In Sequence

    Hi there,

    I've created a sheet using Excel & VBA to record jobs my department is required to perform and need a little help with the job numbers themselves.

    I've created a macro to get Excel to move to the next empty line which works fine and I've added some code to the sheet to get Excel to add a job number when you click in a cell which increases by 1 for each row, however I think there must be a better way

    I've added a combo box to my form for users to select a job number to view details of, but due to the limitation of the combo box, it shows column A only - and the job numbers are in column L.

    I can't move the job numbers into column A as when Excel is trying to find an empty line, it tabs into the cell and then generates a new number so the cell is no longer blank, so then moves to the next line. Nor can I get the combobox to source from column L as I would have to have all the columns in between.

    Is there a better way to get Excel to autogenerate numbers every time I create a new job (akin to the Access autonumber feature)? I know Excel isn't the best app to use, but it's my only option.

    Many thanks in advance,

    TBO

  • #2
    Re: Autogenerated Job Numbers In Combobox

    Hi Bearders

    Let's see your existing code.

    BTW - I don't understand what you mean by

    I can't move the job numbers into column A as when Excel is trying to find an empty line, it tabs into the cell and then generates a new number so the cell is no longer blank, so then moves to the next line. Nor can I get the combobox to source from column L as I would have to have all the columns in between.
    I would have thought that we could generate you some code so that you can effectively use Column A for ID field.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Autogenerated Job Numbers In Combobox

      Cheers for getting back to me Wil.

      Code for finding an empty line is as follows:
      Code:
      'find  first empty row in database
      irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
      Not pretty, but it works.

      Code for autogenerating numbers:
      Code:
      Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
          Dim RowOffset As Long
          Dim IndexCol As String
           'Set values
          RowOffset = 0
           'Change the C to the column where you want the numbers to show
          IndexCol = "B"
           
          Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + RowOffset
      End Sub
      I borrowed this from another query on the forum and it seems to work fine.

      Again borrowed from another query, this time to get a combobox to pull data onto the form:
      Code:
      Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
      Dim str1 As String, str2 As String
      ComboBox1.BoundColumn = 1
      TextBox1.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 2
      Priority_ComboBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 3
      SST_ComboBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 5
      User_TextBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 6
      Equipment_ComboBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 7
      RefNos_TextBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 8
      Fault_TextBox.Value = ComboBox1.Value
      ComboBox1.BoundColumn = 9
      txtpdp.Value = ComboBox1.Value
      
      End Sub
      However, the combobox obviously counts from column A and displays all data as far as I permit it to. I can't get it to start at column L and Excel help leads me to believe that this is the way it must be.

      Ideally, I would have the job number field in column A, but when I do this and create a new record, the first piece of code causes the active row to be somewhere around column 40, rather than 6 (which happens to be the next empty row).

      Hope this makes some sense!

      Comment

      Working...
      X