VBA to move lable to a specific range in a work sheet

[CEO76]

Hi all,

I have one ActiveX control Label inserted into a worksheet. There is a particular range on that work sheet I would like to resize the lable to the range height and width as well as move the lable to cover the range.

I can code to size the lable, however, I don't know how to move the lable to cover the range on that worksheet.

Could any of you help me with a vba code to perform the label move?

Thanks

CEO76

 

[VoG]

Try like this

Sub test()
With ActiveSheet.OLEObjects("Label1")
    .Top = Range("C5").Top
    .Left = Range("C5").Left
End With
End Sub

 

[Rick Rothstein]

Does this code do what you want (just change my example sheet and range to your actual sheet and range and, of course, use your label's actual name)...

Sub MoveResizeLabel()
  Dim RangeToCover As Range
  Set RangeToCover = Range("D4:G9")
  With Worksheets("Sheet1").Label1
    .Left = RangeToCover.Left
    .Top = RangeToCover.Top
    .Width = RangeToCover.Width
    .Height = RangeToCover.Height
  End With
End Sub

 

[CEO76]

Hi Rick & Vog,

The code works great. This is what I reall want.

I want to have an event whrere I move my mouse over a range and a cell in that range highlighted. The only way to do it is to have a mouseover event on the labale.

Anyway. I reall appreciate your help on moving the label to a specific range.

Cheers