Solved

Excel VBA: Listing freeform points

Posted on 2012-03-15
13
555 Views
Last Modified: 2012-03-19
What is the VBA code that will...
list freeform points in column A of an Excel file in the order the freeform points were created.

e.g., if a user creates a freeform line, then enters a point somewhere in the middle, that point will show in the end because it was created last.
0
Comment
Question by:ouestque
  • 5
  • 4
  • 4
13 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37727630
This will do it for one-time entries. If there are multiple entries or deletions then you will have to define how to handle those.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Exit Sub
Target.EntireRow.Cells(1, 1).Value = WorksheetFunction.Max(Range("A:A"))
End Sub

You need to paste it in the worksheet module. To do this right-click on the worksheet tab name and select view code and then paste it.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37728106
I answered a similar (almost exact) question just a short while back.

Your solution, I believe, is here:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27622363.html

Embedded in the solution thread is one solution from the starting to the ending point, and the other is sorted based on x-value.

There is no storage of someone then creating a freeform node in the middle of a freeform.  At the end of the day, the storage is in node order - from left to right, which can be sorted based on x or y value.  So the solution I posted gives you those two options.

Also, there is not event for freeform creation such that starts and stops and edits can be easily determined.  With an appropriate methodology (like hitting a button telling VBA that a change happened, past data stored could be compared with current data to put revisions at bottom).

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37728125
I see you were the original author of the post I posted, lol :P

Another methodology (if you have Excel 2010) would be to turn the macro recorder on, then start drawing - this step MIGHT ( low liklihood ) be able to be automated, not sure)  Once the drawing is finally complete, the VBA code generated could be converted into what you asked for.  Additional edits would follow the same process, where the macro put the edits below the existing data in column A.

Please help me understand why this additional detail is needed - what value would you get out of it?

Dave
0
 

Author Comment

by:ouestque
ID: 37729078
Thanks guys for the responses.

(It's early so let me know if this is a wrong assumption.) Are you saying it is impossible to list nodes in the order in which they were created?

The question Dave referenced above is a different. That question asks to label each node from one end of the line to the other, no matter what order the nodes were created.

My question now is to label each node on the line in the order they were created.
e.g., if user creates a line, then enters a node in the middle, the node in the middle will show at the end of the list because that node was the last created node (Even though it is in the middle of the line). If the user adds another node anywhere else on the line, then that newly created node will show at the end of the list. etc.

Again, it is early so let me know if I missed something. :)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37729333
Just try the code I have given you in the first post and tell me how it is.
0
 

Author Comment

by:ouestque
ID: 37729346
Example: let's pretend a clothes designer drew a free form line. Later someone looks at it and says I don' t remember drawing it that way. It almost looks like someone entered a bunch of nodes after I drew the line. Can you tell me if this happened? If so list which nodes are new.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37729371
No, I am not clear what you want. Can you explain cell-wise?
0
 

Author Comment

by:ouestque
ID: 37729415
Thanks Dave,

I just tried your code. Nice code btw!
Columns C in your excel file seems to show all nodes as they follow the line. Column A of your Excel file sorts the nodes in ascending order. By x and y distances.

Nonetheless neither column shows nodes in the order in which they were created though :(
I.e. when I insert nodes in a line they do not show towards the end of the list.
0
 

Author Comment

by:ouestque
ID: 37729504
let's pretend I click on insert--> picture--> autoshapes-->freeform then in column D1 I click, then D5 I click, then in E9 I click then in D10 I click then press escape. I will have a line with 4 nodes. Now pretend I right click on the line select Edit nodes then I right click the line on D7 select add point. I drag that point to h5. I would like that new point (dragged to h5) to show last in the list because it was the last created node. Your code (brilliant code btw) would show the node dragged to h5 third in the list because it is the third node on the line. I would like that node to show last in the list because it was the last created node.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37730009
I think this could be accomplished by having a stored version of the freeform from which to compare with.  

Do you care about deleting nodes and how would they be handled?

I can envision having a button to say "refresh my node list" and it comparing with a prior version of the freeform to give you the results you asked for.  However, Deleted nodes would just be gone?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37730011
@ssaqibh - this is about interpreting a drawing, not what's in cells.  See the post I originally posted an open it and you'll see what I mean.

Dave
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37730162
Yes I see that now that a detailed explanation is given.
0
 

Author Closing Comment

by:ouestque
ID: 37740583
Good idea. Thanks Dave! I think I can handle the rest :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now