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

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 24, 2005

• #2

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

 

Upvote 0

Y

yorkshirelad

Board Regular

Joined

Aug 18, 2003

Messages

100

• Jan 24, 2005

• #3

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

 

Upvote 0

Y

yorkshirelad

Board Regular

Joined

Aug 18, 2003

Messages

100

• Jan 24, 2005

• #4

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 24, 2005

• #5

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

Click to expand...

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

 

Upvote 0

Y

yorkshirelad

Board Regular

Joined

Aug 18, 2003

Messages

100

• Jan 24, 2005

• #6

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 25, 2005

• #7

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.

 

Upvote 0