VBA: How to identify a sheet (sheet prior to activesheet)

[BIGTONE559]

I need help with a method of identify the sheet that user was on prior to the active sheet.

For example,

If i have 10 sheets and the user opens the workbook on sheet 1. If the user goes to sheet 10 how could i create a variable to identify the sheet that the user was on prior to sheet 10.

I have the following code:

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
Dim aShRange As Range
Dim aSh As Worksheet
    

Set aSh = ActiveSheet

Set aShRange = sh.Range("C3")


If sh.Name <> "HExport" Then
    If aShRange.Value = "" Then 'test if the value in c3 is blank if it's blank it will name it the index

    aSh.Name = aSh.Index
    Else
    
    
  
    On Error Resume Next
    
    'I have serious issues here.  i'm trying to index the sheet whenever a sheet name is changed however, i keep having issues with it.  
    
    sh.Name = aShRange.Value
    Exit Sub
    
    End If

End If
End Sub

If it had some way of identifying the sheet Prior to the sheet that was selected i could make changes in my code to account for that.

Thanks

 

[Joe4]

That's funny. I helped someone else with the same question last week:
http://www.mrexcel.com/forum/showthread.php?t=579066

 

[BIGTONE559]

That's funny. I helped someone else with the same question last week:
http://www.mrexcel.com/forum/showthread.php?t=579066

Hey Joe Thanks for the reply.
i tried the code in your reference and it didn't work. I thought it wasn't working because i had code in the Private Sub Worksheet deactivate already. However when i deleted the code, every time i run the macro the msgbox pops up. It's almost as if sheet changes aren't even registering.



Please Help

 

[BIGTONE559]

Hey Joe Thanks for the reply.
i tried the code in your reference and it didn't work. I thought it wasn't working because i had code in the Private Sub Worksheet deactivate already. However when i deleted the code, every time i run the macro the msgbox pops up. It's almost as if sheet changes aren't even registering.



Please Help

Sorry Joe guess it helps if you read directions completely. I wasn't declaring the public variable and that's where i hit a snag. Again i say thanks for the help and thanks for teaching me how to fish (I love your sig)