Display sheet names in a message box

Please can anyone help?

I am trying to create code that if certain conditions are met in the workbook, after clicking on a button a message box is displayed with text and references to the sheet names of where the condition is met.

My code is below for my IF statement but I am struggling to find a way to show the sheetname in a message box if the IF condition is true:

Code:

---

If (ThisWorkbook.Worksheets("Week 01").Buttons("ReSubmit").Visible = True) Or _
(ThisWorkbook.Worksheets("Week 02").Buttons("ReSubmit").Visible = True) Or _
(ThisWorkbook.Worksheets("Week 03").Buttons("ReSubmit").Visible = True) Or _
(ThisWorkbook.Worksheets("Week 04").Buttons("ReSubmit").Visible = True) Then
MsgBox "This function is not avaiable whilst you have an impending re-submission of" & " " & INSERT SHEETNAME HERE, vbInformation, "Function Not Available" 
Exit Sub
End If

---

Many thanks for taking the time to read my post and any help and assistance would be greatly received.

Hi

Welcome to ExcelFox!!

try

Code:

---

Dim i  As Long
Dim s  As String

For i = 1 To 4
    s = "Week " & Format(i, "00")
    If ThisWorkbook.Worksheets(s).Buttons("ReSubmit").Visible Then
        MsgBox "This function is not avaiable whilst you have an impending" & vbLf & _
                "re-submission of " & s, vbInformation, "Function Not Available"
        Exit Sub
    End If
Next

---

Fantastic many thanks for this, it works brilliantly! But......is it possible to show the names of all the sheets where the "ReSubmit" button is visible as it could be visible on more than one sheet?

Once again, many thanks for your help.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi

Thanks for the feedback. See this version.

Code:

---

Dim i  As Long
Dim s  As String
Dim a  As String

For i = 1 To 4
    s = "Week " & Format(i, "00")
    If ThisWorkbook.Worksheets(s).Buttons("ReSubmit").Visible Then
        a = a & vbLf & vbTab & vbTab & s
    End If
Next
If Len(a) > 1 Then
    MsgBox "This function is not avaiable whilst you have an impending" & vbLf & _
            "re-submission of " & a, vbInformation, "Function Not Available"
End If

---

Brilliant, this works perfectly, many thanks!

Many thanks for all your help and many thanks for your time to help me with this, much appreciated.