Checkbox fires off a Statement

Hi,

pls try

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Provides check button
If Not Intersect(Target, [C4]) Is Nothing Then
'If Target.Column = 3 Then
    Application.ScreenUpdating = False
  
    With Target
        If .Value = "P" Then
            .Value = ""
        ElseIf .Value = "" Then
            .Font.Name = "Wingdings 2"
            .Value = "P"
        End If
   End With
   End If
    Cancel = True
    Application.ScreenUpdating = True
End Sub

Select all Open in new window

Regards

Regonzo,

Greetings!  Thanks for picking this up.  The first half of my "ask"?  You nailed it.  Thank you.

The second half is an addition to the Macro that allows text to be placed in a specified cell on the other Sheets  (Sheet needs to be named and cell where text is going).

So..... if the cell on Sheet 1 is "checked", the text is auto placed on the other sheets listed in the Macro + the specific cell on that sheet.  In this case, it will say "confidential" on the other sheets if the box is checked.

Thank you,


B.

then try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Provides check button
strSheets = "Sheet2,Sheet3"
strCellAddress = "B5"
If Not Intersect(Target, [C4]) Is Nothing Then
    Application.ScreenUpdating = False
  
    With Target
        If .Value = "P" Then
            .Value = ""
            For Each strSheet In Split(strSheets, ",")
                Sheets(strSheet).Range(strCellAdress).Value = ""
            Next
        ElseIf .Value = "" Then
            .Font.Name = "Wingdings 2"
            .Value = "P"
            For Each strSheet In Split(strSheets, ",")
                Sheets(strSheet).Range(strCellAdress).Value = "Confidential"
            Next
        End If
    End With
End If
Cancel = True
Application.ScreenUpdating = True
End Sub

Select all Open in new window

Edited code

Regonzo,

I get an error;

strSheets is not defined........

strSheets = "Sheet2,Sheet3"

Regonzo,

Almost.  I tested it and it almost works.  Here are two questions;

1.) If I want the "confidential" text to show up on Sheets 2 and 3, but want it to show up and disappear (depending on the status of the checkmark -- on/off) how do I distinguish between the cells?  Right now, it shows up at the same sell on each Sheet.

2.) If I want to simply say "all sheets" so as not to have to identify each individual sheet, what line of code would I use to substitute where it tells which sheets to place the text?

Thank you,

B.

Rgonzo, Great Job!!!  Thank you very much!  Appreciate your help.  I'm going to post another request for the two additional enhancements to the macro.  You did a great job.  No scope creep here ;-)

Be good and again, much thanks,

B.