Insert Diamond Shape for every Row and add cell value to each shape

I have an excel spreadsheet that has data in columns A through D. In Column D, there are dates in the format "MM-DD-YYYY". What I want the coding to do, is for every value in column D to insert a DIAMOND shape in the next column over (column E) and then to insert the text in D2 into it's respective shape. This needs to be a loop since the amount of rows vary all the time.

Please see the attached workbook for an example of how the data is set up. Need ASAP

Thanks!

Can anyone please help me with this? Need help ASAP!!

Hey Ollie,

Yes, I am positive about the requirement. I need code that will loop through and ends once one of the cells in column D is blank, there could be 1,000 rows or there could potentially be 18,000 just like this case. So, for every row that has data in Column D, complete the following:

1. Insert a diamond shape to column E for every row
2. Add the text in column D for each cell into its respective shape
3. Then fit the shape to each cell.

Please let me know if you have any further questions! Thanks for any help!

Hey Ollie,

This works well. However, Once the code gets to the last cell in the used range, it loops back to the first value in column D. I need the code to stop once a value in Column D = ""

Yes, I need the code to stop when an empty cell in Column D is encountered. I also need the font to be Arial as well as font size 6

Hey Ollie,

You were able to help me tremendously with adding date values to shapes and having the shapes loop through until the end of the used range. That was GREAT!

Now, I have a much FUNNER task...

The shapes that were added (of which you were so kind to help with!), now have the dates in them with the format "MM/DD". I have added a table in a different tab named "Slides" that has the following headers: Q1 2013, Q2 2013, Q3 2013, Q4 2013, etc (up to Q4 2016).

I have added an additional column that will have these values associated to the record which is also associated with the shape.

IF the record contains the value "Q1 2013", then I want the EXACT shape that is within this record to find the "Q1 2013" within the table in the other tab and position this shape directly below "Q1 2013". I need this to be done for EVERY RECORD. However, there is a catch. There is ANOTHER condition that needs to be added. There will be another field within the record that will have either A, B, C, D, E, or F. IF the record falls within "Q1 2013" AND "D", THEN go to Column with "Q1 2013" within the table and move DOWN the amount of rows to get to "D". I need this shape to be placed in the position of "Q1 2013" & "D" within the table location.


As of right now, I believe the amount of records are around 100. So it's not a huge load.

Please see the workbook I have attached for the example. I need this ASAP as I need to push this report out very quickly

Thanks Ollie for the response!

Well, I am not sure how to do a reference and what you are saying...

I figured I would add a step to ensure the diamonds and text boxes were for the CORRECT record.

I am also not sure about
a. placing both the diamond and text box in the small space available for a quarter on the slide worksheet (how would you like to see them positioned?);
b. how should multiple diamonds and multiple textboxes need to be positioned within the same quarter?

a) For now, I am only worried about getting the shapes to be able to be positioned to the CORRECT spot. Once the shapes have been positioned properly, then I can worry about the text boxes...But I think the best way would be to group the two shapes that are within the same record together, and then have that groupbox positioned accordingly to the proper Quarter and proper classificaiton on the "Slides" tab.
- However, ideally it would be great if the shapes were offsetted to account for the monthly cadence.
- For example: Since Q1 includes - January, February, March THEN
have the shapes that are part of January be positioned slightly to the left of February's, and February's slightly to the left of March, etc..

b) I believe that once the shapes have been positioned to the proper Quarter, then the USER will have to go in and manually position them accordingly since the text boxes vary with Character lengths. Some text boxes will actually have up to 150 characters. So I think once the shapes have been positioned correctly, then the USER will go in and manually position them. The thing I want to avoid, are all the shapes being positioned on top of one another b/c then the user wont be able to distinguish what textboxes are supposed to be with which Diamond (I think Grouping will solve this issue though, but you never know).

I hope that answers your questions...please let me know ASAP if they didnt, b/c once again I need to push this report out ASAP! You're the BEST Ollie!

Wow Ollie this is absolutely AWESOME! This is perfect, exactly what I am looking for. In order for me to transfer this code and to edit the correct columns to match up to the report I will be utilizing and the used range of data that I will be using, can you please put your code in maybe a word document highlighting in a certain color for let's say Classification, Finish Date, and Description that I may need to edit. For instance, within your code if my Finish Date or Description were in different columns, where in your code do I need to edit? Also, if the Classification name is a text string instead of an "A" or "B", as long as that text string is in the slides tab in column A for the lookup, will the code still find the correct table to paste the shapes in?

