Sequential Number
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sequential Number

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have used this code to get the next available number.

    Private sub userform_initialize()
    Dim maxval
    maxval=WorksheetFunction.Max(Sheets("Sheet1").Range("A:A")) + 1
    Userform1.textbox1.text=maxval


    I also have a command button on my form which lets me add a record or exit the userform. My problem is that when I add another record my textbox is not recording the next available number. I am guessing that it has something to do with the fact that the form is not being initialized. Can anyone offer any assistance.

    Carol

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 11:56, slimpickens wrote:
    I have used this code to get the next available number.

    Private sub userform_initialize()
    Dim maxval
    maxval=WorksheetFunction.Max(Sheets("Sheet1").Range("A:A")) + 1
    Userform1.textbox1.text=maxval


    I also have a command button on my form which lets me add a record or exit the userform. My problem is that when I add another record my textbox is not recording the next available number. I am guessing that it has something to do with the fact that the form is not being initialized. Can anyone offer any assistance.

    Carol
    Hi Carol,

    1. What version of Excel are you using?

    2. In your add record event, could you unload the form and then load it again. With screen updating turned off? It might be instantaneous so no one would notice when you re-initialize it.

    3. When you click the add record, increase the text box value by 1.

    Textbox1 = Textbox1 + 1

    might work. Make sure you reference it properly.

    HTH,
    Jay

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Take a look at this code.

    MsgBox "One record written to Claim Payments"

    response = MsgBox("Do you want to add another record?", _
    vbYesNo)

    If response = vbYes Then


    Polnumber.Text = ""
    Claimno.Text = ""
    Daypaid.Text = ""
    Monthpaid.Text = ""
    Yearpaid.Text = ""
    Refnumber.Text = ""
    Paytype.Text = ""
    Payment1.Text = ""
    Payment2.Text = ""
    Paycode.Text = ""
    Paymenttype.Text = ""
    Reserve1.Text = ""
    Reserve2.Text = ""
    Indicator.Text = ""
    TextBox1.Text = Commod1.Value & Commod2.Value
    TextBox2.Text = Refnumber.Value


    Polnumber.SetFocus
    Else
    For Each w In Application.Workbooks
    w.Save
    Next w
    Application.Quit

    End If

    End Sub

    Where should I put the Unload and Load statements.

    Carol

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-12 12:23, slimpickens wrote:
    Take a look at this code.

    MsgBox "One record written to Claim Payments"

    response = MsgBox("Do you want to add another record?", _
    vbYesNo)

    If response = vbYes Then


    Polnumber.Text = ""
    Claimno.Text = ""
    Daypaid.Text = ""
    Monthpaid.Text = ""
    Yearpaid.Text = ""
    Refnumber.Text = ""
    Paytype.Text = ""
    Payment1.Text = ""
    Payment2.Text = ""
    Paycode.Text = ""
    Paymenttype.Text = ""
    Reserve1.Text = ""
    Reserve2.Text = ""
    Indicator.Text = ""
    TextBox1.Text = Commod1.Value & Commod2.Value
    TextBox2.Text = Refnumber.Value


    Polnumber.SetFocus
    Else
    For Each w In Application.Workbooks
    w.Save
    Next w
    Application.Quit

    End If

    End Sub

    Where should I put the Unload and Load statements.

    Carol
    Hi Carol,

    After the End If, type in
    --------------
    Me.Hide
    Call Module1.call_again
    --------------
    Then, in a code module (Module1 in my example above)

    ---------------
    Sub call_again()
    Unload UserForm1
    UserForm1.Show
    End Sub
    ---------------

    This worked for me.

    HTH,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com