Results 1 to 5 of 5

Thread: Display sheet names in a message box

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    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.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    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
    Last edited by DocAElstein; 06-10-2023 at 04:18 PM.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 8
    Last Post: 05-21-2013, 06:34 AM
  2. Message Box Pop-Up "yes or no"
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 02-19-2013, 06:20 PM
  3. VBA Show Message On Sheet Activate
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 10-29-2012, 08:17 PM
  4. Message Box Before Saving Document
    By Lucero in forum Excel Help
    Replies: 2
    Last Post: 04-15-2012, 07:09 AM
  5. Delete Names In A Specific Worksheet
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-14-2011, 02:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •