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.


--
karambos
------------------------------------------------------------------------
karambos's Profile: http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=276632

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:= _
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.


--
karambos
----------------------------------------------------------
--------------
karambos's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=16262
View this thread:
http://www.excelforum.com/showthread...hreadid=276632

.

karambos

See here for one method

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"karambos" wrote in 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:= _
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.


--
karambos
------------------------------------------------------------------------
karambos's Profile:
http://www.excelforum.com/member.php...o&userid=16262
View this thread: http://www.excelforum.com/showthread...hreadid=276632