Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to load a list of web links into specific worksheets? | Excel Worksheet Functions | |||
load event when specific workbook is opened | Excel Programming | |||
How to detect if sheet is hidden? | Excel Worksheet Functions | |||
Excel VBA - Opening a workbook to a specific sheet every time | Excel Programming | |||
Function to detect if Sheet is protected | Excel Programming |