[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding worksheet change event when there is already a SelectionChange event associated with worksheet

Posted on 2014-08-14
11
Medium Priority
?
240 Views
Last Modified: 2014-08-14
I am a VBA novice, so my apologies for this basic question. How do you add a General Declarations + Worksheet Change event to a worksheet (called IAP_Charts_Pub) that already has a Worksheet SelectionChange event associated with it? I tried pasting the Worksheet Change code above the Worksheet SelectionChange code associated with IAP_Charts_Pub, but that is triggering a debug error (at the line "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"). I've included both event codes below:

PRE-EXISTING CODE (Worksheet SelectionChange)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 31 Or Target.Column = 48 Or Target.Column = 65 Or Target.Column = 82 Or Target.Column = 99 Or Target.Column = 116 Then ' Col AD
        If Target.Value Like "##-####" Then
            If Target.Value Like "##-9###" Then
             addDND = "DND\"
          Else
                addDND = ""
          End If
          Application.EnableEvents = False
          Target.Formula = "=HYPERLINK(""Q:\20" & Left(Target, 2) & "\" & addDND & Target.Value & Chr(34) & "," & Chr(34) & Target.Value & Chr(34) & ")"
          Application.EnableEvents = True
    End If
End If
End Sub

Open in new window

NEW EVENT CODE TO BE ADDED (General Declarations + Worksheet Change):
Option Explicit
Dim rng As Range
Dim cl As Object
Dim boolChange As Boolean
Dim x As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    If boolChange Then
        boolChange = False
        Exit Sub
    End If
    Set rng = Range("AD2", Range("AD2").End(xlDown))
    For Each cl In rng
        For x = 2 To 27 Step 5
            If cl.Offset(0, x) = "NEW" Or cl.Offset(0, x) = "REVISED" Then
                boolChange = True
                cl.Value = Format(Now(), "dd-mmm-yy")
                Exit For
            End If
        Next x
    Next cl
    boolChange = False
End Sub

Open in new window

Thanks,
Andrea
0
Comment
Question by:Andreamary
  • 7
  • 4
11 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40260897
I'm confused about what you want to do since SelectionChange and Change are different events that both already exist. SelectionChange occurs when you move from one cell to another and Change occurs after you leave a cell that you have changed.
0
 

Author Comment

by:Andreamary
ID: 40261188
The SelectionChange event was a solution from this website (let's call it hyperlinking) that I applied to my worksheet a few months ago. Recently, I needed another VBA solution (let's call it auto-date update) for a different section of the same worksheet, which again was provided to me through this website. Now I need to know how to add the second VBA solution to my worksheet, given that there is already the existing code associated with this worksheet.

I hope that helps.

Andrea
0
 

Author Comment

by:Andreamary
ID: 40261203
Further to the above, I combined the code (see below) for both solutions, and I'm getting a Compile Error at line 29 for the phrase "addDND":

Option Explicit
Dim rng As Range
Dim cl As Object
Dim boolChange As Boolean
Dim x As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
    If boolChange Then
        boolChange = False
        Exit Sub
    End If
    Set rng = Range("AD2", Range("AD2").End(xlDown))
    For Each cl In rng
        For x = 2 To 27 Step 5
            If cl.Offset(0, x) = "NEW" Or cl.Offset(0, x) = "REVISED" Then
                boolChange = True
                cl.Value = Format(Now(), "dd-mmm-yy")
                Exit For
            End If
        Next x
    Next cl
    boolChange = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 31 Or Target.Column = 48 Or Target.Column = 65 Or Target.Column = 82 Or Target.Column = 99 Or Target.Column = 116 Then ' Col AD
        If Target.Value Like "##-####" Then
            If Target.Value Like "##-9###" Then
             addDND = "DND\"
          Else
                addDND = ""
          End If
          Application.EnableEvents = False
          Target.Formula = "=HYPERLINK(""Q:\20" & Left(Target, 2) & "\" & addDND & Target.Value & Chr(34) & "," & Chr(34) & Target.Value & Chr(34) & ")"
          Application.EnableEvents = True
    End If
End If
End Sub

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

Expert Comment

by:Martin Liss
ID: 40261224
I'm still not sure I understand the question but you can use Intersect to do different things.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("A1:A6")) Is Nothing Then
    MsgBox "I selected " & Target.Address
Else
    MsgBox "I'm somewhere else"
End If
End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40261230
What is addDND?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40261265
It also might help if you attached your workbook and described in words, rather than code, what you are trying to do.
0
 

Author Comment

by:Andreamary
ID: 40261383
Good idea, Martin. I've created a sample worksheet to which I want to add the following functionality:

If the value in Column AF is updated to "NEW" or "REVISED", then the date in Column AD changes to "13 Nov 14".

I would like to have this solution in both VBA and as a macro, as I may decide to go the macro route.

Let me know if you need anything further...thanks!

 Thanks,
 Andrea
Sample-UpdateEFFDate.xlsm
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40261434
I'm going out for lunch and will get back to you within a couple of hours.
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40261717
Here's your workbook updated with a Worksheet_Change event that looks like this
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("AF2:AF" & ActiveSheet.UsedRange.Rows.Count)) Is Nothing Then
    Select Case UCase(Target.Value)
        Case "NEW", "REVISED"
            Range("AD" & Target.Row).Value = #8/13/2014#
    End Select
End If
End Sub

Open in new window


and a macro (in Module1) that looks like this
Sub NewRevised()
Dim lngRow As Long

For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    Select Case UCase(Cells(lngRow, 32).Value) ' 32 = row AF
        Case "NEW", "REVISED"
            Range("AD" & lngRow).Value = #8/13/2014#
    End Select
Next
End Sub

Open in new window

Q-28497661.xlsm
0
 

Author Closing Comment

by:Andreamary
ID: 40261768
Terrific, Martin. All good. Thanks for your patience!
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40261785
You're welcome and I'm glad I was able to help. Note that while there's noting wrong with the way you coded your SelectionChange event, line 26 in post ID : 40261203 it could have been more easily coded using a Select Case structure like I did in my ChangeEvent code.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

873 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