Excel VBA: Listing freeform points

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
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.
I answered a similar (almost exact) question just a short while back.

Your solution, I believe, is here:

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).


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?

CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

ouestqueAuthor Commented:
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. :)
Saqib Husain, SyedEngineerCommented:
Just try the code I have given you in the first post and tell me how it is.
ouestqueAuthor Commented:
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.
Saqib Husain, SyedEngineerCommented:
No, I am not clear what you want. Can you explain cell-wise?
ouestqueAuthor Commented:
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.
ouestqueAuthor Commented:
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.
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?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
@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.

Saqib Husain, SyedEngineerCommented:
Yes I see that now that a detailed explanation is given.
ouestqueAuthor Commented:
Good idea. Thanks Dave! I think I can handle the rest :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.