How to get Worksheet_Activate to run on open as well

[BAlGaInTl]

Hey all.

I came up with the following routine to check to see if the last useable row has been used in a sheet that I have designed.

Private Sub Worksheet_Activate()
    If Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = "" And _
        Cells(Rows.Count, 1).End(xlUp).Offset(-1, 1).Value <> "" Then
    AppendRows
    End If
End Sub

AppendRows is another script that asks for a user input and copies and pastes the last row X number of times. I came up with the above to force the user to run the AppendRows before accidentally filling out the last row. If that happens, AppendRows copies and pastes the data they put in the last row.

This was done to save on file size as there are about 60 columns of calculations.

It works fine when the user switches tabs.

The problem is that if a user fills in what is the last useable line and then closes the workbook, the routine does not catch it the next time it runs.

Is there a way that I can also have the check done on whatever the active sheet is when the workbook opens?

Thanks

 

[NdNoviceHlp]

I think you would best be served by using the wb before close event and run your code on the active sheet. If you want, the wb open event will probably also do. Good luck! Dave

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code here
End Sub

Private Sub Workbook_Open()
'your code here
End Sub

 

[BAlGaInTl]

I think you would best be served by using the wb before close event and run your code on the active sheet. If you want, the wb open event will probably also do. Good luck! Dave

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code here
End Sub

Private Sub Workbook_Open()
'your code here
End Sub

I tried this, but if one of the sheets that don't need this function are open, an error is generated.

Is there a way to check to see if a named range exists in the sheet that is open before it runs?

 

[NdNoviceHlp]

You can try

On Error Resume Next

or address the specific error or... I'm sure you could find a named range but this seems more difficult than it should be. The wb before close event should check if your routine has been run and if not then run it. You are running the routine on wb open whether it needs it or not? The error I assume means that your sub crashes if it runs twice with the same inputs/conditions. If this is the case, the error routine is the easy fix. Dave