Event Procedure - Worksheet_SelectionChange

Hello,

I have an excel with data validation lists in c11 and c19, based on code selected in c11, the drop down list in c19 changes to ones that are linked to that specific value in c11.

I want to add Worksheet_SelectionChange so that when each time the value in c11 is changed, c19 value is cleared automatically.

Can someone plz help me with that?

Thanks!

It seems what you are looking for is Worksheet_Change event with Intesection between Target & cell C11 of the worksheet.

Worksheet_SelectionChange event is fired every time the user changes the active cell by selecting another cell. Worksheet_Change event is fired when any cell is changed by the user or an external link. However, the event wouldn't fire if the cell value changes because of a formula.

Hope this helps !

Originally Posted by LJMetzger

Hi saksmb and Jewel,

I agree with Jewel.

Here are the details:
1. If a cell is changed manually or by VBA, Workseet_Change() is needed.

2. If a cell is changed manually or by VBA and the old value is needed:
a. A global variable is needed to save the old value.
b. Worksheet_SelectionChange() is needed to identify the value of the old value.
c. Workseet_Change() is needed to identify the new value, and possibly take further action.

3. If a range of values is changed by Formula (including a range containing only one cell):
a. A global array of variables is needed to store the old values.
b. The global array needes to be initialized by Workbook_Open().
c. Worksheet_Calculate() is needed to identify which cells in the range changed value.

See the code below or the attached file which implements all of the above.

Lewis

Code like the following needs to be in ThisWorkbook (Formula changes cells):

Private Sub Workbook_Open()
  Call InitialzeSheet1SavedValuesForWorksheetCalculate
End Sub

Code like the following needs to be in an ordinary module (Formula changes cells):

Option Explicit

'The following 'Global Variable' saves the 'Old' value of Cell 'A3' 'Worksheet_Change()'
Public vGblSheet1OldA3Value As Variant

'The following defines the range that will be examined in 'Worksheet_Calculate()'
Public Const sGblSheet1RANGE = "B13:B14"


'The following is the array that contains the 'old values' for the range that will be examined in 'Worksheet_Calculate()'
Public vGblSheet1CalculatedOldValues() As Variant


Sub InitialzeSheet1SavedValuesForWorksheetCalculate()

  Dim myRange As Range
  Dim r As Range
  Dim i As Integer
  Dim iItemsInRange As Integer
 
  'Define the range
  Set myRange = Range(sGblSheet1RANGE)
 
  'Redimension the dynamic array to hold the number of items in the range
  'Save the original values in the range
  iItemsInRange = myRange.Count
  If iItemsInRange > 0 Then
    ReDim vGblSheet1CalculatedOldValues(1 To iItemsInRange)
  End If
 
  For Each r In myRange
    i = i + 1
    vGblSheet1CalculatedOldValues(i) = r.Value
  Next r

 'Clear the range object
 Set myRange = Nothing
  
End Sub

Code like the following needs to be in the 'Sheet' Module:

Option Explicit

'The following variable indicates whether cell 'A3' was changed MANUALLY or by VBA
Private bHaveSelectionChange As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
  'This traps a Manual or VBA Change to the value of Cell 'A3'
  
  Dim sAddress As String
  Dim vValue As Variant

  If Not Intersect(Target, Target.Worksheet.Range("A3")) Is Nothing Then
    sAddress = Target.Address(False, False)
    vValue = Target.Value
    
    If bHaveSelectionChange = True Then
    
      MsgBox "The value of cell '" & sAddress & "' was changed MANUALLY " & _
             "from '" & vGblSheet1OldA3Value & "' to '" & vValue & "'.", , "Worksheet_Change()"
             
    Else
    
      MsgBox "The value of cell '" & sAddress & "' was changed by VBA " & _
             "from '" & vGblSheet1OldA3Value & "' to '" & vValue & "'.", , "Worksheet_Change()"
    
    End If
             
             
    bHaveSelectionChange = False
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'This saves the 'Old' value of cell 'A3' each time cell 'A3' becomes the 'Active Cell'.

  Dim sAddress As String
  
  If Not Intersect(Target, Target.Worksheet.Range("A3")) Is Nothing Then
    bHaveSelectionChange = True
    sAddress = Target.Address(False, False)
    vGblSheet1OldA3Value = Target.Value
    'MsgBox "The old value of cell '" & sAddress & "' is '" & vGblSheet1OldA3Value & "'.", , "Worksheet_SelectionChange()"
  End If
End Sub

Private Sub Worksheet_Calculate()
  'This identifies Changes to specific cells that are changed using formulas
  
  Dim myRange As Range
  Dim r As Range
  Dim vNewValue As Variant
  Dim vOldValue As Variant
  Dim i As Integer
  Dim iError As Long
  Dim sAddress As String
 
  'Define the range
  Set myRange = Range(sGblSheet1RANGE)
 
  'If a runtime error is generated because the old value array was not defined
  'then generate the old value array
  On Error Resume Next
  vOldValue = vGblSheet1CalculatedOldValues(1)
  iError = Err.Number
  If iError = 9 Then
    Call InitialzeSheet1SavedValuesForWorksheetCalculate
  End If
  On Error GoTo 0
  
  
  'Examine each value in the range
  For Each r In myRange
    i = i + 1
    vOldValue = vGblSheet1CalculatedOldValues(i)
    vNewValue = r.Value
    
    'If the value in a cell was changed:
    'a. Output a message
    'b. Save the 'new value' as the 'old value'
    If vNewValue <> vOldValue Then
      sAddress = r.Address(False, False)   'Get the address without the Dollar Signs '$'
    
      MsgBox "The value of cell '" & sAddress & "' was changed by FORMULA " & _
             "from '" & vOldValue & "' to '" & vNewValue & "'.", , "Worksheet_Calculate()"
      vGblSheet1CalculatedOldValues(i) = vNewValue
    End If
    
  Next r

 'Clear the range object
 Set myRange = Nothing
  
End Sub

How do I change this for a range of single cell? i.e. I want it to include the following cells:

"AC6", "AC9", "AC12", "AC15", AC18", "AC21", "AC24"

Any ideas?

darkblueblood,

Please do not cross post as it does nothing but cause confusion. See your original thread for a reply: http://www.excelforum.com/excel-prog...lculation.html

Lewis