Extract part of a Worksheet name.

  • Thread starter Thread starter Casey
  • Start date Start date
C

Casey

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
 
Use the VBA Mid() function...

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

Worksheet functions that duplicate VBA functions aren't accessible in
VBA.
 
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
 
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
 
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
 
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
 
Back
Top