+ Reply to Thread
Results 1 to 17 of 17

Counting Shapes not by Topleftcell property

  1. #1
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Counting Shapes not by Topleftcell property

    I have no code to input here. I'm looking for alternative ways and probably way outside the box ideas on how to count a shape in a given excel range (A2:10) wihtout using the .topleftcell property. I know that's how excel counts them I'm just wondering if there is potentially some method of achieving this with a .top >0 type of code. I've been playing with it so far I've been able to get a count of all shapes using the .top >0 value ,but I don't know if it's possible to restrict that to a range.
    Any help is much apperciated, and it's possible this is all just a wild dream.
    Regards
    G

    See threads below that so some of the coding, I'm placing an edit at the front before labeling solved. Does anyone know how to take that code and do a -1 for each bar of a certain color?
    Last edited by gsrahn91; 05-07-2019 at 01:57 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Counting Shapes not by Topleftcell property

    wihtout using the .topleftcell property
    Why ?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Because I have over 300 shapes on an excel document that intersect certain cell ranges at different points. and not all of those points carry a topleftcell reference (since the start of the objects is all in different locations), but they are all the height and width of cells.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Counting Shapes not by Topleftcell property

    ??

    All shapes have a topleftcell property.

  5. #5
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    My apologies i'm bad at explaining, and I don't have a file that I can actually upload for your view. But I will make a simple one and upload it. If there is a Rectangular shape from Range B1 to G1 it's topleftcell property is only visible in cell A1.
    So
    Please Login or Register  to view this content.
    With that code you could put in cell A1 =CountShapes(B1:B10) and it will return a value of 1 because there is a shape with a TopLeftCell property starting in Cell B1, however if you where to write =CountShapes(C1:10) it would return a value of 0 becuse while it is intersecting a shape it is not intersecting its TopLeft Property.

    I'm almost certain there isn't a method to do what I want I'm just reaching out to see if someone has come up with something clever.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    How about
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Oh my goodness it was that simple... I got to focused in on one way. You are amazing Fluff13. I didn't even think about setting a range to bottom right. So far it looks like it's working. Thank you so much.

  8. #8
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Thank you as well for taking the time to respond to my question.

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    You're welcome & thanks for the feedback

  10. #10
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Before I close this thread out, anyone know how to subtract 1 from the count based on color?

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    You could use something like
    Please Login or Register  to view this content.
    where the value in red is the colour value of the blue you are using. Change one of the shapes to a different colour & won't be counted.

  12. #12
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Thank you again for the amazing work. Have a good day!
    Regards
    G

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    You're welcome & thanks for the feedback

  14. #14
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    The code as is works in most situations however (and I will upload a new document for viewing) sometimes when a bar slightly over laps it will count in the range and sometimes I can't get it out of the range. This code is going into a document that I don't use it's used by other people who produce a schedule and for whatever reason they have used shapes as their people to overlay on a time sheet. Thank you to everyone who looks at this and give feedback or input. I'm trying to come up with solutions that don't require new training. I think the solution is out there, but I get hung up on the syntax of VBA coding.

    Regards
    G

    Code below

    Please Login or Register  to view this content.
    Apparently I'm not allowed to post URL but this post is a cross post. here is the url with out the http://www.mrexcel.com/forum/excel-q...-vba-code.html
    Attached Files Attached Files
    Last edited by 6StringJazzer; 05-09-2019 at 10:17 AM. Reason: Live link added by Moderator

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    Thanks for supplying the link.
    I'm not sure what you are trying to do with the final section of your code, but the first three can be simplified like
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2016
    Posts
    10

    Re: Counting Shapes not by Topleftcell property

    Fluff,

    Thank you for the reply. I will simplify that code. I thought I could condense them, but I kept running into errors (Probably a typo on my part somewhere).

    I'm not sure why but in some places where the shape touches a cell it gets counted one column over. I.E. a shape extending from range b2:g2 will also give a +1 count in column h2. I know it's because the shape is touching H2 and therefore beign seen by the code.

    But this doesn't happen in place that a cell is touching. I think a change in the way the document is produced could stop this error, but I was hoping to create something that didn't require a change in procedure so there's less to train on the new sheets functionality.

    I'm looking for a way based on width in the range to ignore ones that are just touching versus ones that are completely passing through the cell.

    I've been digging into the properties and methods of ranges so I can learn the syntax better.

    Regards
    G

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Counting Shapes not by Topleftcell property

    I'm afraid I've no idea how to check if the shape slightly overlaps a cell.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. .TopLeftCell Property
    By AdiK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2016, 08:18 AM
  2. [SOLVED] Excel 2010 returns wrong .Shapes property values
    By FORTRANguru in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2014, 01:01 AM
  3. [SOLVED] Shapes, TopLeftCell. Row incorrect
    By malcmail in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-28-2014, 08:18 AM
  4. Creating buttons using TopLeftCell
    By Ben F. in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2013, 09:57 AM
  5. Pass Shapes property names into module
    By rexwrx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2007, 08:47 PM
  6. [SOLVED] range variable won't assign (chartobject.topleftcell property)
    By Matthew Dodds in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2005, 10:30 AM
  7. Enabled Property of Shapes?
    By Joe HM in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1