Solved

Excel VBA: Listing freeform points

Posted on 2012-03-15
13
567 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

25 Experts available now in Live!

Get 1:1 Help Now