Populating dropdown list 2 with data depending upon what was selected in list 1

[karambos]

I'm programming VB in Excel. I've created a series of simple lists in a
worksheet. I would like to select one object from list 1 in a cell in
column A and then have the cursor cell move to the right neighbouring
cell and present me with a dropdown menu showing the contents of
another list dependant on what was chosen in the first cell. Here's the
code so far:


Code:
--------------------
Sub Dropdownlist()

Dim cell As Variant

If (Range("A40:A4000").Select) Then
cell = ActiveCell.Address

Select Case (ActiveCell.FormulaR1C1)

Case "1. Tooling"
Range("B40").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$1:$B$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Case "2. Project Organisation"
Range("B40").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$C$1:$C$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Select

End If

End Sub
--------------------


The problem is - how do I get the variable "cell" to move to the right
one cell? If I add "+1" I get the value $A$401 instead of $A$41 as I
would like

Many thanks for any help.

 

[GJones]

Hi karambos;


Use this idea

MyVariable = "$A$40"

Range(MyVariable).Offset(0, 1).Select


Thanks,

Greg

-----Original Message-----

I'm programming VB in Excel. I've created a series of simple lists in a
worksheet. I would like to select one object from list 1 in a cell in
column A and then have the cursor cell move to the right neighbouring
cell and present me with a dropdown menu showing the contents of
another list dependant on what was chosen in the first cell. Here's the
code so far:


Code:
--------------------
Sub Dropdownlist()

Dim cell As Variant

If (Range("A40:A4000").Select) Then
cell = ActiveCell.Address

Select Case (ActiveCell.FormulaR1C1)

Case "1. Tooling"
Range("B40").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,

AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=$B$1:$B$11"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Case "2. Project Organisation"
Range("B40").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList,

AlertStyle:=xlValidAlertStop, Operator:= _

 

[Dick Kusleika]

karambos

See here for one method

http://www.dicks-blog.com/excel/2004/05/one_combo_based.html