Programmatically create a label in a worksheet.

[david.j.winfield]

I am using excel 2003. How would I programmatically create a label in
a worksheet. The following doesn't work:
Dim mylabel As Label
mylabel.Visible = True
mylabel.Caption = "my caption"

Is there a way to programmatically create an array of labels by doing
Dim mylabels(5) as label?

Then could I create/delete the label as needed by using mylabel.create
(or whatever the appropriate method is) and mylabel.delete?

if I want a new function that performs an action on several different
labels can I give it a label/array of labels as a parameter by
declaring the function with something like:
Function my_label_function(changelabel as label) as integer

Thanks,
David

 

[Ivyleaf]

Hi David,

Depends what sort of label you mean... one from the 'control toolbox'
or 'forms' bar. They both have their pro's and cons. Personally I
usually prefer the 'forms' ones. To add one of these, the following
code should be an example:

Sub MakeLabels()
Dim myLabel As Label
Dim TgtRng As Range

Set TgtRng = Selection

With TgtRng
Set myLabel =
ActiveSheet.Labels.Add(.Left, .Top, .Width, .Height)
End With
With myLabel
.Caption = "Helo world... I'm a label!"
.Name = "New Label"
End With
End Sub

As for making an array of them, I don't think you can... but you could
possibly stick 'em in a collection? I've never tried it. Someone else
on here is bound to know.

Cheers,
Ivan.

 

[Gary''s Student]

Here is a little label maker:

Sub bgtr()

Set myDocument = Worksheets(1)
myDocument.Shapes.AddLabel(msoTextOrientationHorizontal, _
100, 100, 60, 150) _
.TextFrame.Characters.Text = "My Caption"

End Sub

 

[david.j.winfield]

Hi David,

Depends what sort of label you mean... one from the 'control toolbox'
or 'forms' bar. They both have their pro's and cons. Personally I
usually prefer the 'forms' ones. To add one of these, the following
code should be an example:

Sub MakeLabels()
Dim myLabel As Label
Dim TgtRng As Range

Set TgtRng = Selection

With TgtRng
Set myLabel =
ActiveSheet.Labels.Add(.Left, .Top, .Width, .Height)
End With
With myLabel
.Caption = "Helo world... I'm a label!"
.Name = "New Label"
End With
End Sub

As for making an array of them, I don't think you can... but you could
possibly stick 'em in a collection? I've never tried it. Someone else
on here is bound to know.

Cheers,
Ivan.

Thank you, this works great. How would I make a label of the type
that is on the 'control toolbox'?

 

[david.j.winfield]

As far as making an array of labels this does seem to work:
Dim counter As Integer
counter = 1
Dim mylabel(5) As Label

Do Until counter = 5
Set mylabel(counter) = ActiveSheet.Labels.Add(20, 20, 100, 100)
mylabel(counter).Visible = True
mylabel(counter).Caption = "This is mylabel(" & counter & ")"
counter = counter + 1
Loop

How would this be implemented using the for each statement?

Thanks,
David

 

[david.j.winfield]

If I use an array such as mylabel(5) I cannot actually use the array
latter on in another function because the labels are not actually
named mylabel(1), mylabel(2), etc. So I guess I have to store each of
the label names in a sheet and get them back later. The following
code will do essentially what I want. Is there a better way?

Private Sub CommandButton1_Click()
Dim counter As Integer
counter = 1
Dim mylabel(5) As Label

Do Until counter = 6
'//ActiveSheet.Labels.Add(.Left, .Top, .Width, .Height)
Set mylabel(counter) = ActiveSheet.Labels.Add(20, 300 + 20 *
counter, 100, 20)
mylabel(counter).Visible = True
'mylabel(counter).Caption = "This is mylabel(" & counter & ")"
mylabel(counter).Caption = "This is mylabel(" & counter & ")" & _
"Its name is" & mylabel(counter).Name

'//store label names in sheet
Range(Cells(19 + counter, 1).Address).Value =
mylabel(counter).Name
counter = counter + 1
Loop

End Sub

Private Sub CommandButton2_Click()
Dim mylabel_names(5) As String
Dim counter As Integer
counter = 1

Do Until counter = 6
mylabel_names(counter) = Range(Cells(19 + counter,
1).Address).Value
On Error Resume Next
ActiveSheet.Labels(mylabel_names(counter)).delete
counter = counter + 1
Loop

End Sub

Private Sub CommandButton3_Click()
ActiveSheet.Labels.delete
End Sub