Cell equal value new value of active cell which is a drop down

[hassanmoukad]

I have a cell which i declared as active cell using developers tool in excel
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("C1").Value = ActiveCell.Value
End Sub
So whatever cell i click on, cell C1 will show that Value.
What i want to do it
Cell C1 is working correctly when i click acell, the C1 gets the value of that cell. But my issue is that, i have created drop down from Data Validation in specific cells. At first, when i click that cell , assume B1, C1 get the correct value of that cell, but when i select a new value from B1's drop down, C1 dose not change , it keeps that initial value. Is there a ways to make C1, change when i select a new value from B1's Drop Down?

[paulked]

The code is triggered by a Selection change, which is why it won't detect the change in the same cell (ie Data Validation).

The code below will work, but only for the first DV change:



Option Explicit


Private Once As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
Dim dv As Range
If Once = True Then Exit Sub
Set dv = Target
Once = True
Range("C1") = dv.Value
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("C1").Value = ActiveCell.Value
Once = False
End Sub


There will be a way to do this multiple times for the same cell, but my time has to be spent elsewhere this morning, hope this little bit gets you started though!

[paulked]

Maybe I was over-thinking this, try...



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("C1") = Target
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("C1").Value = ActiveCell.Value
End Sub