Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mjack003
 
Posts: n/a
Default Worksheet SelectionChange Event


Hi,

I have this code here for a selectionchange event on one of my
worksheets.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(Cells(Target.Row, 4), _
Cells(Target.Row + Target.Rows.Count - 1, 30)).Select
End Sub

This works great as long as the range I want is contiguous. How would
I go about changing this code to only keep selected rows if the user
holds the CTRL key and selects random rows? I don't know if this is
even possible but I've played with the intersect method and this
doesn't seem to be a solution.
Any input is appreciated.

Thanks,
Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=539927

  #2   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default Worksheet SelectionChange Event

Try following code.

BTW: you forgot to disable events while you're changing selection in your
code!

HTH
--
AP

'----------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rArea As Range
Dim r1 As Range
Dim rSelect As Range
Application.EnableEvents = False
For Each rArea In Target.Areas
Set r1 = Range( _
Cells(rArea.Row, "D"), _
Cells(rArea.Row + rArea.Rows.Count - 1, "AD") _
)
If rSelect Is Nothing Then
Set rSelect = r1
Else
Set rSelect = Union(rSelect, r1)
End If
Next rArea
rSelect.Select
Application.EnableEvents = True
End Sub
'----------------------------------
"mjack003" a écrit
dans le message de news:
...

Hi,

I have this code here for a selectionchange event on one of my
worksheets.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(Cells(Target.Row, 4), _
Cells(Target.Row + Target.Rows.Count - 1, 30)).Select
End Sub

This works great as long as the range I want is contiguous. How would
I go about changing this code to only keep selected rows if the user
holds the CTRL key and selects random rows? I don't know if this is
even possible but I've played with the intersect method and this
doesn't seem to be a solution.
Any input is appreciated.

Thanks,
Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile:
http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=539927



  #3   Report Post  
Posted to microsoft.public.excel.misc
mjack003
 
Posts: n/a
Default Worksheet SelectionChange Event


Thanks Ardus. Didn't even think about that...ingenious! :) One problem
though...the user is selecting from a very long list. Haven't really
put any thought into this yet but is there anyway to keep the screen
from centering to the intial selection...which distracts the user and
causes them to lose how far down the list they had scrolled. Thank you
for the quick response.

Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=539927

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I reference data from one worksheet to another using combob caricc New Users to Excel 0 December 17th 05 10:58 PM
One worksheet to calulate different items simultaneously John Excel Worksheet Functions 4 November 28th 05 08:07 PM
Search a worksheet, extract rows using a list from another sheet bobf Excel Discussion (Misc queries) 9 August 31st 05 05:56 AM
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 05:21 PM
Macro to search for and display data in another worksheet Mark H Excel Worksheet Functions 0 June 14th 05 01:40 PM


All times are GMT +1. The time now is 07:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"