Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a sheet is present in a workbook
Hello All,
I need some help in programming a macro. I need to check whether a specific worksheet is present in a workbook. If it is present, the macro follows one logic and if it does not it follows another logic. I tried referring to an cell value in that sheet, If ActiveWorkbook.Sheets("Alignment_Retail").Range("A 1").Value = "Select" Then But, while running the code, if the sheet "Alignment_Retail" is not present in the workbook, VBA immediately throws an "subscript out of range" error. Can some one please help me to draw out a different logic. All I need is to validate, whether the sheet is present in the workbook or not. I cannot use the worksheet count logic as the number of sheets can vary. Hoping to hear from you!!! Thanks in advance, Sagar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a sheet is present in a workbook
Add this function
Public Function SheetExists(SName As String, _ Optional ByVal Wb As Workbook) As Boolean 'Chip Pearson On Error Resume Next If Wb Is Nothing Then Set Wb = ThisWorkbook SheetExists = CBool(Len(Wb.Sheets(SName).Name)) End Function and then use it something like this... if sheetexists("Alignment_Retail", activeworkbook) then ActiveWorkbook.Sheets("Alignment_Retail").Range("A 1").Value = "Select" end if -- HTH... Jim Thomlinson "Sagu" wrote: Hello All, I need some help in programming a macro. I need to check whether a specific worksheet is present in a workbook. If it is present, the macro follows one logic and if it does not it follows another logic. I tried referring to an cell value in that sheet, If ActiveWorkbook.Sheets("Alignment_Retail").Range("A 1").Value = "Select" Then But, while running the code, if the sheet "Alignment_Retail" is not present in the workbook, VBA immediately throws an "subscript out of range" error. Can some one please help me to draw out a different logic. All I need is to validate, whether the sheet is present in the workbook or not. I cannot use the worksheet count logic as the number of sheets can vary. Hoping to hear from you!!! Thanks in advance, Sagar |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a sheet is present in a workbook
Here is one way:
Sub look() On Error Resume Next If Len(ThisWorkbook.Worksheets.Item("Alignment_Retail ").Name) = 0 Then On Error GoTo 0 MsgBox "The sheet isn't there." Else MsgBox "The sheet is there." End If End Sub I've done this before, but now use a sheet management procedure in all my projects. Cliff Edwards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check whether a sheet is present in a workbook
It worked out perfectly..
Thanks a million for you help, Cliff !! Thanks, Sagar On Jun 9, 10:19*am, ward376 wrote: Here is one way: Sub look() On Error Resume Next * * If Len(ThisWorkbook.Worksheets.Item("Alignment_Retail ").Name) = 0 Then On Error GoTo 0 * * * * MsgBox "The sheet isn't there." * * Else * * * * MsgBox "The sheet is there." * * End If End Sub I've done this before, but now use a sheet management procedure in all my projects. Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating if a Sheet is present in a workbook | Excel Discussion (Misc queries) | |||
List all sheets present in Workbook | Excel Worksheet Functions | |||
Command for present sheet | Excel Programming | |||
Check if sheet exists in a closed workbook | Excel Programming | |||
How Do I Check To See Id A Character Is Present In A Cell | Excel Programming |