Extract part of a Worksheet name.

[Casey]

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]

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


"Casey" skrev:

>
> 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
>
>

[JE McGimpsey]

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 <[email protected]>,
Casey <[email protected]> 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]

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

[[email protected]]

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

[Casey]

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.

[Dove]

Two more string functions that I find useful are:

'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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Casey]

David,
Thanks for the insights. I wish I had started this whole programming 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> Thanks again.