Limit number of items to add to an invoice? 2

[Pudsters]

I have a button that adds items to an invoice (code below). But I only want to be able to add 10 items or 10 rows. So if the user attempts to add an 11th item, a message will popup saying, "Sorry, Invoice is Full. Must start another invoice to add more items."

Anybody know the proper code?

Private Sub cmdAdd_Click()
ActiveWorkbook.Sheets("Invoice").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDescriptions.Value
ActiveCell.Offset(0, 2) = txtSilverstar.Value
ActiveCell.Offset(0, 3) = txtRegular.Value
ActiveCell.Offset(0, 4) = txtAfterhours.Value

Range("A1").Select
End Sub

 

[ettienne]

Use a variable to count each line added and when you hit 11 display a message box and do not add the line, or better still disable the Add button when you hit 10.

 

[Pudsters]

Well the problem is I don't know how to do that yet. I'm a beginner at VBA. Also, I still want a message box to popup, so the user knows WHY he can't add more items.

Any help would be greatly appreciated.

 

[ettienne]

Declare a variable in the Declarations section:

Dim intCounter as Integer

Then change your command button:

Private Sub cmdAdd_Click()
'Increment counter
intCounter = intCounter + 1
if intCounter = 11 then
msgbox "Your message goes here"
'Reset counter
intCounter = 0
else
ActiveWorkbook.Sheets("Invoice").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDescriptions.Value
ActiveCell.Offset(0, 2) = txtSilverstar.Value
ActiveCell.Offset(0, 3) = txtRegular.Value
ActiveCell.Offset(0, 4) = txtAfterhours.Value

Range("A1").Select
end if
End Sub

 

[xlbo]

...so what have you tried - what hasn't worked ?

In this instance it looks like you are outputting to an excel sheet, starting at row 1. That beiong the case, you can test the row property of the activecell. If it is > 10 then show a msgbox and exit the code

have a look in the help files for the bolded words as a starter for 10

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Pudsters]

Declare a variable in the Declarations section:"

Dim intCounter as Integer

I can't seem to find a Declarations section, how do I add this?

 

[xlbo]

the Declarations section is just anywher ein your sub for a standard variable - tends to be just after the Sub name:

Sub Generic_Sub()
Dim myVariable as string

'rest of code goes here
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Pudsters]

I copied and pasted the codes above but it had no effect on my invoice. I was still able to keep adding items.

Declare a variable in the Declarations section:

Dim intCounter as Integer

Then change your command button:

Private Sub cmdAdd_Click()
'Increment counter
intCounter = intCounter + 1
if intCounter = 11 then
msgbox "Your message goes here"
'Reset counter
intCounter = 0
else
ActiveWorkbook.Sheets("Invoice").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDescriptions.Value
ActiveCell.Offset(0, 2) = txtSilverstar.Value
ActiveCell.Offset(0, 3) = txtRegular.Value
ActiveCell.Offset(0, 4) = txtAfterhours.Value

Range("A1").Select
end if
End Sub

 

[Chance1234]

after 'reset counter

put exit sub


Chance,

Filmmaker, taken gentleman and He tan e epi tas all change on monday

 

[Pudsters]

Still not working, I changed the counter to 3 but I can still add as many items as I want, with no message box.

Private Sub cmdAdd_Click()
'Increment counter

Dim intCounter As Integer

intCounter = intCounter + 1
If intCounter = 3 Then
MsgBox "Your message goes here"
'Reset counter

Exit Sub

intCounter = 0
Else
ActiveWorkbook.Sheets("Invoice").Activate
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboDescriptions.Value
ActiveCell.Offset(0, 2) = txtSilverstar.Value
ActiveCell.Offset(0, 3) = txtRegular.Value
ActiveCell.Offset(0, 4) = txtAfterhours.Value

Range("A1").Select
End If
End Sub

 

[rmikesmith]

Minor correction: Exit Sub is not really needed here as all of the alternate code is within the Else portion of the IF statement. I doesn't hurt anything, except you need to reset intCounter before the Exit Sub command.

To see what is happening, add a breakpoint at the beginning of cmdAdd_Click and inspect the value of intCounter. You could add intCounter as a Watch instead (Debug|Add Watch... from the VBE menu). Either way, look at the value of intCounter as the commandbutton is clicked.


Regards,
Mike

 

[ettienne]

intCounter needs to be a global variable, not a variable declared in cmdAdd_Click().
Each time you click Add and cmdAdd_Click() is run a new intCounter is declared and set to zero, so it never reaches anything higher than 1.
You need to declare intCounter in the General Declarations section. In your VBA IDE (or VBE) screen press Control-Home and you should be taken to the General Declarations section.

 

[rmikesmith]

I recommend you copy ettienne's original posted code, since a number of changes have been made, then debug, if necessary, as I suggested in my last post. I see no reason ettienne's code shouldn't work.


Regards,
Mike

 

[Pudsters]

OKAY!!! That got the message to show but if you press OKAY, You can still add more items. It needs to somehow disable the cmdAdd button.

 

[rmikesmith]

Problem is, the code re-sets intCounter after one "too many invoice items" condition/message. Referring back to your original post, how does the user initiate a new invoice?


Mike

 

[Pudsters]

On the invoice page, I have a button "Create New Invoice" with a macro I recorded that opens another worksheet and it copies the customer information over to it.

 

[rmikesmith]

OK, I think we need to take a step back. Xlbo's suggestion is probably best, otherwise, intCounter would need to be made a global variable and reset to zero elsewhere. Here is a modified version of your button click event procedure:

Private Sub cmdAdd_Click()
Dim TotalLines As Long

    ActiveWorkbook.Sheets("Invoice").Activate

   TotalLines = Application.WorksheetFunction.CountA(Range("A1:A10"))
   If TotalLines = 10 Then
     MsgBox "Sorry, Invoice is Full. Must start another invoice to add more items."
   Else
     Range("A1").Select
     Do
       If IsEmpty(ActiveCell) = False Then
         ActiveCell.Offset(1, 0).Select
       End If
     Loop Until IsEmpty(ActiveCell) = True
     ActiveCell.Value = cboDescriptions.Value
     ActiveCell.Offset(0, 2) = txtSilverstar.Value
     ActiveCell.Offset(0, 3) = txtRegular.Value
     ActiveCell.Offset(0, 4) = txtAfterhours.Value
     Range("A1").Select
   End If
End Sub

Note, this assumes that your list of invoiced items will be in rows 1-10. If that's not the case, change the range address appropriately.


Regards,
Mike

 

[Pudsters]

Perfect, That did the job. Thanks Mike! And the code seems more compact as well.

I'm curious, what does this means? "Dim TotalLines As Long"

 

[rmikesmith]

Dim TotalLines As Long

This tells the compiler to declare the variable TotalLines as a Long data type, which is a 32-bit Integer that can store values of -2,147,483,648 to 2,147,483,647. In this particular instance, I could have declared TotalLines as an Integer, a 16-bit data type that can store numbers from -32,768 to 32,767, since the maximum number of invoice items/lines is 10. However, I'm in the habit of declaring longs for variables that reference rows on a worksheet since the maximum number of rows possible, 65,536, exceeds the upper limit for an Integer.


Hope that helps.
Mike

 

[Pudsters]

Mike, thanks again, that was a great help.