Visual basic macro in Excel.

[Jawaid]

Hi everyone,

Will appreciate if someone could advise me on how to do this:

In an Excel workbook I have a few sheets. I'm trying to write a macro which looks for a named sheet and if it is there do the required action and if it is not there then do nothing or simply show a message saying the sheet is not present.

I have the codes which action on the sheet IF the sheet is in the workbook but if not then macro stops with message "Out of range" or something similar.

Please, could somebody advise me how I can achieve this.

Many thanks,

Jawaid.

[extofer]

This is not a problem, you can do this: write an array of all sheet names (I believe it's woorkbook. sheetname ), then check if you have the name you need.
Here are some things I need to know...
How are you calling the macro to start?
Is the name a varialbe or are you passing it in?
Just tell me how I can get you started.

[Jawaid]

Thanks for replying. Here is a part of my code which, as you can see, is still incomplete but will give you some idea. In the first block, if the Return sheet does not exist in the workbook then the macro stops. I would like it to look for the sheet and if it does not exist then show a message to say it does not exist and go look for the next sheet in the next block.

[
Sub Sheetdel()
' Deletes selected sheets.
Application.DisplayAlerts = False
Sheets("Returns").Select
Dim Msg, Style, Title, Response, MyString
Msg = "Is This Multiple Returns?" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Please Confirm Return Type" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes"

' Perform some action. (Do nothing)
Else ' User chose No.
MyString = "No" ' Perform some action.
ActiveWindow.SelectedSheets.Delete
End If

Application.DisplayAlerts = False
Sheets("BInvoices").Select
ActiveWindow.SelectedSheets.Delete

Application.DisplayAlerts = False
Sheets("Customer's Details").Select
ActiveWindow.SelectedSheets.Delete

End Sub

[nel141168]

Function SheetExists(SheetName As String) As Boolean' returns TRUE if the sheet exists in the active workbook
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function

Example:
If Not SheetExists("MySheetName") Then
MsgBox "MySheetName doesn't exist!"
Else Sheets("MySheetName").Activate
End If

[Paul Friedman]

Hi everyone, Will appreciate if someone could advise me on how to do this: In an Excel workbook I have a few sheets. I'm trying to write a macro which looks for a named sheet and if it is there do the required action and if it is not there then do nothing or simply show a message saying the sheet is not present. I have the codes which action on the sheet IF the sheet is in the workbook but if not then macro stops with message "Out of range" or something similar. Please, could somebody advise me how I can achieve this. Many thanks, Jawaid.

Here's one way--

'Macro to do an action on sheet named "Sheet_X"

For S=1 to Sheets.Count
If Sheets(S).Name = "Sheet_X" then
'Do the action on Sheets(S)
Endif
Next S