Test worksheet name
 

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

Test worksheet name
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Petr" wrote in message om...
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

Test worksheet name
 

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


(Petr) wrote:

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

Test worksheet name
 

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message ...
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


(Petr) wrote:

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

Test worksheet name
 

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

--
Don Guillett
SalesAid Software

"Petr" wrote in message
om...
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