selection VBA

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

I cannot find any technique to trigger an event when activating the next cell within a selected range by tabbing. Is it possible?

Reply | Quote

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!

Reply | Quote

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!

Thank you Hans

Reply | Quote

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

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

-Brett

Reply | Quote

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.

THANX, works perfectly.

Reply | Quote