One last request , I think it would also be extremely helpful if the text boxes were able to have the Chr(13) after around 50 characters. That way anything over 50 characters would basically do a wordwrap to a multi-line. Ollie, you are the MAN and have made my life so much easier!

Hey Ollie,

You are absolutely awesome. My new favorite pen pal! haha

Thank you for making your code parameter driven as this will be EXTREMELY beneficial in the future so there is
very low maintenace, if any required. When I transfered the code to the target workbook and changed your declarations
to reflect the columns associated, my first attempt running the code gave me a compile error so I went into my references
and saw that I needed to add "Microsoft Scripting Runtime". I tried running code again and I received a run-time
error, more specifically:

Run-time error '457': This key is already associated with an element of this collection

The line of code that fails at is line 119:
dctClassification.Add Trim$(.Cells(lngRowNumber, gstrOClassificationCodeColumn).Value), lngRowNumber

Any thoughts?

Actually, scratch my last comment about line 119. I saw your comment and I had to unmerge column A on the Slides tab. Once I changed the merged range to column B the code ran up until line 154 with the run-time errror:

Run-time error '424': Object required


I tried deciphering..but to no avail

Yup, I did the unmerge but now have a failure at line 154 see above right before your last comment

Code is on line 154-155:

Set objCalendarQuarter = dctQuarters(Trim$(.Cells(lngRowNumber, gstrCalendarQuarterColumn).Value) _
& "|" & Trim$(.Cells(lngRowNumber, gstrIClassificationCodeColumn).Value))

If you look right under the Help dropdown menu, you will see where in the VBE it tells you where your cursor is on which line and what column in the code.

Hey Ollie,

I did not have Q4 2015 through Q4 2016 listed in the Slides tab but I did have data for those Quarters. I added these quarters and years to the slides tab, ran the code and still received the same run-time error at same line. I then ensured that all of my classification text in column A on the slides tab match the entries in the records on the Task tab - they did match. However, I still get the same run-time error: Object required

Hey Ollie,

I am still receiving the same error at the same line. Yes, I do have the class module as well. Your new msgbox if statement wasn't reached...I did go into Tools > Options > General - Break on All Errors. So that is done...

Just FYI..

- Classification column
* This column is now Column "C" and is column header "Workstream"
* Column includes text strings that are equal to the text strings in Column A on the slides tab

- Description column
* This column is now Column "M" and actually doesn't have a header. There is a formula in
this column "=MID(E2,8,1000)" . This is the value that goes into the textbox

- Finish Date column
* This column is now Column "F" and is column header "Finish"

- Task type column
* This column is now Column "J" and doesn't have a header

- Calendar Quarter column
* This column is now Column "N" and doesn't have a header

SPECTACULAR! Worked!

Can we get the text boxes to move down a little bit? I would essentially like for the top of the text box to barely be touching the bottom of the diamond.

Let's discuss tonight...I have to go somewhere as well. I have to think about this...I really dont want you to have to change any code.

I'll be back on in about 7 hours...thanks Ollie.

Hey Ollie,

I am going to show my colleagues tomorrow, and get their opinion on how everything is set up right now. So I will keep you posted and will let you know by tomorrow night! Again, thanks so much for your help! You have been awesome.

Hey Ollie,

I know it has been some time since I have added anything to this thread. However, I finally have results on how to proceed with this task! The code has worked great, and I have to say I have had A LOT of comments on how awesome the code is! The ONLY issue is the final product which is going into a powerpoint slide. What I've found is it is extremely difficult to maintain the shapes of the diamonds and the text within the diamond to transfer into a powerpoint slide. The diamonds change shape to appear much smaller than the actual object in Excel, and this actually creates A LOT of work for me. Is there any way to have the end result take every shape and put them straight into a powerpoint, and then the user can edit the placements in the slide itself? This would eliminate the issues I am having when I am doing the transfer from spreadsheet to powerpoint as the shapes do not maintain their structure from excel. Any thoughts?