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

[Lazy Warm]

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!

 

[vletm]

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!)

 

[Lazy Warm]

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.

 

[vletm]

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

 

[p45cal]

A tweak which might be useful for users is to identify lone start vs. finish times with a different shape.
If you change:

If d1 <> Empty Then
  x1 = d1
Else
  x1 = d2
End If

to:

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:

With .Shapes.AddShape(msoShapeDiamond, L, T, W, H)

to:

With .Shapes.AddShape(myshpType, L, T, W, H)

You get this sort of thing:

I haven't bothered with exact positioning.

 

[vletm]

... or more colors which user could set as want
... which Lazy Warm didn't ask