Data Validation Message Box Moves

Posted by Eric on January 16, 2002 3:43 AM

I have several d.v. message boxes on my spreadsheet. If the user repositions any one of these boxes, they all move to the same location instead of staying near their associated cells.

Is there a way to lock the position of the data validation message boxes?

Posted by Joe Was on January 16, 2002 6:44 AM

Turn off the assistant before you call the data validation code the on code exit turn the assistant back on. This will lock the box to the cell.

Many of the objects in Excel have X & Y position properties. Data Validation is not one of then though. So, any controle will need to come from another object.

So indirectly we can move the Validation box when it is under the controle of the assistant. With the assistant, which will controle the position of the validation box, code a move of the assistant and the validation box will move with it.

Use this block to move the assistant. JSW

With Assistant
.Reduced = True
.Move xLeft:= 400, yTop:= 300
.MoveWhenInTheWay = True
.TipOfDay = True
.Visible = True
.Animation = msoAnimationGreeting
End With

Here the ".Move xLeft:= 400, yTop:= 300 is the position property of the object. JSW

Posted by Eric on January 16, 2002 6:41 PM

thanks Joe! (no text)