Solved

Checkbox Notification

Posted on 2016-11-16
3
46 Views
Last Modified: 2016-11-17
I have a great little WB here that Ragonzo helped me with originally building.  

Here are the two enhancements I need:

 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 in advance.

B.
Checkboxv2.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41890640
Try the below macro instead. To specify the cell for Sheet2 and Sheet3, modify the address in lines 19 and 21. The macro will also populate the specified cell in lines 22-23 for every worksheet, except for Sheet1.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Application.EnableEvents = False
    
    Dim ws As Worksheet, CellValue As String
    
    If Target.Value = "P" Then
        CellValue = ""
        Target.Value = ""
    Else
        CellValue = "Confidential"
        Target.Value = "P"
    End If
    
    For Each ws In Worksheets
        If ws.Name <> Me.Name Then
            Select Case ws.Name
                Case "Sheet2"
                    ws.Range("C5").Value = CellValue
                Case "Sheet3"
                    ws.Range("D6").Value = CellValue
                Case Else
                    ws.Range("C5").Value = CellValue
            End Select
        End If
    Next
    
    Cancel = True
    
    Application.EnableEvents = True
    
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 41890753
This looks promising!!!  Let me test it.

B.
0
 

Author Closing Comment

by:Bright01
ID: 41891239
Wayne,

Thank you very much!  Fast, accurate and I learned something new.  What more could I ask for?  Thanks again,

B.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question