Microsoft Excel Help?

Question

I want to display a message box using VBA when the number of rows in an Microsoft EXCEL worksheet exceeds 120 rows.

Can this be done?

Please provide me with the sample coding if possible.

Thank you

Answer 1

I assume you mean when the number of rows (containing data) get 121. Of course this can be done - you're gonna have to write a macro for it... but then, you also need to define an event to run the macro - examples of event are
** Click of a button
** Opening of the excel workbook
etc.

You have to decide two things -
1. What defines a 'filled row' (ie, when would you consider the row as containing data)
2. When and how would you like the check to be performed (ie the event).

Once you provide the above two details - I could help you write the code.

.

Answer 2

This is the macro that you need.
If your list is in column A, then...
Then in B1 paste this function
=COUNTA( A:A)

Just open the VBE for that file,
In that exact sheet paste this in the code area for it

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value > 120 Then
MsgBox "You exceeded the up limits (120)", vbCritical
End If
End Sub

Enjoy my profile, I am the VBAXLMan

Answer 3

Try here:
http://vbaexpress.com/kb/default.php

You don't have to join. Just hit "List Entries".