PDA

View Full Version : [SOLVED] Check sheet names and display MsgBox



ilyaskazi
05-31-2005, 01:08 AM
following are the sheet names in the workbook...

chk-fares, fares, rules, zones, temp, sheet1, temp-zones
from the above given sheets, 3 sheets (names given below) are always constant....
fares, rules, zones

workbook may contain any number of sheets and by any names. but if any 1 or 2 or all the 3 constant sheets found in the workbook, then show msgbox as="yes ap-temp" else show msgbox as "not ap-temp"

Richie(UK)
05-31-2005, 02:34 AM
Hi,

Please don't SHOUT when you post questions. Thanks :)
(Edit : I see the post has been amended for you)

You could try making a function that will return True or False depending upon whether the workbook contains one of the specified worksheets. Something like this perhaps:

Sub test()
Dim varWSNames As Variant
varWSNames = Array("FARES", "RULES", "ZONES")
MsgBox "Does the workbook contain one of the specified sheets?" & vbNewLine & _
SheetsExist(varWSNames)
End Sub

Function SheetsExist(varSheetNames As Variant, Optional wbk As Workbook) As Boolean
Dim lCnt As Long, ws As Worksheet
SheetsExist = False
If wbk Is Nothing Then Set wbk = ActiveWorkbook
For lCnt = LBound(varSheetNames) To UBound(varSheetNames)
On Error Resume Next
Set ws = wbk.Worksheets(varSheetNames(lCnt))
On Error GoTo 0
If Not ws Is Nothing Then
SheetsExist = True
Exit For
End If
Next lCnt
End Function

HTH

ilyaskazi
05-31-2005, 03:21 AM
some or the other way its working fine...thanku

let me check to my requirement and then I will be back.

ilyaskazi
06-09-2005, 01:19 AM
Yes it is ok.
But now actually my need is to check if all the sheet exist which is stored in arrays.

At present your code is checking if any of the sheet exist. I want true answer only if all the sheets exist.

Richie(UK)
06-09-2005, 03:48 AM
Hi,

Like this?


Sub test()
Dim varWSNames As Variant
varWSNames = Array("FARES", "RULES", "ZONES")
MsgBox "Does the workbook contain all of the specified sheets?" & vbNewLine & _
AllSheetsExist(varWSNames)
End Sub

Function AllSheetsExist(varSheetNames As Variant, Optional wbk As Workbook) As Boolean
Dim lCnt As Long, ws As Worksheet
AllSheetsExist = True
If wbk Is Nothing Then Set wbk = ActiveWorkbook
For lCnt = LBound(varSheetNames) To UBound(varSheetNames)
On Error Resume Next
Set ws = wbk.Worksheets(varSheetNames(lCnt))
On Error GoTo 0
If ws Is Nothing Then
AllSheetsExist = False
Exit For
End If
Set ws = Nothing
Next lCnt
End Function

HTH

ilyaskazi
06-09-2005, 05:30 AM
Exactly,

Fantastic. Thanku.

ilyaskazi
06-09-2005, 05:40 AM
if found true, how to import sheets in new workbook.

import only which matches the names containing in arrays.

ilyaskazi
06-09-2005, 09:01 AM
ok i hv done myself. thanku.. :hi:

Zack Barresse
06-09-2005, 09:11 AM
Hi,

Also, a couple of other methods in our KB to test for sheets ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=187
http://www.vbaexpress.com/kb/getarticle.php?kb_id=420
http://www.vbaexpress.com/kb/getarticle.php?kb_id=389 (chart only)


(Hiya Richie!!)