Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default Snap an object to a cell border via macro

Try:

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

Greg
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

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

"Greg Wilson" wrote:

Try:

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

Greg

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom


(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


"Greg Wilson" wrote:

Try:

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

Greg

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default Snap an object to a cell border via macro

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom


(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


"Greg Wilson" wrote:

Try:

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

Greg



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

Ok thanks, I'll research those properties.

"Greg Wilson" wrote:

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom


(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


"Greg Wilson" wrote:

Try:

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

Greg

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Snap an object to a cell border via macro

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


Brettjg wrote:

Ok thanks, I'll research those properties.

"Greg Wilson" wrote:

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom

(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


"Greg Wilson" wrote:

Try:

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

Greg


--

Dave Peterson
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

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

"Dave Peterson" wrote:

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


Brettjg wrote:

Ok thanks, I'll research those properties.

"Greg Wilson" wrote:

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom

(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


"Greg Wilson" wrote:

Try:

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

Greg


--

Dave Peterson

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Snap an object to a cell border via macro

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/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....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_tut...e_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_gr...Excel*&num=100

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

Brettjg wrote:

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

"Dave Peterson" wrote:

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


Brettjg wrote:

Ok thanks, I'll research those properties.

"Greg Wilson" wrote:

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom

(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


"Greg Wilson" wrote:

Try:

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

Greg


--

Dave Peterson


--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 295
Default Snap an object to a cell border via macro

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

"Dave Peterson" wrote:

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/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....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_tut...e_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_gr...Excel*&num=100

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

Brettjg wrote:

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

"Dave Peterson" wrote:

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


Brettjg wrote:

Ok thanks, I'll research those properties.

"Greg Wilson" wrote:

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



"Brettjg" wrote:

Hi Greg, I was just fooling around with that and tried

.right = Range("A1").right
.bottom = Range("A1").bottom

(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


"Greg Wilson" wrote:

Try:

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

Greg

--

Dave Peterson


--

Dave Peterson

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
Place a Bottom Border in a Table via a Macro [email protected] Excel Discussion (Misc queries) 0 February 26th 07 12:19 AM
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error [email protected] Excel Discussion (Misc queries) 4 September 25th 06 01:35 PM
Macro linked to text or an object areaume Excel Discussion (Misc queries) 1 May 15th 06 08:15 PM
Very interesting problem that should be a snap to figure out! KenRamoska Excel Discussion (Misc queries) 3 March 15th 06 09:38 PM
Changing the border of one cell s/n change the border of adjacent gjanssenmn Excel Discussion (Misc queries) 2 October 5th 05 08:35 PM


All times are GMT +1. The time now is 08:32 PM.

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

About Us

"It's about Microsoft Excel"