Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
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. On Apr 2, 12:01*am, " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
Here is a little label maker:
Sub bgtr() Set myDocument = Worksheets(1) myDocument.Shapes.AddLabel(msoTextOrientationHoriz ontal, _ 100, 100, 60, 150) _ .TextFrame.Characters.Text = "My Caption" End Sub -- Gary''s Student - gsnu2007g " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
On Apr 1, 9:29 am, Ivyleaf wrote:
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. On Apr 2, 12:01 am, " wrote: 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 Thank you, this works great. How would I make a label of the type that is on the 'control toolbox'? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
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 On Apr 1, 9:56 am, " wrote: On Apr 1, 9:29 am, Ivyleaf wrote: 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. On Apr 2, 12:01 am, " wrote: 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 Thank you, this works great. How would I make a label of the type that is on the 'control toolbox'? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically create a label in a worksheet.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically create button on Excel worksheet (vb.net) | Excel Programming | |||
How do I use a worksheet label to create a formula in Excel 2007? | Excel Discussion (Misc queries) | |||
How do I use a worksheet label to create a formula in Excel 2007? | Excel Discussion (Misc queries) | |||
label x-axis values programmatically | Charts and Charting in Excel | |||
Create UserForm programmatically | Excel Programming |