Slight Modification to Macro

Posted on 2016-11-17
Medium Priority
Last Modified: 2016-11-17
I had this question after viewing Checkbox Notification.

I have one mod. that needs to be made here.  Right now it looks at "active cell" for a double click to produce the checkmark.  I need it to look at A SPECIFIC CELL.  In the case of the example attached, it needs to look at "Sheet1, Cell C4".  

Thank you in advance!

Question by:Bright01
LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 41891330
This should take care of the active cell problem

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Application.EnableEvents = False
    Dim ws As Worksheet, CellValue As String
    If Target.Address = Range("C4").Address Then
        If Target.Value = "P" Then
            CellValue = ""
            Target.Value = ""
            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("F7").Value = CellValue
                End Select
            End If
    End If
    Cancel = True
    Application.EnableEvents = True
End Sub

Open in new window


Author Closing Comment

ID: 41891513
Saqib,  Thank you very much!  Works perfectly.  


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

624 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