| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Place a Bottom Border in a Table via a Macro | Excel Discussion (Misc queries) | |||
| Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
| Macro linked to text or an object | Excel Discussion (Misc queries) | |||
| Very interesting problem that should be a snap to figure out! | Excel Discussion (Misc queries) | |||
| Changing the border of one cell s/n change the border of adjacent | Excel Discussion (Misc queries) | |||
Linear Mode
