run-time error 91 on Msg Box Code

[Conell8383]

Hi all. I hope you can help I have a piece of code that is producing a run time error 91

I am trying to get a MsgBox to tell me if there is a sheet in my workbook called "In Progress" or else that is not in my workbook but it is creating an error on this line

If worksheet.sheet.name = "In Progress" Then

The rest of the code is below. Im sure I am not declaring something but I am at a loss as to what. Completely stumped so as always any and all help is greatly appreciated.

My code is here

CODE

Public Sub Msg_Box()


Dim wb As Workbook
    
    Set wb = ThisWorkbook
    
    Dim worksheet As worksheet
    
    
    With wb
    
    If worksheet.sheet.name = "In Progress" Then
    MsgBox "found"
    Else
    MsgBox "not found"
    
    
    End If
    
    End With


End Sub

 

[ScottR]

Try

Public Sub Msg_Box2()
'Ready an unset worksheet variable
Dim WS As worksheet

'Loop through each worksheet in the workbook
For Each WS In ThisWorkbook.sheets
'Evaluate the worksheet name
    If WS.Name = "In Progress" Then
        MsgBox "Found"
        'Exit if found
        Exit Sub
    End If
Next
'Only runs if no worksheet was found in the for loop, otherwise the sub is
'terminated
MsgBox "Not Found"

End Sub

 

[Conell8383]

Hi ScottR: Thank you for taking the time to respond. The code is unfortunately not working as hoped. I think this is because I am calling it, and the code is not telling it 'hey i mean this workbook'. My entire code actually first opens up a dialog box and that allows a user to pick an excel sheet, then calls the msg box code. When the code you have supplied me is called it Msg Boxes not found even though "In Progress" sheet exists in the workbook. I have supplied a picture for better understanding.

My complete code with Dialog box

Sub Open_Workbook_Dialog()


Dim my_FileName As Variant


    MsgBox "Pick your TOV file" '<--| txt box for prompt to pick a file


        my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*") '<--| Opens the file window to allow selection


    If my_FileName <> False Then
    Workbooks.Open Filename:=my_FileName




Call Msg_Box2      '<--|Calls the Filter Code and executes


End If




End Sub


Public Sub Msg_Box2()
'Ready an unset worksheet variable
Dim WS As Worksheet


'Loop through each worksheet in the workbook
For Each WS In ThisWorkbook.Sheets
'Evaluate the worksheet name
    If WS.Name = "In Progress" Then
        MsgBox "Found"
        'Exit if found
        Exit Sub
    End If
Next
'Only runs if no worksheet was found in the for loop, otherwise the sub is
'terminated
MsgBox "Not Found"


End Sub

 

[Trebor76]

Hi Conell8383,

Here's a non-looping way:

Option Explicit
Public Sub Msg_Box()

    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ThisWorkbook
    
    On Error Resume Next
        Set ws = wb.Sheets("In Progress")
        If Err.Number = 0 Then
            MsgBox "Found"
        Else
            MsgBox "Not Found"
        End If
    On Error GoTo 0

End Sub

HTH

Robert

 

[ScottR]

If I understand you correctly (you're trying to determine if the worksheet "In Progress" is in the workbook you're opening you can set it up to pass the opened workbook as a parameter to your sub routinue

Sub Open_Workbook_Dialog()


Dim my_FileName As Variant, [COLOR=#ff0000]myFile as workbook[/COLOR]


    MsgBox "Pick your TOV file" '<--| txt box for prompt to pick a file


        my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*") '<--| Opens the file window to allow selection


    If my_FileName <> False Then
    [COLOR=#ff0000]set myFile=[/COLOR]Workbooks.Open(my_FileName)




Call Msg_Box2[COLOR=#ff0000](myFile)[/COLOR]      '<--|Calls the Filter Code and executes


End If




End Sub


Public Sub Msg_Box2([COLOR=#ff0000]byref WB as workbook[/COLOR])
'Ready an unset worksheet variable
Dim WS As Worksheet


'Loop through each worksheet in the workbook
For Each WS In [COLOR=#ff0000]WB[/COLOR].Sheets
'Evaluate the worksheet name
    If WS.Name = "In Progress" Then
        MsgBox "Found"
        'Exit if found
        Exit Sub
    End If
Next
'Only runs if no worksheet was found in the for loop, otherwise the sub is
'terminated
MsgBox "Not Found"


End Sub

 

[Conell8383]

BOO YAAH!! ScottR you magician The code you gave me with the corrections in red worked like a charm. Thank you so much for taking the time to respond. It is greatly appreciated. Much respect from Dublin. I hope you have a great day, and thank you again for the help.