Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Automatically Generate Numbers In Sequence

  1. #1
    Join Date
    8th December 2007
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,677

    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

    Web Presence:
    Personal: Datasapien
    LinkedIn: Will Riley

  3. #3
    Join Date
    8th December 2007
    Posts
    14

    Re: Autogenerated Job Numbers In Combobox

    Cheers for getting back to me Wil.

    Code for finding an empty line is as follows:
    VB:
     '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:
    VB:
    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:
    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Numbers In Sequence
    By Vlad999 in forum EXCEL HELP
    Replies: 13
    Last Post: December 11th, 2006, 05:24
  2. Automatically generate relationship and keycode
    By codeasp in forum EXCEL HELP
    Replies: 2
    Last Post: May 31st, 2006, 14:00
  3. Auto Assign Numbers in Sequence
    By cswa860 in forum EXCEL HELP
    Replies: 3
    Last Post: September 4th, 2005, 10:53
  4. GETPIVOTDATA Function Doesn't Automatically Generate
    By agmerseth in forum EXCEL HELP
    Replies: 4
    Last Post: July 23rd, 2004, 04:53
  5. Formulas : Sequence of numbers extraction
    By skelum in forum EXCEL HELP
    Replies: 3
    Last Post: February 5th, 2004, 23:38

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
  •  
porno