Sequential Number

[slimpickens]

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

 

[Jay Petrulis]

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

 

[slimpickens]

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

 

[Jay Petrulis]

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