Pass column & line co-ordinates to Excel

[peterd51]

Hi,

I have a macro and currently it works on fixed lines and columns...

what I would like to do is set it up so a user could highlight the block of lines and columns desired and then run the macro on that block.

Can't seem to find the right search words to see anything previously asked. Any ideas please?

If it's not possoble then I can set it up where the user types the co-ordiates in a specific location and the macro can just read the numbers, but it's not as easy for the user then...

Regards
Peter

 

[HarleyQuinn]

How about having a look for Application.Selection to get you started.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Bong]

In Excel, "lines" are "rows". A block of rows and columns is a "range". When a user highlights a range, that range is "selected". The selection designation is its "address":

print selection.address
$D$11:$I$18

If you want the macro to operate on the range the user selected (presumably before hitting a button or something), use "selection". BTW, I think it's a bad idea to depend on user selection. I'd prefer to use a dialog but that's just me.

_________________
Bob Rashkin

 

[SkipVought]

a user could highlight the block of lines and columns desired and then run the macro on that block.

sub FormatSelection()
With Selection
 'now reference all the objects to the selection range
 .font.bold=true
 .HorizontalAlignment = xlcenter
 'etc
End With
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[peterd51]

Hi,

sorry for teh delayed response, it got a bit hectic since I posted the question.

Thanks for the info, it works!

I understand the concern letting users loose but they'll mess it up no matter what I do. As I keep the master copy and they work on copies I can always restore it.

Regards
Peter