detecting open worksheet in open work book

[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

 

[Bill Renaud]

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.

 

[Chip Pearson]

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)

 

[windsurferLA]

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.