Hi, Can anyone help me with this please.
I have coded a Worksheet Change Event that uses 3 shapes to represent the change. My code to determine which shape to use is working just as I want, but I have never used shapes in vba code before.
Tried searching for the answer but failed!
Basically, I want to delete the Shape in the Activecell and copy 1 of 3 shapes back to the same cell and centre it!
My arrows are called, "Down" ,"Up" and "Same" and they are on the activesheet.
Thanks.
Don't delete the shapes, hide them all except the one you want to display.
If you add/delete/paste multiple times you could end up having troubling referencing the shapes.
If posting code please use code tags, see here.
Any idea how I would reference the Shapes within the Activecell as my code loops through E8:G15 checking the current settings vs. the previous setting.
Then I need to display the relevant shape.
You can refer to the shapes by name via the Shapes collection.
The code would initially hide all the shapes, then based on your criteria display the required shape.
What is the criteria?
Please Login or Register to view this content.
That's kind of the right idea.
Please Login or Register to view this content.
Top 5 Project Risks Matrix.zip
Thanks for that Norie.
I am a bit confused though. Will I have to create an Up,Down and Same shape for each cell in the range(E8:G15) on the attached spreadsheet for it to work?
Eh, you only mentioned 3 shapes.
If you have a set of shapes for each row/cell/whatever then it's going to be a bit more complicated.
I'll have a look at the workbook.
I did, apologies. Should have shown you the workbook to start with.
That was why I was trying to copy the shapes when the target values changed.
Thanks.
Seve
When I open the workbook I'm getting an Information Management Policy alert.
Looking at it's details it says that has the name of the policy as Framework Retention Management and a policy statement.
Is this file from Sharepoint?
Can I safely ignore the alert?
Yes the file is from share point. I am sure it would be ok to ignore the alert.
Have a look at the attached, incomplete file.
I created a group from the Up, Down and Same arrows, then copied the group to the cells you want to display the arrow in.
I've only done the first 2 rows in columns, I only came up with the idea half an hour ago.
Anyway, take a look at the file and post back with a list of all the things that are wrong with it.
Norie,
Range(E8:G8) works perfectly as does Cell(G9), but the Cell(E9) seems to trigger the Shape in Cell(F8) and Cell(F9) triggers Cell(E9)!
Why I have no idea.....
Cheers.
The problem is probably with the way I've done the code to work out which group belongs to the changed cell.
Hopeful a simple fix.
Actually I've a kind of related question, will you be adding/removing rows?
No adding or removing!
Infact we only need the first 5 rows, (8 to 12).
Thanks.
I think I've sorted the problem and I've update to include all 5 rows, see the attached.
By the way, when testing I've been getting unusual results and I think it's because the offset isn't quite right in the original code.
I've changed the offset to 40 and things seem to work, better check that yourself though.
All working perfectly. Thank you very much.
Could you explain this code?
strArrows = "Group " & (Target.Column + Target.Row * 3) - 28
The groups are named Group 1, Group 2, Group 3 in the first row, then Group 4, Group 5, Group 6 in the second and so on.
The numbers can be broken down to <column> + <row>*3.
To get the group number for a specific cell we can use it's row and column, but we need to adjust them because we start in E8 which is row 8 of column 5.
So to find the group number for the target cell we can use this.
which I tidy up a bit to get this.Please Login or Register to view this content.
I hope that makes some sort of sense.Please Login or Register to view this content.
Norrie,
I understand what you have done and it works perfectly.
Thank you for all the help.
Iain.
Hi Norrie,
I missed 1 part of the functionality on this.
I need the Blue Star to appear in Column C when data in column D is new or changes from the default, column D <>"Description of risk. Generally brief overview of what might happen and impact on project and activecell.Offset(0,40)<>"Active"
I know how to trigger the change event and used your code to trap the relevant Star(1-5).
Just not sure how to show the Star. I tried Activesheet.Shapes(strArrows).visible but that didn't work.Please Login or Register to view this content.
I've attached the updated file(includes the 5 stars) should you wish to help.
Thanks.
There's no need to find the group, all we need is the row to identiy the correct star.
Then in the New_Risk sub we would have something like this.
Which would be called like this:Please Login or Register to view this content.
Try that, but make sure you check the logic - not sure I got that quite right.Please Login or Register to view this content.
Norrie,
I have managed to icorporate the code above and all seems well.
Thanks again.
Seve.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks