Snap an object to a cell border via macro

B

Brettjg

Hello there

I want to move a text box to the corner of a cell (say "Textbox 1" to the
corner of Cell A1) by using a macro. I can't find anything in help or the
threads in Discussion forums. I tried recording a macro and turning on the
"snap to grid" tool as the first step, but it didn't generate any code. Does
anyone have a solution please?

Regards, Brett
 
G

Greg Wilson

Try:

With ActiveSheet.Shapes("Text Box 1")
.Left = Range("A1").Left
.Top = Range("A1").Top
End With

Greg
 
B

Brettjg

Absolute corker! Thanks Greg - you don't know how much that will change my
life!
Regards, Brett
 
B

Brettjg

Hi Greg, I was just fooling around with that and tried
.right = Range("A1").right
.bottom = Range("A1").bottom
Click to expand...

(pretty obvious, really) and it debugs. When I typed the right in lower case
it auto-corrected it, so I thought that it might work, but it doesn't like
Right or Bottom it seems. Any clues?
Brett
 
G

Greg Wilson

Right and Bottom are properties that just aren't supported by the VBA Shapes
collection. Generally, Right and Bottom are achieved by adding the Width
property to the Left property and the Height property to the Top property
respectively.

Greg
 
B

Brettjg

Ok thanks, I'll research those properties.

Greg Wilson said:
Right and Bottom are properties that just aren't supported by the VBA Shapes
collection. Generally, Right and Bottom are achieved by adding the Width
property to the Left property and the Height property to the Top property
respectively.

Greg
Click to expand...
 
D

Dave Peterson

Just in case you have trouble:

With ActiveSheet.Shapes("Text Box 1")
.Left = .Parent.Range("A1").Left
.Top = .Parent.Range("A1").Top
.Width = .Parent.Range("a1").Width
.Height = .Parent.Range("a1").Height
End With

I like to qualify my ranges, so I can use:

With worksheets("sheet9999").Shapes("Text Box 1")
.Left = .Parent.Range("A1").Left
.Top = .Parent.Range("A1").Top
.Width = .Parent.Range("a1").Width
.Height = .Parent.Range("a1").Height
End With
 
B

Brettjg

Hi Dave Thankyou for that, it gave me the key to unlock the door!. Sorry
about delayed response but I can't see any of the content of the answers on
my computer now when I go to this site. I can only see that I have posted a
question and that there is an answer. Maybe you have an idea on that?
Microsoft hasn't responded to my email either. Thanks again, Brett
 
D

Dave Peterson

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm
 
B

Brettjg

Hey Dave, thankyou so much, I'll check all that out (have to email this back
to my computer - bloody ridiculous really). Thanks and regards, Brett
 
Top