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.
--
Regards,
Bill Renaud

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" <(E-Mail Removed)> wrote in message
news:47119d29$0$9546$(E-Mail Removed)...
> 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

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.



Chip Pearson wrote:
> 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.
>
>