Data Validation
 

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

Data Validation
 

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

Data Validation
 

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

Data Validation
 

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

Data Validation
 

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

Data Validation
 

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

Data Validation
 

Hi Bob,

I have defined cell A1 as an editable field that uses a data validation list
for its dropdown.

The range for the data validation can be found in B1:B3 with the values:
B1 Project A
B2 Project B
B3 Project C

In cell A1, I select 'Project A' from the dropdown list. Cell A1 now has a
value of 'Project A'.

If I change the value of B1 to 'Project AAA', I would like to see the value
of cell A1 automatically changed to 'Project AAA', but it doesn't; the value
of cell A1 remains to be the original 'Project A'.

Bob Phillips wrote:
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?

Hi Bob,

[quoted text clipped - 18 lines]

Thanks


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

Data Validation
 

Morning Susanna,

I have just tried it again, using the cell addresses that you used, and it
is working fine as I understand it.

I have changed the code to your data, added extra error-handling, so please
replace all the code with this and try again. Note where it gets installed.

Option Explicit

Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Count = 1 Then
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
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
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
...
Hi Bob,

I have defined cell A1 as an editable field that uses a data validation
list
for its dropdown.

The range for the data validation can be found in B1:B3 with the values:
B1 Project A
B2 Project B
B3 Project C

In cell A1, I select 'Project A' from the dropdown list. Cell A1 now has
a
value of 'Project A'.

If I change the value of B1 to 'Project AAA', I would like to see the
value
of cell A1 automatically changed to 'Project AAA', but it doesn't; the
value
of cell A1 remains to be the original 'Project A'.

Bob Phillips wrote:
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?

Hi Bob,

[quoted text clipped - 18 lines]

Thanks


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

Data Validation
 

Thanks Bob. Now it works.

But let me add another complication to this.

The source list for the Data Validation is in another workbook (file); i.e.,
B1:B10 (the dataList) is actually referencing C1:C10 in another workbook. I
would like the source list to be a dynamic range, such that if I add or
change anything in the source list, it gets reflected in cell A1 and its
dropdown list, but I want the dropdown to also omit the lines that are blanks.
How can I successfully do this?

Bob Phillips wrote:
Morning Susanna,

I have just tried it again, using the cell addresses that you used, and it
is working fine as I understand it.

I have changed the code to your data, added extra error-handling, so please
replace all the code with this and try again. Note where it gets installed.

Option Explicit

Private oldValue As String
Private Const DVCell As String = "A1"
Private Const dataList As String = "B1:B10"

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Count = 1 Then
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
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range(dataList)) Is Nothing Then
If Not IsEmpty(Target) Then
oldValue = Target.Value
End If
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.

Hi Bob,

[quoted text clipped - 23 lines]

Thanks


--
Message posted via http://www.officekb.com