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

[yorkshirelad]

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

 

[Andrew Poulsom]

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

Try this in the module for the Worksheet:

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]

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]

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]

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

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

 

[yorkshirelad]

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]

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.