Text Box to Cover sheet when done

[rki1966]

I am trying to find a way so that when someone has updated the sheet a text box covers the sheet ("Sheet is Updated"). The next person will hide the text box and review the sheet and then has a text box that indicates ("Sheet is final"). There are several sheets in the workbook.

 

[pbornemeier]

Place this code in a standard module:

Option Explicit

Sub CoverWorksheet(Optional sDisplayText As String)
    Dim sShape As String
    With ActiveWindow.VisibleRange
        ActiveSheet.Shapes.AddShape(msoShapeRectangle, .Left, .Top, .Width, .Height).Select
        Selection.Name = "Cover"
        Selection.Characters.Text = sDisplayText & vbLf & vbLf & _
        "To erase this textbox, click on it, press and release Esc and then press and release Delete"
    End With
    ActiveSheet.Shapes("Cover").Select
End Sub

Place this code on the code page of each worksheet where the code should be active:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sAnswer As String
    Dim sShape As String
    sAnswer = InputBox("'U' to show 'Sheet is Updated'.  " & vbLf & _
        "'F' to show 'Sheet is final'." & vbLf & _
        "Anything else to continue without covering sheet", "Cover worksheet?", "U")
    Select Case UCase(sAnswer)
    Case "U"
        CoverWorksheet "Sheet is Updated"
    Case "F"
        CoverWorksheet "Sheet is final"
    End Select

End Sub

Double-click the worksheet to bring up the input box.
U for the updated message
F for the Final message

 

[rki1966]

This worked. I did change it to Word Art instead of Tex Box. How do I write a macro that will delete all the Word Art on each sheet if there is one. Here is my macro but is not working.

Sub Clear_Text()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Shapes.SelectAll
Selection.Delete

Next ws

End Sub

 

[pbornemeier]

Sub Clear_Text()
    'Remove all shapes from all worksheets
 
    Dim ws As Worksheet
    Dim shp As Shape
 
    For Each ws In Worksheets
        For Each shp In ws.Shapes
            shp.Delete
        Next
    Next
 
End Sub

 

[rki1966]

Last question, how do I exclude one sheet. I want to leave the word art on on sheet.

 

[pbornemeier]

Sub Clear_Text()
    'Remove all shapes from all worksheets except one
 
    Dim ws As Worksheet
    Dim shp As Shape
 
    For Each ws In Worksheets
        If ws.Name <> "Leave Me Alone" Then
            For Each shp In ws.Shapes
                shp.Delete
            Next
        End If
    Next
 
End Sub

</PRE>





</PRE>