I would like to provide a message box to inform the user if a specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns true if
rows are there rows in that sheet. That would be useful when performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
ups.com...
I would like to provide a message box to inform the user if a specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns true if
rows are there rows in that sheet. That would be useful when performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke

I'm not interested in showing the dialog when ever the an individual
sheet changes (isn't that what it does)...

I would like a little function/sub that I can call, to check if the
sheet named (just an example) AddedInfo contains any lines/rows after
row 10.

That should be called when a specific button macro is run and at load
time for the spreadsheet.

Does this make sense?

regards,
Ladyhawke


Bob Phillips skrev:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
ups.com...
I would like to provide a message box to inform the user if a specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns true if
rows are there rows in that sheet. That would be useful when performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke

You should try it, then you would know what it does.

What it does, if entered correctly, is check that any time the spreadsheet
is changed as to whether the data goes beyond row 10. Far better than a
button to trigger it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
oups.com...
I'm not interested in showing the dialog when ever the an individual
sheet changes (isn't that what it does)...

I would like a little function/sub that I can call, to check if the
sheet named (just an example) AddedInfo contains any lines/rows after
row 10.

That should be called when a specific button macro is run and at load
time for the spreadsheet.

Does this make sense?

regards,
Ladyhawke


Bob Phillips skrev:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
ups.com...
I would like to provide a message box to inform the user if a specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns true if
rows are there rows in that sheet. That would be useful when
performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke

In this case it is definately not wanted that the status is checked
every time the spreadsheet changes. As I stated before I want to check
it only when loading the spreadsheet and when the user performs a
specific operation.

The reason is that the user should be able to work with the rest of the
spreadsheets without being disturbed by a message all the time (want a
messagebox to show on the indicated occations only if there are lines
in the named spreadsheet).

I might be able to pick out code from the snippet to check when needed,
will look into that a little later...

regards,
Ladyhawke



Bob Phillips skrev:

You should try it, then you would know what it does.

What it does, if entered correctly, is check that any time the spreadsheet
is changed as to whether the data goes beyond row 10. Far better than a
button to trigger it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
oups.com...
I'm not interested in showing the dialog when ever the an individual
sheet changes (isn't that what it does)...

I would like a little function/sub that I can call, to check if the
sheet named (just an example) AddedInfo contains any lines/rows after
row 10.

That should be called when a specific button macro is run and at load
time for the spreadsheet.

Does this make sense?

regards,
Ladyhawke


Bob Phillips skrev:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
ups.com...
I would like to provide a message box to inform the user if a specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns true if
rows are there rows in that sheet. That would be useful when
performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke

But with your method that could open the workbook, no problem, keep putting
data beyond row 10 all day long, and not be told until they open it the next
day.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
oups.com...
In this case it is definately not wanted that the status is checked
every time the spreadsheet changes. As I stated before I want to check
it only when loading the spreadsheet and when the user performs a
specific operation.

The reason is that the user should be able to work with the rest of the
spreadsheets without being disturbed by a message all the time (want a
messagebox to show on the indicated occations only if there are lines
in the named spreadsheet).

I might be able to pick out code from the snippet to check when needed,
will look into that a little later...

regards,
Ladyhawke



Bob Phillips skrev:

You should try it, then you would know what it does.

What it does, if entered correctly, is check that any time the
spreadsheet
is changed as to whether the data goes beyond row 10. Far better than a
button to trigger it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
oups.com...
I'm not interested in showing the dialog when ever the an individual
sheet changes (isn't that what it does)...

I would like a little function/sub that I can call, to check if the
sheet named (just an example) AddedInfo contains any lines/rows after
row 10.

That should be called when a specific button macro is run and at load
time for the spreadsheet.

Does this make sense?

regards,
Ladyhawke


Bob Phillips skrev:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ladyhawke" wrote in message
ups.com...
I would like to provide a message box to inform the user if a
specific
sheet contains rows with content after row 10.

How can this be done and can I make a sub for it so it returns
true if
rows are there rows in that sheet. That would be useful when
performing
the action that might insert info there (to warn the users of new
information available)

regards,
Ladyhawke