Hi,
I’m having problems extracting part of a worksheet name to compare to
text string.
I’m getting a 438 error Object doesn’t support this property or method
I have looked in help and the Mid function doesn’t seem to be supporte
in VBA, but I’m at a loss for a substitute. I just need to check if th
first three letters of the worksheet name = CWR

Here is an excerpt of my code.

ElseIf ActiveSheet.Name = "CWR LOG" Then
ComboBoxTopics.ListIndex = 2
ElseIf Application.WorksheetFunction.Mid(ActiveSheet.Name , 1, 3)
"CWR" Then 'This is where the error hits.
ComboBoxTopics.ListIndex = 1

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=56220

try
ElseIf Mid(ActiveSheet.Name, 1, 3) = "CWR" Then


"Casey" skrev:


Hi,
Im having problems extracting part of a worksheet name to compare to a
text string.
Im getting a 438 error Object doesnt support this property or method.
I have looked in help and the Mid function doesnt seem to be supported
in VBA, but Im at a loss for a substitute. I just need to check if the
first three letters of the worksheet name = CWR

Here is an excerpt of my code.

ElseIf ActiveSheet.Name = "CWR LOG" Then
ComboBoxTopics.ListIndex = 2
ElseIf Application.WorksheetFunction.Mid(ActiveSheet.Name , 1, 3) =
"CWR" Then 'This is where the error hits.
ComboBoxTopics.ListIndex = 10


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=562204

Use the VBA Mid() function...

ElseIf Mid(ActiveSheet.Name, 1, 3) = "CWR" Then

Worksheet functions that duplicate VBA functions aren't accessible in
VBA.

In article ,
Casey wrote:

Hi,
I’m having problems extracting part of a worksheet name to compare to a
text string.
I’m getting a 438 error Object doesn’t support this property or method.
I have looked in help and the Mid function doesn’t seem to be supported
in VBA, but I’m at a loss for a substitute. I just need to check if the
first three letters of the worksheet name = CWR

Here is an excerpt of my code.

ElseIf ActiveSheet.Name = "CWR LOG" Then
ComboBoxTopics.ListIndex = 2
ElseIf Application.WorksheetFunction.Mid(ActiveSheet.Name , 1, 3) =
"CWR" Then 'This is where the error hits.
ComboBoxTopics.ListIndex = 10

excelent & JE McGimpsey,
Thanks for the replies. Works fine now

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=56220

Casey

Here is a snipet of code I use for looping through one of my workbooks
searching for sheet names. Note the use of the wildcard.

Alan

Dim sht as Worksheet
For Each Sht In Wkbk.Sheets
If Sht.Name Like "*My_Sheet" Then
'your code here
End If
Next Sht


Casey wrote:
Hi,
I'm having problems extracting part of a worksheet name to compare to a
text string.
I'm getting a 438 error Object doesn't support this property or method.
I have looked in help and the Mid function doesn't seem to be supported
in VBA, but I'm at a loss for a substitute. I just need to check if the
first three letters of the worksheet name = CWR

Here is an excerpt of my code.

ElseIf ActiveSheet.Name = "CWR LOG" Then
ComboBoxTopics.ListIndex = 2
ElseIf Application.WorksheetFunction.Mid(ActiveSheet.Name , 1, 3) =
"CWR" Then 'This is where the error hits.
ComboBoxTopics.ListIndex = 10


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=562204

Alan,
Thanks for the reply. If you look at the entire thread; I got th
answer I needed, however I think your reply may help with anothe
problem I have with a different project. Thanks again

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=56220

Two more string functions that I find useful a

'For what you are looking for
If Left$(ActiveSheet.Name,3) = "CWR" Then

It's related funcion is Right$. I know that these are supported through all
versions of Excel VBA I have worked with, up through 2003 as carried over
functions from the days of DOS Basic. I believe that you can leave off the
$ but I use it as a habit from the old days.

David

"Casey" wrote in
message ...

Alan,
Thanks for the reply. If you look at the entire thread; I got the
answer I needed, however I think your reply may help with another
problem I have with a different project. Thanks again.


--
Casey


------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=562204

David,
Thanks for the insights. I wish I had started this whole programmin
stuff in my younger days so I would have some habits from the old days
As it is; I'm old and all of my habits are bad period. <vbg Thank
again

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=56220