increase numbered cell by

[Guest]

How do I get a control box to increase in increments of one when it is
pushed. I have created an invoice, but I need control over the numbering. (
I don't want it to assign a number every time I print or save it.

 

[Ian]

Create a textbox with your starting invoice number in it. Then use this
code. It increments the texbox value by 1 each time it is double clicked,
but has the advantage that you can select the textbox and edit it manually
if you double click by mistake. Enter the cell reference you want your
invoice number is as the Linked Cell in the textbox properties.

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Value = TextBox1.Value + 1
End Sub

 

[Guest]

I added the text box, then right clicked the tab at the bottom (to get vb),
then pasted the code. I get the error "REFERENCE IS NOT VALID"

Please help

 

[Ian]

I just tried the same here and it worked fine.

If you right click on the textbox and select view code, the VB window shows
TextBoxn (where n is the number of the textbox) in one drop down box and
Change in the other. Open the Change drop down menu and select DblClick.
Where the cursor is flashing copy the middle line of my code, changing the
textbox number to suit your box.

I don't see why this shouldn't work.

 

[Guest]

I don't know what the heck I'm doing wrong.

Here is the code as it looks in VB. (Starting number being 100)

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox100.Value = TextBox1.Value + 1
End Sub

Whenever I double click it, it goes back to the VB page.

Thanks

 

[Guest]

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox100.Value = TextBox100.Value + 1
End Sub

The last one I had not changed the second "1" This is the code I have copied
from the VB page. It still won't change numbers when double clicked.

Thank you

 

[Ian]

You have to enter your starting value in the textbox manually, then th
second line of your code should read textbox1.value in each instance. So the
code is waiting for a doubleclick in textbox1, then making the new value of
textbox equal to the current value +1.

So you need to change textbox100 to textbox1 in your code.

 

[Guest]

I have done what you said and I get a similar problem to marliss. When I
double click on the text box, it automatically open the vb box. I copied and
pasted you exact routine into the vb box and made sure I manuall input a vale
of 1000 on the left hand side of that as the starting value. I even change
the color of the font. I am using Excel 2003. Is the some other value to
change. The values in the drop down boxes are textbox1 and dblclk.

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Value = TextBox1.Value + 1
End Sub