I have made a dropdonwbox in a cell by defining a validation list to the
cell.
Is it possible to catch the event when the user select an item in the
dropdownbox? I want to fill up other cells in the same row when it happens.

I figure out that I can use a formula in the other cells but I do need a
macro as the user may accidentally delete the contents of these cells and
the formula will also be deleted.
So I need to capture this event handler and then call a
function/procedure/macro to do a look up and fetch the values and fill in
these other cells.

"Man Utd" wrote in message
...
I have made a dropdonwbox in a cell by defining a validation list to the
cell.
Is it possible to catch the event when the user select an item in the
dropdownbox? I want to fill up other cells in the same row when it
happens.

the change_event of the worksheet will be triggered
when you change / select in data validation dropdown


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Man Utd wrote :

I figure out that I can use a formula in the other cells but I do
need a macro as the user may accidentally delete the contents of
these cells and the formula will also be deleted.
So I need to capture this event handler and then call a
function/procedure/macro to do a look up and fetch the values and
fill in these other cells.

"Man Utd" wrote in message
...
I have made a dropdonwbox in a cell by defining a validation list
to the cell.
Is it possible to catch the event when the user select an item in
the dropdownbox? I want to fill up other cells in the same row when
it happens.

You can't capture the event of clicking the item in the dropdown box per se
(except perhaps with complex API code) but, for most versions of Excel past
xl97, you can capture the worksheet change event that is triggered when a
selection is made. You need to disable and reenable events in your code else
the act of populating the cells will cause a loop. Code example:

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.EnableEvents = False
With Target
Cells(.Row, Columns.Count). _
End(xlToLeft)(1, 2) = .Value
End With
.EnableEvents = True
End With
End Sub

Regards,
Greg

"Man Utd" wrote:

I have made a dropdonwbox in a cell by defining a validation list to the
cell.
Is it possible to catch the event when the user select an item in the
dropdownbox? I want to fill up other cells in the same row when it happens.

My code example should restrict the cell to the one with the dropdown box.
Assuming cell C2:
Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.EnableEvents = False
With Target
If .Address = "$C$2" Then _
Cells(.Row, Columns.Count). _
End(xlToLeft)(1, 2) = .Value
End With
.EnableEvents = True
End With
End Sub

"Man Utd" wrote:

I have made a dropdonwbox in a cell by defining a validation list to the
cell.
Is it possible to catch the event when the user select an item in the
dropdownbox? I want to fill up other cells in the same row when it happens.

'To incrementally populate the cells to the right with the selections:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = ActiveCell
If Not Target.Address = c.Address Then Exit Sub
Application.EnableEvents = False
Cells(c.Row, Columns.Count).End(xlToLeft)(1, 2) = c.Value
Application.EnableEvents = True
End Sub

'To change all cells to the right with the current selection:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set c = ActiveCell
If Not Target.Address = c.Address Then Exit Sub
Application.EnableEvents = False
Set rng = Range(c(1, 2), Cells(c.Row, Columns.Count))
rng.Value = c.Value
Application.EnableEvents = True
End Sub

Regards,
Greg

"Man Utd" wrote:

I figure out that I can use a formula in the other cells but I do need a
macro as the user may accidentally delete the contents of these cells and
the formula will also be deleted.
So I need to capture this event handler and then call a
function/procedure/macro to do a look up and fetch the values and fill in
these other cells.

"Man Utd" wrote in message
...
I have made a dropdonwbox in a cell by defining a validation list to the
cell.
Is it possible to catch the event when the user select an item in the
dropdownbox? I want to fill up other cells in the same row when it
happens.