Linking the order of two objects with the contents of a cell

yorkshirelad

Board Regular
Joined
Aug 18, 2003
Messages
100
Hope I can get some input here.

I have two objects (box1 and box2) on top of each other on a worksheet. Each of these objects contain hotspots which run a different set of macros.

I would like to change the order of the two objects by changing the contents of a cell.

For example if cell A1 = 1 then box1 is on top, If cell A1 = 2, then box2 is on top.

Any help would be much appreciated.

Many thanks

Yorkshirelad
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Linking the order of two objects with the contents of a

Try this in the module for the Worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    If Target.Value = 1 Then
        ActiveSheet.Shapes("Box1").ZOrder msoBringToFront
    ElseIf Target.Value = 2 Then
        ActiveSheet.Shapes("Box2").ZOrder msoBringToFront
    End If
End Sub
 

yorkshirelad

Board Regular
Joined
Aug 18, 2003
Messages
100
Re: Linking the order of two objects with the contents of a

Unfortunately that doesn't seem to do anything.

I already have macros e.g. MacroBox1 which puts Box1 on top and MacroBox2 which puts Box2 on top - is there a way to trigger them by the cell contents?

Many thanks
 

yorkshirelad

Board Regular
Joined
Aug 18, 2003
Messages
100
Re: Linking the order of two objects with the contents of a

I now have a button that the user can click to run the macro but I still need some help with the coding. It needs to equate to -

If A1=1 "Box1" is on top, if A1=2 Box2 is on top.

Any help would be much appreciated.

Many thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Re: Linking the order of two objects with the contents of a

yorkshirelad said:
I now have a button that the user can click to run the macro but I still need some help with the coding. It needs to equate to -

If A1=1 "Box1" is on top, if A1=2 Box2 is on top.

Any help would be much appreciated.

Many thanks

The code I posted will fire when cell A1 is changed by the user. It worked when I tested it.
 

yorkshirelad

Board Regular
Joined
Aug 18, 2003
Messages
100
Re: Linking the order of two objects with the contents of a

Hi Andrew - thanks very much for your further reply

Although I have entered the code you supplied into the worksheet module nothing happens when I change the value of Cell A1.

It doesn't come up with any errors either.

Can the coding be changed so it runs as a macro? At least that way I can see if there is an error and what the error is?

Many thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Linking the order of two objects with the contents of a

Just copy lines 2 to 6 into a procedure in a General module and run that.
 

Forum statistics

Threads
1,148,222
Messages
5,745,467
Members
423,952
Latest member
EduardoM

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