Monday, January 20, 2014

Move command button or any object with sheet scroll

Generally we use command button to assign macro to a button or assign it to shape.But while working on a large sheet button disappears as we scroll horizontally or vertically.this piece of could will scroll command button along with sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Range("I6:K6")) Is Nothing Then
With Me.UPDATE_1
.Left = Target.Left + 20
.Top = Target.Top - 70
.Width = 72
End With
End If
End Sub
we are using selection change event to record any change in sheet hence running the code.target is defined as active cell.i used range("i6:k6") as target.if any change will be made in these cells my command button will move.

No comments:

Post a Comment