Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Auto Assign Numbers in Sequence

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

  • Auto Assign Numbers in Sequence

    Hello,

    I am currently using a command button that uses the now() function formatted as a number to generate a unique po# in a protected cell. Is there a way to create a po log and assign a macro to it that will assign the first number in the list and continue through the list each time the command button is used?

    The log would look something like this:
    606500
    606501
    606502
    606503
    606504
    606505
    606506
    606507

    Thank you!!

  • #2
    Re: Auto Assign Numbers in Sequence

    Can you post your current code so we can get an idea of something to build on?

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Comment


    • #3
      Re: Auto Assign Numbers in Sequence

      Here it is. Thank you!

      Private Sub Workbook_Open()
      Dim FName As String
      Dim FNo As String
      Dim x As Long
      FName = ThisWorkbook.Path & Application.PathSeparator & "Number.Txt"
      FNo = FreeFile
      x = 1
      On Error Resume Next
      Open FName For Input As #FNo
      Input #FNo, x
      x = x + 1
      ' *** Change range reference to suit ***
      Range("C6:F7").Value = x
      Close #FNo
      FNo = FreeFile
      Open FName For Output As #FNo
      Write #1, x
      Close #FNo
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      ActiveSheet.Paste
      Application.CutCopyMode = False
      With Selection
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      End With
      Selection.Merge
      End Sub

      Comment


      • #4
        Re: Auto Assign Numbers in Sequence

        Here's another one I have.......... what I'd like is to be able to track the numbers in a log.

        Sub PO()
        '
        ' PO Macro
        ' Macro recorded 8/19/2005
        '

        ActiveSheet.Unprotect Password:="test"
        Range("O6:R7").Select
        ActiveCell.FormulaR1C1 = "=NOW()"
        Range("O6:R7").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
        End With
        Selection.UnMerge
        With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
        Selection.Merge
        ActiveSheet.Protect Password:="test"
        End Sub

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X