Extract part of a Worksheet name.

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
 
J

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

acampbell012

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
 
C

Casey

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
 
D

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
 
C

Casey

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use optionbutton value to select code to run. 5
Unable to make the name Property of the Range Object Local 6
How do you capture the name of the current worksheet in VBA? 12
Run-time error 9 when trying to copy a worksheet 4
new worksheet name 5
shorten worksheet name 1
Creating the name of a worksheet in a cell 2
rename a worksheet adding a sequential number at the end 1

Top