Test worksheet name

[Petr]

Hallo,
I would need to test whether the active sheet has one of the name from
several possibilities.

I tried the simple code below, but it is ending with error.

Sub worksheet_name_test()

Dim wks As Worksheet
If Not wks.Name = "Revenue" Or wks.Name = "SF Revenue" Or wks.Name =
"Rbn" Then
MsgBox "Correct sheet was not chosen!", vbCritical
End If
End Sub


Thank you in advance for any suggestions.

Petr Duzbaba

 

[Ron de Bruin]

Hi Petr

Try this one

With ActiveSheet
If Not .Name = "Revenue" Or .Name = "SF Revenue" _
Or .Name = "Rbn" Then
MsgBox "Correct sheet was not chosen!", vbCritical
End If
End With

 

[keepITcool]

Petr,

on behalf of all Dutchmen: thanks for getting us into the
quarterfinals, I hope you'll regret it in the finals

Dim wks As Worksheet
If Not wks.Name = "Revenue" Or wks.Name = "SF Revenue" Or wks.Name =

if you just want to test the activesheet, you dont need the wks variable
=> use instead activesheet.name etc..

however you probably included the wks variable to keep your code short.
BUT you forgot to assign it a value (or in this case an object)

dim wks as worksheet
SET wks = activesheet
....your code

alternatively use a select case statement:

select case lcase$(activesheet.name)
case "revenue","sf revenue","rbn"
'ok
case else
msgbox "Please select a Revenue sheet!", vbCritical
end select





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Ron de Bruin]

Hi keepITcool

on behalf of all Dutchmen: thanks for getting us into the
quarterfinals, I hope you'll regret it in the finals

Here is a nice picture for the English fans<g>
http://www.rondebruin.com/beckss.jpg

 

[Don Guillett]

If Not wks.Name = "Revenue" Or wks.Name = "SF Revenue" Or wks.Name = "Rbn"
Then

If wks.Name <> "Revenue" Or wks.Name <> "SF Revenue" Or wks.Name <> "Rbn"
Then
or try with AND
If wks.Name <> "Revenue" and wks.Name <> "SF Revenue" and wks.Name <> "Rbn"
Then