Error in Code: Previous = ActiveSheet.name

Question

I have used the following code to return the user to the previous worksheet for years (Office 2003 and 2007) but am now getting the error, "Compile error: Can't find project or library" when I try to run it in Office 2010. I see the following highlighted, "Previous = ActiveSheet.name".

Does anyone have an idea why this would no longer work please? The full code follows (when he/she leaves a worksheet the first macro runs and then when a button is pressed to return, the second runs):

Sub FreezePreviousWorksheet()

    Previous = ActiveSheet.name

    Sheets("Summary").Range("A1").Value = Previous

End Sub

Sub ReturnToPreviousWorksheet()

    Previous = Sheets("Summary").Range("A1")

    Sheets(Previous).Select

End Sub

I am also getting the same error in another macro that I have used for years (the "Cancel = True" is highlighted in this case):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If Application.CutCopyMode = False Or Sheets("Summary").Range("a3") = 1 Then

                Cancel = True

        Else

Thanks very much in advance for your help.

Carl

Answer 1

Hi,

in addition to Jeeped's answer..

try this..

Sub FreezePreviousWorksheet()

Dim Previous As String

Previous = ActiveSheet.Name

Sheets("Summary").Range("A1").Value = Previous

End Sub

Sub ReturnToPreviousWorksheet()

Dim Previous As String

Previous = Sheets("Summary").Range("A1").Value

Sheets(Previous).Select

End Sub

Answer 2

The first thing I notice is that you are not explicitly declaring the variables that you are using. In the VBE, go to Tools, Options and see if the Require Variable Declaration option is set in the top Code Settings group of the Options dialog. This coding option can also be overrode with an Option Explicit statement in your module sheet but that wouldn't seem to be the case here unless it was recently added.

Option Explicit Statement (Visual Basic)

In regard to your second worksheet code, the Cancel = True statement seems to belong to a Worksheet_BeforeDoubleClick routine rather than a Worksheet_SelectionChange routine. This is typically used to optionally halt Excel launching into an in-cell editing mode when a cell is double clicked. It is typically used like this,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address(0, 0) = "A1" Then

Cancel = True

Target = "dblclicked"

End If

End Sub

Note that the Cancel variable is declared within the sub's declaration. In this manner, double-clicking on A1 will populate the cell with dblclicked and stop Excel from entering in-cell editing mode while double-clicking on any other cell will allow in-cell editing mode. I'm not entirely sure what purpose it has in your Worksheet_SelectionChange routine.

Worksheet.BeforeDoubleClick Event

Answer 3

The first thing I notice is that you are not explicitly declaring the variables that you are using. In the VBE, go to Tools, Options and see if the Require Variable Declaration option is set in the top Code Settings group of the Options dialog. This coding option can also be overrode with an Option Explicit statement in your module sheet but that wouldn't seem to be the case here unless it was recently added.

Option Explicit Statement (Visual Basic)

In regard to your second worksheet code, the Cancel = True statement seems to belong to a Worksheet_BeforeDoubleClick routine rather than a Worksheet_SelectionChange routine. This is typically used to optionally halt Excel launching into an in-cell editing mode when a cell is double clicked. It is typically used like this,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Address(0, 0) = "A1" Then

Cancel = True

Target = "dblclicked"

End If

End Sub

Note that the Cancel variable is declared within the sub's declaration. In this manner, double-clicking on A1 will populate the cell with dblclicked and stop Excel from entering in-cell editing mode while double-clicking on any other cell will allow in-cell editing mode. I'm not entirely sure what purpose it has in your Worksheet_SelectionChange routine.

Worksheet.BeforeDoubleClick Event

Thanks for taking the time to help. Reading your instructions made me realize I still have a LOT to learn about writing good code. I am going to have to really study what you have said to learn how to correct deficits in my code.

By the way, I finally figured out the reason for all the errors in code that had previously worked fine for years in other versions of Office…I had a reference to “Microsoft Office Soap Type Library v3.0” from a previous installation of Office 2003.

When I upgraded to Office 2010 this reference was no longer valid, which was causing all the problems and error messages "Compile error: Can't find project or library". I corrected the bad reference and all works perfectly now.

The reason I use the code in my Worksheet_SelectionChange routine is to stop someone from copying and pasting from one cell to another in my worksheet to prevent corruption of any conditional formatting or formulas in some cases.

If the user copies to the clipboard and selects any cell in the sheet, it shows a notice and stops the paste. It does not apply if there is nothing on the clipboard or if I have placed a “1” in cell A3 of the “Summary” sheet.

Thanks again.

Carl

Answer 4

Hi,

in addition to Jeeped's answer..

try this..

 

Sub FreezePreviousWorksheet()

Dim Previous As String

Previous = ActiveSheet.Name

Sheets("Summary").Range("A1").Value = Previous

End Sub

Sub ReturnToPreviousWorksheet()

Dim Previous As String

Previous = Sheets("Summary").Range("A1").Value

Sheets(Previous).Select

End Sub

Thanks so much for the help. I made the changes you suggested because that was a better way to write my code but it did not solve the problem.

I finally figured out the reason for all the errors in code that had previously worked fine for years in other versions of Office…I had a reference to “Microsoft Office Soap Type Library v3.0” from a previous installation of Office 2003. 

When I upgraded to Office 2010 this reference was no longer valid, which was causing all the problems. I corrected the bad reference and all works perfectly now.

Thanks again,

Carl