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