1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add shapes and scale them to dates available in the worksheet using VBA

Discussion in 'VBA Macros' started by Lazy Warm, Dec 5, 2017.

  1. Lazy Warm

    Lazy Warm New Member

    Messages:
    2
    I would like to create some basic shapes using excel VBA based on the below mentioned sample data in excel. If both start and finish dates are available then I want to add a line to the scale under respective month or months. e.g. if the start and finish dates are 08-Jan-2017 and 14-Jan-2017 then I want a line to be drawn to the scale under Jan-2017. If there's only either a start or finish date available then I want to add a diamond shape to the scale under the respective month. Please help me to solve this. Thank you so much!

    Capture.JPG
  2. vletm

    vletm Well-Known Member

    Messages:
    2,890
    Lazy Warm
    This would be a Sample of something You asked ... or not?
    Press [Show Dates] to see the result.
    (There are NO all checks of User actions! ... be sure with dates!)

    Attached Files:

    Lazy Warm and sathishsusa like this.
  3. Lazy Warm

    Lazy Warm New Member

    Messages:
    2
    Hi Vletm,

    The excel sheet is excellent! Thank you so much! You have written the code in a very sophisticated manner which is very difficult for a no vice like me to understand. I am unable to see any variable declarations etc so it is getting very difficult for me to understand the code you have written. e.g. you have written "xd" in the code, I am not able to figure it out where you have declared it in the code. If you could explain me such things then it will be a big help to understand the programme. Also, if I enter any date in say e.g. year 2029, the line drawn is not coming to the scale. Perhaps, you may assist me in modifying that as well. Again, thank you so much for all your help.
  4. vletm

    vletm Well-Known Member

    Messages:
    2,890
    < I would like to create some basic shapes using excel VBA based on the below mentioned sample data in excel.
    ... I am unable to see any variable declarations etc ... >
    What would You get from any variable declarations?
    ex 'xd' if there would be text like Dim xd As Long ... would it be better?
    I haven't declared ANY variables in that code! It works without those too.
    You let me understand that You know basic of VBA
    ... then You could read even step-by-step and
    find out what would code do and what would be function of any variable.


    < case 2029 >
    hmm? What would You mean? I tested ex 1-Jan-2029 and there were no challenge! I even tested it with Start and Finish Dates ... worked!
    ... or the scale ... one column per month ...
    if there is huge differences between min & max Dates then
    ... it would look still correct!
    ... or if very long 'lines' then there would be some challenges; I have tested it only dates like in Your sample file!

    >> I modified file for 'longer lines' <<

    Attached Files:

    Last edited: Dec 7, 2017 at 9:17 AM
  5. p45cal

    p45cal Well-Known Member

    Messages:
    924
    A tweak which might be useful for users is to identify lone start vs. finish times with a different shape.
    If you change:
    Code (vb):
    If d1 <> Empty Then
      x1 = d1
    Else
      x1 = d2
    End If
    to:
    Code (vb):
    If d1 <> Empty Then  'start date only
     x1 = d1
     myshpType = msoShapeRightArrowCallout
    Else  'finish date only or no dates at all
     x1 = d2
     myshpType = msoShapeLeftArrowCallout
    End If
    and change:
    Code (vb):
    With .Shapes.AddShape(msoShapeDiamond, L, T, W, H)
    to:
    Code (vb):
    With .Shapes.AddShape(myshpType, L, T, W, H)
    You get this sort of thing:
    upload_2017-12-7_13-40-34.png
    I haven't bothered with exact positioning.
  6. vletm

    vletm Well-Known Member

    Messages:
    2,890
    ... or more colors which user could set as want
    ... which Lazy Warm didn't ask

    Attached Files:

Share This Page