Automatically highlighting cells behind an embedded object

Jaymond

New Member
Joined
Sep 17, 2006
Messages
14
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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

New Member
Joined
Sep 17, 2006
Messages
14
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

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

New Member
Joined
Sep 17, 2006
Messages
14
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592

ADVERTISEMENT

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

New Member
Joined
Sep 17, 2006
Messages
14
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

New Member
Joined
Sep 17, 2006
Messages
14
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,130
Messages
5,857,550
Members
431,885
Latest member
Rsdg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top