0

I have a Data Validation List in Cell D10. When the user selects a value, I would like to copy the value to a different cell on the same worksheet.

When I try

Range("G10").value= Range("D10").value

nothing is returned.

As D10 is a Data Validation List, I also tried

Range("G10").value=Range("D10").validation.value

(from an example code found online) but still nothing is returned.

Community
  • 1
  • 1
Peter Maw
  • 1
  • `Range("G10").value= Range("D10").value` is the right way to go. What is your validation like? Can you share more detail? – BigBen Apr 14 '20 at 16:02
  • Just in case, try .value2 instead of value. However, there may something wrong with other things you don't show us. – Mister Equis Apr 14 '20 at 16:08
  • 2
    How is your code being triggered? Is the sheet with the validation list active when the code runs? – Tim Williams Apr 14 '20 at 16:20
  • What is the `ActiveSheet` when the code runs? Have you tried [Fully Qualifying your Ranges](https://stackoverflow.com/a/22080453/9290986)? – Chronocidal Apr 14 '20 at 16:26

1 Answers1

1

This works for me:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$D$10" Then
        Range("G10").Value = Range("D10").Value
    End If

End Sub

It must be placed in the code of the sheet containing your cell with data validation. I guess you didn't used the $ to identify the cell.

Evil Blue Monkey
  • 2,276
  • 1
  • 7
  • 11
  • By the way if i did something wrong answering, please tell me. I am fairly new. ^^ Anyway: Target.Address does use $. If you omit them, it doesn't work. – Evil Blue Monkey Apr 14 '20 at 16:19
  • Well, he said "when a user select a value". Can there be any other way to catch the event? – Evil Blue Monkey Apr 14 '20 at 16:20
  • Thank you. I'll take a look. – Evil Blue Monkey Apr 14 '20 at 16:21
  • 1
    @EvilBlueMonkey `Target.Address` only uses "$" because [those are the Default Arguments for `Address`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.address) - you could use `Target.Address(False,False)` to get "D10" instead – Chronocidal Apr 14 '20 at 16:31