I have a cell (A3) that uses Data Validation.

I've managed to create a list for the Data Validation with the values:
Project A
Project B
Project C

If I add or change an entry in the list, it gets updated in the dropdown.
However, if in cell A3, I had previously selected 'Project A', and then later
changed 'Project A' in the list to 'Project AAA', the value in cell A3
remains as 'Project A' . But I want the value in cell A3 to be automatically
changed to 'Project AAA'.

A similar problem had been reported in the discussion thread titled 'Data
Validation/Drop down list automatic update'. Tom Ogilvy responded by saying
a macro using a change event is required. Would you be able to provide me
with the code for this macro?

Thanks

Susanna,

Try this


Private oldValue As String
Private Const DVCell As String = "M1"
Private Const dataList As String = "H1:H10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("dataList")) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Susanna via OfficeKB.com" wrote in message
...
I have a cell (A3) that uses Data Validation.

I've managed to create a list for the Data Validation with the values:
Project A
Project B
Project C

If I add or change an entry in the list, it gets updated in the dropdown.
However, if in cell A3, I had previously selected 'Project A', and then
later
changed 'Project A' in the list to 'Project AAA', the value in cell A3
remains as 'Project A' . But I want the value in cell A3 to be
automatically
changed to 'Project AAA'.

A similar problem had been reported in the discussion thread titled 'Data
Validation/Drop down list automatic update'. Tom Ogilvy responded by
saying
a macro using a change event is required. Would you be able to provide me
with the code for this macro?

Thanks

Hi Bob,

It didn't work. It did not replace the old value in the cell with the new
updated value in the Data Validation list.

Susanna

Bob Phillips wrote:
Susanna,

Try this

Private oldValue As String
Private Const DVCell As String = "M1"
Private Const dataList As String = "H1:H10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("dataList")) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

I have a cell (A3) that uses Data Validation.

[quoted text clipped - 15 lines]

Thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

It did in my test.

I created two constants to allow easy adaptation to your cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Susanna via OfficeKB.com" wrote in message
...
Hi Bob,

It didn't work. It did not replace the old value in the cell with the new
updated value in the Data Validation list.

Susanna

Bob Phillips wrote:
Susanna,

Try this

Private oldValue As String
Private Const DVCell As String = "M1"
Private Const dataList As String = "H1:H10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("dataList")) Is Nothing Then
With Target
If Range(DVCell).Value = oldValue Then
Range(DVCell).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

I have a cell (A3) that uses Data Validation.

[quoted text clipped - 15 lines]

Thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

Hi Bob,

The problem is not getting the change to be reflected in the dropdown. The
problem is replacing the contents of what was previously in the cell.

For example, if I had selected Project A from the list, the cell now contains
Project A.
Then I change Project A to Project AAA on the data validation list. Yes, I
will get Project AAA on the dropdown but the contents of the cell still
remains as Project A. What I want done is as soon as I make the change in
the data validation list, the contents of my cell gets updated automatically
to Project AAA.


Bob Phillips wrote:
It did in my test.

I created two constants to allow easy adaptation to your cells.

Hi Bob,

[quoted text clipped - 45 lines]

Thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

Lost me Susanna.

If you change the Project A to Project AAA on the data validation list, and
the dropdown cell is changed, what cell is it that remains unchanged?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Susanna via OfficeKB.com" wrote in message
...
Hi Bob,

The problem is not getting the change to be reflected in the dropdown.
The
problem is replacing the contents of what was previously in the cell.

For example, if I had selected Project A from the list, the cell now
contains
Project A.
Then I change Project A to Project AAA on the data validation list. Yes,
I
will get Project AAA on the dropdown but the contents of the cell still
remains as Project A. What I want done is as soon as I make the change in
the data validation list, the contents of my cell gets updated
automatically
to Project AAA.


Bob Phillips wrote:
It did in my test.

I created two constants to allow easy adaptation to your cells.

Hi Bob,

[quoted text clipped - 45 lines]

Thanks


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1