Solved

Excel VBA: Listing freeform points

Posted on 2012-03-15
13
604 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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 42

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 42

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 42

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

687 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