Data Validation

[Susanna via OfficeKB.com]

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

[Bob Phillips]

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" <forum@OfficeKB.com> wrote in message
news:52B035F22DFFC@OfficeKB.com...
> 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 via OfficeKB.com]

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

[Bob Phillips]

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" <forum@OfficeKB.com> wrote in message
news:52B1DE7840320@OfficeKB.com...
> 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

[Susanna via OfficeKB.com]

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

[Bob Phillips]

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" <forum@OfficeKB.com> wrote in message
news:52BBBAA4126A8@OfficeKB.com...
> 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

[Susanna via OfficeKB.com]

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

[Bob Phillips]

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" <forum@OfficeKB.com> wrote in message
news:52BDAE4C0896C@OfficeKB.com...
> 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

[Susanna via OfficeKB.com]

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