Right most column of dragged range

[Brian.Crawford]

I need to be able to determine the right most column and the bottom most row of a user dragged range. My user is dragging a value beyond the edge of the accepted range and I want to be able to trap that activity and shorten the drag to only within the accepted range.
Suggestions
Thanks
Brian

 

[repairman615]

Hello,

Try like:

MsgBox Selection.Rows.Count & " Rows selected"
MsgBox Selection.Columns.Count & " Columns selected"
MsgBox Selection.Row
MsgBox Selection.Column

You could do subtraction or other operation to determine location.

 

[Brian.Crawford]

Thanks. I'll try working with that.
.
In retrospect what would be nice woud be to be able trap and stop the drag before it goes beyond the accepted range as data is already in those cells that should not be overwritten.
I'm not keen to lock that column as the user should be allowed to type a value into that single column, but not by dragging other cells into it.
Brian

 

[repairman615]

Yes, However you can adapt the selection using the .Resize.

Here is a snip that my help you. The range to select in this case is D4:G7. Four rows and four columns. As long as the selection is started within this range, D4:G7 will be ultimatly selected.

Start a new workbook and paste in the worksheet module to test and maybe get some ideas.






<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> changed = Intersect(Target, Range("D4:G7"))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("D4").Resize(4, 4).Select<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>