?
Solved

Excel VBA: Listing freeform points

Posted on 2012-03-15
13
Medium Priority
?
608 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

771 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