Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

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

Detect lines in specific sheet at load time
 

Hi Bob
No they can't: The only occasion that inserts rows in that named sheet
is when the user imports data using a specific macro. This data is
sorted and if data is invalid for the current excel file the concerned
rows will be moved to the name sheet.

Therefore I would check whenever they perform the macro and let the
user know if the latest import had any "invalid" data and then just
remind them if needed (they didn't sort out the data) when opening the
excel file.

regards,
Ladyhawke


Bob Phillips wrote:
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.

Detect lines in specific sheet at load time
 

So just add this code to your import macro

Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If


and also

Private Sub Workbook_Open()
Set rng = ActiveSheet.UsedRange
If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

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

"ladyhawke" wrote in message
oups.com...
Hi Bob
No they can't: The only occasion that inserts rows in that named sheet
is when the user imports data using a specific macro. This data is
sorted and if data is invalid for the current excel file the concerned
rows will be moved to the name sheet.

Therefore I would check whenever they perform the macro and let the
user know if the latest import had any "invalid" data and then just
remind them if needed (they didn't sort out the data) when opening the
excel file.

regards,
Ladyhawke


Bob Phillips wrote:
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.

Detect lines in specific sheet at load time
 

Exactly what's needed - great response and thank you for the patience
with a newbie

regards
Ladyhawke

Detect lines in specific sheet at load time
 

I tried to steer you away, but you stood your ground <bg

--
HTH

Bob Phillips

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

"ladyhawke" wrote in message
ups.com...
Exactly what's needed - great response and thank you for the patience
with a newbie

regards
Ladyhawke

Detect lines in specific sheet at load time
 

I'vw now implemented the ting a a note to observe:

The sheet in question has been frormatted and

If rng(rng.Count).Row 10 Then
MsgBox "last row is " & rng(rng.Count).Row
Endif

Does not work as intended (reports 1000 rows), the issue is solved like
this:

Private Function ComputeNextEmptyRow(ByRef sheet As Object, ByVal
startRow As Integer) As Integer
While sheet.Cells(startRow, 1).Value < ""
startRow = startRow + 1
Wend
ComputeNextEmptyRow = startRow
End Function

And this function is called like this:

row = ComputeNextEmptyTripRow(sh, TripStartRow)
If row 10 Then
MsgBox "There are " & row - 10 & " row(s) in AddedInfo sheet."
End If

Thx again for providing clear and useful help

regards,
Ladyhawke