code to make a block arrow point from cell A to cell B

Hello,
I'm looking for a piece of code to make an existing shape (block
arrow) point from one cell to another.  I know how to refer to cells
and their properties.  It's just the shape that I don't know how to
handle by vba.
Could you give me a hand or a reference?
Thanks
0
hermac
12/29/2009 11:56:57 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1356 Views

Similar Articles

[PageSpeed] 45

Is this what you are wanting?

Activecell.Value = ChrW(&H25BA)
-- 
Cheers,
Ryan


"hermac" wrote:

> Hello,
> I'm looking for a piece of code to make an existing shape (block
> arrow) point from one cell to another.  I know how to refer to cells
> and their properties.  It's just the shape that I don't know how to
> handle by vba.
> Could you give me a hand or a reference?
> Thanks
> .
> 
0
Utf
12/29/2009 12:55:01 PM
[I reocrded a macro while rotating the shape using the rotation bar on
the shape.  Here is the recorded maco

Selection.ShapeRange.IncrementRotation 180#


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165603

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
joel
12/29/2009 1:12:59 PM
On 29 dec, 13:55, Ryan H <Ry...@discussions.microsoft.com> wrote:
> Is this what you are wanting?
>
> Activecell.Value =3D ChrW(&H25BA)
> --
> Cheers,
> Ryan
>
>
>
> "hermac" wrote:
> > Hello,
> > I'm looking for a piece of code to make an existing shape (block
> > arrow) point from one cell to another. =A0I know how to refer to cells
> > and their properties. =A0It's just the shape that I don't know how to
> > handle by vba.
> > Could you give me a hand or a reference?
> > Thanks
> > .- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Thanks Ryan, but no, I inserted a shape from the Insert Menu > Shapes
> Block Arrow and reshaped it with the handles( rotation, resizing
etc..) to make it begin in cell D20 and point to (end in) A8
The AutoShapeType =3D 34.
I would like to programm  it (through resizing and rotating) to
originate in any other cell and stop in any other cell.
Thanks anyway.
Herman

0
hermac
12/29/2009 1:21:05 PM
did you se my posting of rotating the arrow?

Selection.ShapeRange.IncrementRotation 180


A cell and a shape both have the following 4 properties

Left, Top, width, Height

They are pixel references where the top left of the screen is 0,0
(x=width,y=height).  These are similar to a coordinate axis except the
positive direction in the y direction is down the screen (top towards
bottom).  So if you want a shape to go between columns B to C Yuse the
following


set MyLine = activesheet.shapes("Line 1")
MyLine.left = Range("B4")

