Iterating through each label on a worksheet

U

usr58201

I need to write something that will iterate through all of the labels on myworksheet and change some of their properties. Specifically, I need to update the height and width, the background and foreground colors, and add a border. I tried this, which was successful for the height and width, but there seem to be no options for the other properties. Can anyone help? Thanks.

Sub updateLabels()

Dim obj As OLEObject
Dim lblwidth, lblHeight as Single
lblWidth = 488.25
lblHeight = 26.25

For Each obj In Sheet2.OLEObjects
If obj.progID = "Forms.Label.1" Then
obj.Height = lblHeight
obj.Width = lblWidth
obj.Backcolor 'doesn't exist
obj.Forecolor 'doesn't exist
obj.Border 'doesn't exist
End If

Next obj

End Sub

By the way, I don't really care if these labels are ActiveX labels or just regular old labels. I just did ActiveX since I couldn't find a way to iterate through regular labels. If you have a solution that works for normal labels, please reply.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top