Hello World

User enters data into a form control for the purpose of generating a
manifest. I want to stop the user from entering more than 30 products
within a single userform. The controls are linked to a worksheet called
"Temp". Also on the same userform I want to be able to tell the user if any
information is missing for instance this code executes the msgbox but then
procedes with the rest of the code?

Sub Name()
Sheets("TEMP").Select
If Range("E40") = "-" Then 'Relates to a cell where information is
copied from into. "-" = product code seperator.
MsgBox "You must enter a Product Code!", vbInformation
End If
User_InputP.Show 'Userform for entering products.
'rest of code to copy and paste information into a table

TIA
Mark

A couple of methods (of numerous possibilities)

1. WHEN USER TRIES TO FINISH FORM

Code
-------------------

Sub Button1_Click()
'- check 1
If Range("A1").Value = "" Then
MsgBox ("Must enter value")
Range("A1").Select
Exit Sub
End If
'- check 2
If Range("A2").Value 100 Then
MsgBox ("Value too high")
Range("A2").Select
Exit Sub
End If
'- check 3
counter = Application.WorksheetFunction.CountA(Range("A1:A30 "))
If counter = 30 Then
MsgBox ("Cannot have more than 30 products. Please delete " & counter - 30)
Range("A31").Select
Exit Sub
End If
'- checks complete. save file
ActiveWorkbook.Save
MsgBox ("Form saved.")
End Sub

-------------------


2. IN PROCESS OF COMPLETING FORM
Using worksheet Change event

Code
-------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountA(Range("A1:A30 ")) = 30 Then
MsgBox ("You have reached the limit of 30 entries")
End If
End Sub

-------------------


--
Message posted from http://www.ExcelForum.com

Thank you Brian

"BrianB " wrote in message
...
A couple of methods (of numerous possibilities)

1. WHEN USER TRIES TO FINISH FORM

Code:
--------------------

Sub Button1_Click()
'- check 1
If Range("A1").Value = "" Then
MsgBox ("Must enter value")
Range("A1").Select
Exit Sub
End If
'- check 2
If Range("A2").Value 100 Then
MsgBox ("Value too high")
Range("A2").Select
Exit Sub
End If
'- check 3
counter = Application.WorksheetFunction.CountA(Range("A1:A30 "))
If counter = 30 Then
MsgBox ("Cannot have more than 30 products. Please delete " & counter -
30)
Range("A31").Select
Exit Sub
End If
'- checks complete. save file
ActiveWorkbook.Save
MsgBox ("Form saved.")
End Sub

--------------------


2. IN PROCESS OF COMPLETING FORM
Using worksheet Change event

Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountA(Range("A1:A30 ")) = 30 Then
MsgBox ("You have reached the limit of 30 entries")
End If
End Sub

--------------------



---
Message posted from http://www.ExcelForum.com/