Hi,

I got this code from this forum (Provided by Ron Coderre) to get Data
Validation list when selected show a msg box and move to the next DV list.
But the code work on contagious DV list i.e. A2, B2 and C2 etc. But I am
curious to know what modification is to be made in the Code if the DV list
are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7.

Any suggestion will be appreciated,

Rishi


--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------

When the dependent DV lists are not in CONTIGUOUS cells, your scenario is
messier to code, since there is no consistent relative cell relationship.
You'll most likely need an IF...ELSEIF...ELSEIF...ELSE....ENDIF construct
where you check the Target against the address of each of the DV cells. If
the Target matches a DV cell, then you'll need to clear the specifically
referenced next DV and prompt for input.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Hi,

I got this code from this forum (Provided by Ron Coderre) to get Data
Validation list when selected show a msg box and move to the next DV list.
But the code work on contagious DV list i.e. A2, B2 and C2 etc. But I am
curious to know what modification is to be made in the Code if the DV list
are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7.

Any suggestion will be appreciated,

Rishi


--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------

Hi

i'm really new to VBA and don't have any programming exp. so can you tell me
what code i should write if my DV are following

in Cell C6 i have a DV to select 'State Name', when any State from the list
selected i need a Msg box to appear with "Select District Name from List"
then

in Cell V6 data cleared and cell selected, And when any District is selected
from this DV list another Msg Box appear with "Select Block Name" then

in Cell X8 Data cleared and Cell Selected and person entering data can
select any name from this cell DV list.

This is all, i think i'm able to explain my problem, i need your help in
doing this.

Regards

Rishi

"Ron Coderre" wrote:

When the dependent DV lists are not in CONTIGUOUS cells, your scenario is
messier to code, since there is no consistent relative cell relationship.
You'll most likely need an IF...ELSEIF...ELSEIF...ELSE....ENDIF construct
where you check the Target against the address of each of the DV cells. If
the Target matches a DV cell, then you'll need to clear the specifically
referenced next DV and prompt for input.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Hi,

I got this code from this forum (Provided by Ron Coderre) to get Data
Validation list when selected show a msg box and move to the next DV list.
But the code work on contagious DV list i.e. A2, B2 and C2 etc. But I am
curious to know what modification is to be made in the Code if the DV list
are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7.

Any suggestion will be appreciated,

Rishi


--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------

Regarding:
i'm really new to VBA and don't have any programming exp. so can you tell me
what code i should write if my DV are following

Here it is

'--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is the first Data Validation cell
If Not Intersect(Target, Range("C6")) Is Nothing Then
'Select the next DV cell
Range("V6").Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select a DISTRICT the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

'Check if the active cell is the second Data Validation cell
ElseIf Not Intersect(Target, Range("V6")) Is Nothing Then
'Select the next DV cell
Range("X8").Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select a BLOCK the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")
End If

ErrTrap:
Application.EnableEvents = True
End Sub
'--------end of code----------

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Hi

i'm really new to VBA and don't have any programming exp. so can you tell me
what code i should write if my DV are following

in Cell C6 i have a DV to select 'State Name', when any State from the list
selected i need a Msg box to appear with "Select District Name from List"
then

in Cell V6 data cleared and cell selected, And when any District is selected
from this DV list another Msg Box appear with "Select Block Name" then

in Cell X8 Data cleared and Cell Selected and person entering data can
select any name from this cell DV list.

This is all, i think i'm able to explain my problem, i need your help in
doing this.

Regards

Rishi

"Ron Coderre" wrote:

When the dependent DV lists are not in CONTIGUOUS cells, your scenario is
messier to code, since there is no consistent relative cell relationship.
You'll most likely need an IF...ELSEIF...ELSEIF...ELSE....ENDIF construct
where you check the Target against the address of each of the DV cells. If
the Target matches a DV cell, then you'll need to clear the specifically
referenced next DV and prompt for input.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Hi,

I got this code from this forum (Provided by Ron Coderre) to get Data
Validation list when selected show a msg box and move to the next DV list.
But the code work on contagious DV list i.e. A2, B2 and C2 etc. But I am
curious to know what modification is to be made in the Code if the DV list
are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7.

Any suggestion will be appreciated,

Rishi


--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------

Dear Ron

i must say you are a Genious, and Thanks a lot for the Code you have given.

But one thing i have noticed that if the DV is in a Marged cell ( C6:D6 )
then the code didn't worked, (i've changed the code Range as C6:D6 also)

is it a programme difault or i'm doing any mistake.

once again thank you for the help


Regards

Rishi

When the DV list is in a merged cell.....you shouldn't need to change the
code as long as it refers to the upper left cell of the merged range. In
your case, it would reference C6.

***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Dear Ron

i must say you are a Genious, and Thanks a lot for the Code you have given.

But one thing i have noticed that if the DV is in a Marged cell ( C6:D6 )
then the code didn't worked, (i've changed the code Range as C6:D6 also)

is it a programme difault or i'm doing any mistake.

once again thank you for the help


Regards

Rishi

Your only mistake is using "merged cells" feature.

Merged cells cause no end of problems with copying, pasting, sorting, filtering
and a host of other functions and should be avoided at all cost.


Gord Dibben MS Excel MVP


On Sat, 9 Dec 2006 04:09:00 -0800, Rishi
wrote:

Dear Ron

i must say you are a Genious, and Thanks a lot for the Code you have given.

But one thing i have noticed that if the DV is in a Marged cell ( C6:D6 )
then the code didn't worked, (i've changed the code Range as C6:D6 also)

is it a programme difault or i'm doing any mistake.

once again thank you for the help


Regards

Rishi

Succinctly put, Gord....
I abandoned writing my comments on merged cells when I was starting Chapter
3 of my intended post. <vbg

***********
Regards,
Ron

XL2002, WinXP


"Gord Dibben" wrote:

Your only mistake is using "merged cells" feature.

Merged cells cause no end of problems with copying, pasting, sorting, filtering
and a host of other functions and should be avoided at all cost.


Gord Dibben MS Excel MVP


On Sat, 9 Dec 2006 04:09:00 -0800, Rishi
wrote:

Dear Ron

i must say you are a Genious, and Thanks a lot for the Code you have given.

But one thing i have noticed that if the DV is in a Marged cell ( C6:D6 )
then the code didn't worked, (i've changed the code Range as C6:D6 also)

is it a programme difault or i'm doing any mistake.

once again thank you for the help


Regards

Rishi

Merged cells are just a pet peeve of mine.

I should shut up about it but some users think the feature is the best thing
since sliced bread and populate their sheets with them.

Then post here wondering why they can't do this or that and are getting error
messages.

I have not tried 2007 yet but maybe some improvement has been made?


Gord

On Sat, 9 Dec 2006 11:46:01 -0800, Ron Coderre
wrote:

Succinctly put, Gord....
I abandoned writing my comments on merged cells when I was starting Chapter
3 of my intended post. <vbg

***********
Regards,
Ron

XL2002, WinXP


"Gord Dibben" wrote:

Your only mistake is using "merged cells" feature.

Merged cells cause no end of problems with copying, pasting, sorting, filtering
and a host of other functions and should be avoided at all cost.


Gord Dibben MS Excel MVP


On Sat, 9 Dec 2006 04:09:00 -0800, Rishi
wrote:

Dear Ron

i must say you are a Genious, and Thanks a lot for the Code you have given.

But one thing i have noticed that if the DV is in a Marged cell ( C6:D6 )
then the code didn't worked, (i've changed the code Range as C6:D6 also)

is it a programme difault or i'm doing any mistake.

once again thank you for the help


Regards

Rishi