MyLine.Left = Range("B4").left
MyLine.Width = (Range("C4").left + Range("C4").width) -
Range("B4").left


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165603

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
12/29/2009 3:52:43 PM
On 29 dec, 16:52, joel <joel.43y...@thecodecage.com> wrote:
> did you se my posting of rotating the arrow?
>
> Selection.ShapeRange.IncrementRotation 180
>
> A cell and a shape both have the following 4 properties
>
> Left, Top, width, Height
>
> They are pixel references where the top left of the screen is 0,0
> (x=3Dwidth,y=3Dheight). =A0These are similar to a coordinate axis except =
the
> positive direction in the y direction is down the screen (top towards
> bottom). =A0So if you want a shape to go between columns B to C Yuse the
> following
>
> set MyLine =3D activesheet.shapes("Line 1")
> MyLine.left =3D Range("B4")
>
> MyLine.Left =3D Range("B4").left
> MyLine.Width =3D (Range("C4").left + Range("C4").width) -
> Range("B4").left
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/showthread.php?t=3D165=
603
>
> [url=3D&quot;http://www.thecodecage.com"]Microsoft Office Help[/url]

Yes Joel, you put my nose in the good direction. Thank you.
The precise spot on the cell where the arrow points TO and on the cell
where it points FROM should depend on the relative position of those
cells.
So far I'm experimenting with this :

Sub Macro7()
Dim W As Shape
Dim Orig As Range, Dest As Range
Dim DHor As Double, DVer As Double, OHor As Double, OVer As Double


Set Orig =3D Application.InputBox("Origin Cell", Type:=3D8)
Set Dest =3D Application.InputBox("Destination Cell", Type:=3D8)
If Orig.Cells.Count <> 1 Or Dest.Cells.Count <> 1 Then
    MsgBox "Ranges of of origin and destination must be single cells"
    Exit Sub
End If
Select Case True
Case Dest.Column < Orig.Column And Dest.Row < Orig.Row 'Dest is
linksboven Orig
    DHor =3D Dest.Offset(0, 1).Left: DVer =3D Dest.Offset(1, 0).Top
    OHor =3D Orig.Left: OVer =3D Orig.Top
Case Dest.Column =3D Orig.Column And Dest.Row =3D Orig.Row 'Dest =3D Orig
    MsgBox "Cells of Origin and Destination must be different"
    Exit Sub
Case Dest.Column =3D Orig.Column And Dest.Row < Orig.Row 'Dest is boven
Orig
    DHor =3D Dest.Left + Dest.Width / 2: DVer =3D Dest.Top + Dest.Height
    OHor =3D DHor: OVer =3D Orig.Top

Case Dest.Column > Orig.Column And Dest.Row < Orig.Row 'Dest is
rechtsboven Orig
    DHor =3D Dest.Left: DVer =3D Dest.Offset(1, 0).Top
    OHor =3D Orig.Offset(0, 1).Left: OVer =3D Orig.Top

Case Dest.Column > Orig.Column And Dest.Row =3D Orig.Row 'Dest is
rechtsnaast Orig
    DHor =3D Dest.Left: DVer =3D Dest.Top + Dest.Height / 2
    OHor =3D Orig.Offset(0, 1).Left: OVer =3D DVer


Case Dest.Column > Orig.Column And Dest.Row > Orig.Row 'Dest is
rechtsonder Orig
    DHor =3D Dest.Left: DVer =3D Dest.Top
    OHor =3D Orig.Offset(0, 1).Left: OVer =3D Orig.Offset(1, 0).Top

Case Dest.Column =3D Orig.Column And Dest.Row > Orig.Row 'Dest is onder
Orig
    DHor =3D Dest.Left + Dest.Width / 2: DVer =3D Dest.Top
    OHor =3D DHor: OVer =3D Orig.Offset(1, 0).Top

Case Dest.Column < Orig.Column And Dest.Row > Orig.Row 'Dest is
linksonder Orig
    DHor =3D Dest.Offset(0, 1).Left: DVer =3D Dest.Top
    OHor =3D Orig.Left: OVer =3D Orig.Offset(1, 0).Top

Case Dest.Column < Orig.Column And Dest.Row =3D Orig.Row 'Dest is
linksnaast Orig
    DHor =3D Dest.Offset(0, 1).Left: DVer =3D Dest.Top + Dest.Height / 2
    OHor =3D Orig.Left: OVer =3D DVer
End Select
Set W =3D ActiveSheet.Shapes("Wijzer")
W.Top =3D (OVer + DVer) / 2
W.Left =3D (OHor + DHor) / 2
W.Width =3D Sqr(Application.SumSq((OHor - DHor), (OVer - DVer)))
W.Rotation =3D Application.Degrees(Atn((DVer - OVer) / (DHor - OHor)))
'trigonometric definition of the angle

End Sub

Problem is the exact meaning of Top and Left  with block arrows.
Thanks a lot
Herman
0
hermac
12/30/2009 6:54:58 AM
The origin or a shape is the upper left corner.  when placing them on a
worksheet the problem is the rows and columns on the sheet can change
size but the shapes don't change size at the same time.  Also each row
can have a diferent height and every column can be a different width. 
So you must adjust the size of the coluns and rows before you change the
position and size of a shape.


I only briefly looked at you code.  I aggree that you want to center
the arrows vertically by Getting the height and dividing by 2 to find
the cnet oer the the shape and the of the verticle area where you are
placing the shape on the workbook.


I don't think you want to do the same with the horizontal position but
it may work.  Yo have to realize there is a border around the cells that
have a small dimension.  When you place a shape at the left or top
position of a cell it will sit ontop or the border line around the
cells.  So you want to make the shape a little smaller than the cells
distances.

You will see that if you use the code below the LeftSide and RightSide
give the same position.  Yo uprobably want to have a little space
between the two expecially if you have a visible border around your
cells.


Dim RightSide As Single
Dim LeftSide As Single
RightSide = Range("B2").Left + Range("B2").Width
LeftSide = Range("C2").Left


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165603

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
12/30/2009 10:47:27 AM
Reply:

Similar Artilces:

Way to make non-selectable worksheet?
I have a spreadsheet with 4 form buttons on it that will run different macros. Is there any way to make it so the user can't click on any part of the worksheet except for the buttons? I don't want a message to pop up, I just want Excel to ignore if the user tries to click on anything except the buttons. Thanks! Botman, A couple of possibilities: You could protect the sheet (Tools - Protection - Protect Sheet. You'll want no locked cells (Format - Cells - Protection - Locked). In Excel 2002, you can specify that locked cells cannot be selected. Can't do that in XL97...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

when adding cells resulting from tax calulations they do not equa.
when calulating non continuos columns based on tax percentage they do not add in total due to rounding. how can I add the non continuos cells without rounding the results Hi check out http://www.mcgimpsey.com/excel/pennyoff.html for some ideas Cheers JulieD "Marty" <Marty@discussions.microsoft.com> wrote in message news:949A3935-8E83-4EC3-A342-11D8C779AEB1@microsoft.com... > when calulating non continuos columns based on tax percentage they do not > add > in total due to rounding. how can I add the non continuos cells without > rounding the results ...

Trying to make a duplicate roster with a button. #2
That seems like it would be easier, but sometimes easier isn't what i wanted... : -- virte ----------------------------------------------------------------------- virtex's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1488 View this thread: http://www.excelforum.com/showthread.php?threadid=26516 ...

Blocking external emails to Group email boxes
How do I stop external senders from reaching the email boxes that might include all domain users? Someone might find the name of one of those groups email address and send to all users. Not good!! what version of Exchange? in Exchange 2003 you can restrict delivery to "authenticated users"; on 5.5, you can restrict delivery on the Delivery Restrictions tab, maybe to whatever list contains all your own users... "RickS" <RickS@discussions.microsoft.com> wrote in message news:C593ED15-0461-4F4B-81F5-D8C8157B6AC0@microsoft.com... > How do I stop external senders f...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Excel Cell Protection / Locking Problem
Is there a way to protect formulas in a sheet (that is, locking certai cells and protecting the sheet) while still enabling users to add line in unlocked areas of the sheet? I am trying to develop a price quoting form where the user can ad extra lines (if additional line items are added) while I keep th structure of the sheet (formulas that total columns, calculate tax an so forth) intact. I have a feeling that once I protect a sheet, I am foreclosed fro adding line no matter what. Thanks -- Message posted from http://www.ExcelForum.com have you used Data Validation? This restricts wha...

making a program rotate and delete
I am so stuck. i am trying to make a wresting program in excel. Th top row is weight the first colum is the names of each wrestlers coach Each coach gets to pick up to 5 wrestlers that they do NOT want t fight theirs . and the wrestlers names get putunder their weigh class. B]I need the program to rotate each weight class so it wil randomly match up wrestlers but NOT the ones that don't want to fight. Am i explaing this well, (i doubt it) [/B] thanks -- Message posted from http://www.ExcelForum.com I still can't figure it out anyone have any ideas -- Message posted from htt...

Remove Restore Points?
My last run of AVG found 2 trojans in two restore point files (I think that's what they were), but I can't remove the file involved, a long numerical file name. Can I remove them? How? Thanks jw@eldorado.com wrote: > On Sat, 06 Mar 2010 06:36:27 -0600, philo <philo@privacy.net> wrote: > > >> turn off System Restore >> >> then reboot and turn it back on >> >> >> BTW: the default size of 12% is absurd... >> when you reset it... 2-3% should be fine > > > It worked,. Thanks > > I set it...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

Maximum Number of Cell Formats
My company uses very large spreadsheets to confrom to goverment requirements. Recently we have run into a Warning message when we try to insert a new column. It says we've used up all the cell formats, so we can't add any new cells. The Microsoft documentation seems to indicate that the maximum number of cell formats is 4000. I guess we're hitting that limit and Excel won't let us enter anymore data. How do I increase the max number of cell formats? How do I find out how many I've used already? How do I delete ones I'm not using (Excel seems to "hang on&quo...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

Need to check if the value in one cell is correct or not
Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digi...

count table non empty cells
Hi Layout: WXP SP3, Office 2003. I have a table that has checkmarks in some of it's cells (one character per cell) . I want to use a formula field in the last cell of each column that counts the non empty cells (the ones with checkmarks) from that column. Thanx Crios Exactly what are the checkmarks? Are they FormFields as used in document that is protected for filling in forms? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnew...

Making OL Notes Always On Top
Hi All, Is it possible to write a Note in OL and have it always On Top so that no matter what application is happening it is always on the screen. All I can seem to do is open the Note on top of one application, but if I go to another screen I have to click the Note from the Task Bar to place it on the screen again ie I can't get it to STAY on top - any ideas? Regards Ron H. ...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Hard cells
Hi, I have a wookbook, say it is Workbook A, in which the cells take calculations from another workbook, say Workbook B. So A3 in Workbook A may read as 334.40 but it is really a combination of A4 and A6 in Workbook B. Here is my delimma. I want to email Workbook A to someone but email not Workbook B. Is there some way to turn A3 in Workbook A into a hard number, 334.50, so that it will read that number when they receive it? Thanks a lot, Stephen Copy the cell, then choose Edit/Paste Special and select the Values radio button. In article <01d701c39e4b$bfa80020$a301280a@...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Change case for all text in cell range
I'm trying to change all the names in existing cells (in a contiguous range) from standard case to all upper case (e.g: A1 = Joe Smith, A2 = Jane Doe, etc. to: A1 = JOE SMITH, A2 = JANE DOE, etc.). I need the new upper case values to be in the original cells. Using "=UPPER(A1:BB82) forces the values in that range to change but the new upper case values are in a different cell range. Any ideas? Thanks in advance. RLL ~ 1/27/2004 Borrowing code from Dave Peterson on truncating Left, this will make everything in A1:BB86 uppercase. Sub UCaseMe() Application.ScreenUpdating = False D...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Coloring a Cell
Is there any way to make a cell turn a different color when the value in it is something? I have a bunch of scores being calculated and I want anything lowed than 80% to be tunred yellow and anything below 60% to be turned red. I have some programming knowledge and I'm willing to learn. Thanks Zach -- OlYeller21 ------------------------------------------------------------------------ OlYeller21's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36309 View this thread: http://www.excelforum.com/showthread.php?threadid=562443 Check out "Conditional Fo...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...