Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Can you use a Range $N$xx:$Q$xx in Target.Address? | Excel Programming | |||
Using Target.Address in Excel 98 | Excel Discussion (Misc queries) | |||
target address | Excel Programming |