Automatically highlighting cells behind an embedded object

[Jaymond]

Hello everyone!

I'm having a hell of a time trying to get excel to automatically highlight the cells behind an embedded object when I have the object selected.

I have an embedded PDF file shown as an icon. What I would like to do is have the cells behind the icon fill in with red when I click on the icon. Then when I select another cell (deselect the icon), I want all the cells behind that icon to return to white. Is this even possible with excel?

 

[Derek]

Hi there

You could try something like this:

Change a1:d4 in the following code to the range you want to highlight. then paste this in a standard module and assign it to your imbedded icon.

Sub Highlilght()
If [a1:d4].Interior.ColorIndex = 3 Then [a1:d4].Interior.ColorIndex = xlNone Else [a1:d4].Interior.ColorIndex = 3
End Sub

To remove all interior color from the sheet when a cell is selected, use this code (right click sheet tab, left click view code and paste in the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Cells.Interior.ColorIndex = xlNone
End Sub

Change Cells in the above code to your specific range, if required.

regards
Derek

 

[Jaymond]

then paste this in a standard module and assign it to your imbedded icon.

Hello Derek. How do I go about assigning a specific macro to an embedded object? Thanks!

 

[Derek]

Hi again

An alternative is to give the icon / picture a thick red border (eg 15 point line) then assign this macro code to the icon / picture.

The border, displays / hides when the icon/picture is clicked.

Sub RedBorderToggle()
ActiveSheet.Shapes(Application.Caller).Line.Visible = Not ActiveSheet.Shapes(Application.Caller).Line.Visible
End Sub

To hide borders of all objects on the sheet, right click the sheet tab, left click View Code and paste in this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.DrawingObjects.ShapeRange.Line.Visible = msoFalse
End Sub

regards
Derek

 

[Derek]

Hi

Right click your icon / picture, select Assign Macro, then New. That will take you to the VB Editor and show commencement of the code - replace that with the code I gave you. Use Alt + F11 to exit the VB Editor. Right click the icon/picture again, selected Assign Macro and check the correct named macro is assigned to it. Click OK

Regards
Derek

 

[Jaymond]

I think we're on to something here. It does seem to be working, but there's one problem. I have multiple embedded objects in the worksheet. So when I click on an icon, the red border does appear. But it doesn't go away when I click on another icon. The borders only go away when I click on an actual cell. Is there anyway to make the border disappear when I click on another icon so that only the icon I have selected will show its border?

 

[Derek]

Hi

If you want this to apply to all embeded objects, assign this code to them all:

Sub RedBorderToggle()
ActiveSheet.DrawingObjects.ShapeRange.Line.Visible = msoFalse
ActiveSheet.Shapes(Application.Caller).Line.Visible = Not ActiveSheet.Shapes(Application.Caller).Line.Visible
End Sub


regards
Derek

 

[Jaymond]

You da man Derek!

One last question, hehehe. Is it possible to create a hyperlink for a cell that takes you directly to an embedded object shown as an icon?

 

[Jaymond]

Well, I spoke to soon I guess. For some reason, with the codes you had me set up, it won't allow me to double click on the icon and open the PDF file. Any ideas?

 

[Derek]

Hi again

I think, at least in Excel2000, you can only hyperlink between cells. However this might provide a work around.

Right click sheet tab, select View Code and paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
ActiveSheet.DrawingObjects.ShapeRange.Line.Visible = msoFalse
If Target.Font.ColorIndex = 5 Then Application.Run "BorderToggle"
End Sub

Then add the following code to your previous standard module:

Sub BorderToggle()
Dim str1 As String
str1 = ActiveCell.Value
ActiveSheet.DrawingObjects.ShapeRange.Line.Visible = msoFalse
ActiveSheet.Shapes(str1).Line.Visible = Not ActiveSheet.Shapes(str1).Line.Visible
End Sub

Type the name of your icon/picture in bright blue font (colorindex 5). You need to right click on each icon/picture to see what it is called. Now if you select a cell with bright blue font it will activate the border of the icon/picture typed in that cell

regards
Derek