detecting open worksheet in open work book

  • Thread starter Thread starter windsurferLA
  • Start date Start date
W

windsurferLA

Using Excel'97 running under WinXP-pro w/ SR-2

To prevent an error when activating a specific worksheet in an open
workbook, I need to know if a specific worksheet is already the active
worksheet.

Forest example, a command such as:

If (Worksheets("scratch").Range("E3").Value = "") Then .... End If

can be used to tell if the a specific cell contains a value.



Is there a better way to determine if a specific worksheet in a workbook
is the active worksheet than by using the following code?

If((worksheets.active)= worksheets("scratch")) Then .... End If
 
Write a UDF (User-Defined-Function) like the following:

'----------------------------------------------------------------------
Public Function IsActiveWorksheet(strWorksheetName As String) As Boolean
Dim wsActive As Worksheet
Dim wsTest As Worksheet

On Error GoTo ExitFunction

Set wsActive = ActiveSheet
Set wsTest = Worksheets(strWorksheetName)

IsActiveWorksheet = (wsTest.Name = wsActive.Name)

ExitFunction:
End Function


If the active sheet happens to be a chart, the error handler will cause the
function to return False anyway.
 
You could try something like

If ActiveSheet.Name = Worksheets("Scratch").Name Then
' Scratch is active
Else
' Scratch is not active. make it so.
Worksheets("Scratch").Activate
End If

If you always need Scratch to be active when the workbook opens, just use

Private Sub Workbook_Open()
Me.Worksheets("Scratch").Activate
End Sub

This code doesn't care is Scratch is already active, it just ensures that it
is active.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Thanks for prompt reply and very useful suggestion.

Your code for ensuring sheet is active that doesn't care if sheet is
lready active is just want I wanted. It also introduced me to the short
cut "me" keyword which until now I had not used, even though I written
hundreds of macros.
 
Back
Top