Solved

Checkbox Notification

Posted on 2016-11-16
3
36 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
  • 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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