Select case target.address
 

For the cells I13:I18, in a SelectionChange event macro, what would the select case syntax look like for...

If I13 is selected on the sheet then the Named Range ABC is selected (highlighted)
It I14 is selected on the sheet then select the Named Range DEF is selected (highlighted).

....and so on for I13 to I18, a different named range selected for each cell in that range.

The purpose is to select the named range on the sheet to enter data in a certain sequence of cells, which the named range will provide when selected. So the user can enter data and tab through the named range using Enter Data/Enter or Enter Data/Tab to sequence through a particular range.

Thanks

Select case target.address
 

Hi Howard,

Am Sat, 26 Aug 2017 16:52:37 -0700 (PDT) schrieb L. Howard:

For the cells I13:I18, in a SelectionChange event macro, what would the select case syntax look like for...

If I13 is selected on the sheet then the Named Range ABC is selected (highlighted)
It I14 is selected on the sheet then select the Named Range DEF is selected (highlighted).

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("I13:I18")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Select Case Target.Address(0, 0)
Case "I13"
Application.Goto Range("ABC")
Case "I14"
Application.Goto Sheets("Sheet1").Range("DEF")
Case "I15"
Application.Goto Sheets("Sheet1").Range("GHI")

Select case target.address
 

Select Case Target.Address
Case "I13": Range("ABC").Select
Case "I14": Range("DEF").Select
'and so on...
End Select 'Case Target.Address

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select case target.address
 

On Sunday, August 27, 2017 at 2:18:57 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Sat, 26 Aug 2017 16:52:37 -0700 (PDT) schrieb L. Howard:

For the cells I13:I18, in a SelectionChange event macro, what would the select case syntax look like for...

If I13 is selected on the sheet then the Named Range ABC is selected (highlighted)
It I14 is selected on the sheet then select the Named Range DEF is selected (highlighted).

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("I13:I18")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Select Case Target.Address(0, 0)
Case "I13"
Application.Goto Range("ABC")
Case "I14"
Application.Goto Sheets("Sheet1").Range("DEF")
Case "I15"
Application.Goto Sheets("Sheet1").Range("GHI")
.
.
.

End Select
End Sub

or:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("I13:I18")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim varRng As Variant
Dim i As Integer

varRng = Array("ABC", "DEF", "GHI")

Select Case Target.Address(0, 0)
Case "I13"
i = 0
Case "I14"
i = 1
Case "I15"
i = 2
.
.
.
End Select
Application.Goto Sheets("Sheet1").Range(varRng(i))
End Sub


Regards
Claus B.

Hi Claus,

Both codes work great.

Thanks, Howard

Select case target.address
 

On Sunday, August 27, 2017 at 2:21:30 AM UTC-7, GS wrote:
Select Case Target.Address
Case "I13": Range("ABC").Select
Case "I14": Range("DEF").Select
'and so on...
End Select 'Case Target.Address

--
Garry


Hi Garry,

Had to add the (False, False) to Target.Address(0, 0), works great, pretty compact code also.

Thanks, Howard