Results 1 to 9 of 9

Thread: selection VBA

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I'm trying to setup a cell with the contents of whatever is in the "A" column of the row with any cell selected in that row.

    ie: cell D3 would return the contents of A34 for any cell selected in row 34.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Right-click the sheet tab and enter or copy the following code:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Range("D3") = Range("A" & ActiveCell.Row)
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    [quote name='Zeno' post='769743' date='08-Apr-2009 11:12']I'm trying to setup a cell with the contents of whatever is in the "A" column of the row with any cell selected in that row.

    ie: cell D3 would return the contents of A34 for any cell selected in row 34.[/quote]

    THANX, works perfectly.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769746' date='08-Apr-2009 11:21']Right-click the sheet tab and enter or copy the following code:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Range("D3") = Range("A" & ActiveCell.Row)
    End Sub
    [/quote]

    Hi. How could this code be modified to make it so I can apply this concept to a range of cells?

    For example, if I have cells B3:H14 that I want to apply this code to so that if I select cell B5 it gives me the value in A5, if I select cell D13 it gives me the value in A13, etc.

    And to complicate it, only one cell (in the range B3:H14) in any of the columns should have a value in it. So if I select B3 it has a value equal to A3, but then if I select B6 it has a value equal to A6 and B3 now has no value.

    Any help is greatly appreciated.
    Thanks,
    Brett

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this do what you want?

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Range("B3:H14"), ActiveCell) Is Nothing Then
    	Range(Cells(3, ActiveCell.Column), Cells(14, ActiveCell.Column)).ClearContents
    	ActiveCell = Cells(ActiveCell.Row, 1)
      End If
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='778526' date='05-Jun-2009 16:14']Does this do what you want?

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    	 If Not Intersect(Range("B3:H14"), ActiveCell) Is Nothing Then
    	   Range(Cells(3, ActiveCell.Column), Cells(14, ActiveCell.Column)).ClearContents
    	   ActiveCell = Cells(ActiveCell.Row, 1)
    	 End If
       End Sub
    [/quote]
    I cannot find any technique to trigger an event when activating the next cell within a selected range by tabbing. Is it possible?
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There is no event for that, unfortunately. You could trap the Tab key using

    Application.OnKey "{TAB}", "YourTabHandler"

    but then the YourTabHandler macro should be able to handle the use of the tab key in all possible circumstances correctly - not an easy feat!

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='778607' date='06-Jun-2009 09:22']There is no event for that, unfortunately. You could trap the Tab key using

    Application.OnKey "{TAB}", YourTabHandler"

    but then the YourTabHandler macro should be able to handle the use of the tab key in all possible circumstances correctly - not an easy feat![/quote]
    Thank you Hans
    Regards
    Don

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='778526' date='05-Jun-2009 15:14']Does this do what you want?

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Range("B3:H14"), ActiveCell) Is Nothing Then
    	Range(Cells(3, ActiveCell.Column), Cells(14, ActiveCell.Column)).ClearContents
    	ActiveCell = Cells(ActiveCell.Row, 1)
      End If
    End Sub
    [/quote]

    That does exactly what I want. Thanks so much!!!

    -Brett

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •