1 Replies - 492 Views - Last Post: 30 May 2017 - 10:13 PM Rate Topic: -----

#1 cws  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 26-May 17

change the caption of a label on an excel worksheet

Posted 30 May 2017 - 12:31 PM

I have a label1 on a worksheet called HL1(1). How can I change the .visible and the .caption properties during the execution of a vba macro? Seems I have tried everything but I just can't seem to get it.
I am runing excel 2002 (I know it is old) , OS OF XP.

ActiveSheet.Shapes("Label 1").TextFrame.Characters.Text = ".008"
Label.visible. = true
Sheet3.Lable1.Visible = True
Sheet3.Lable1 = ".008"


Is This A Good Question/Topic? 0
  • +

Replies To: change the caption of a label on an excel worksheet

#2 andrewsw  Icon User is offline

  • the case is sol-ved
  • member icon

Reputation: 6375
  • View blog
  • Posts: 25,756
  • Joined: 12-December 12

Re: change the caption of a label on an excel worksheet

Posted 30 May 2017 - 10:13 PM

By "tried everything" you mean by a lot of random guesswork. That isn't an effective, or productive, approach.

Record macros to give you some basic code. Click into words and press the F1 function key to access the help system. Use the Object Browser and, of course, the internet.

I recorded a macro in Excel 2010 (2002 may give slightly different content):

Sub Macro1()
'
' Macro1 Macro
'
    ActiveSheet.Labels.Add(255.75, 60.75, 149.25, 57).Select
    Range("K8").Select
    ActiveSheet.Shapes.Range(Array("Label 1")).Select
    Selection.Characters.Text = "hello"
    Range("D5").Select
    ActiveSheet.Shapes.Range(Array("Label 1")).Select
    Range("G10").Select
End Sub

I can see there is a Labels collection as well as the label belonging to the Shapes collection.

I do know VBA, though, so my guesswork isn't quite random:

Sub Test()
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    ws.Labels(1).Characters.Text = "hi"
    
    ws.Labels(1).Visible = False
    
    ws.Shapes("Label 1").Visible = True
End Sub

(Again, this is for Excel 2010, 2002 may be slightly different. For example, does 2002 have a Labels collection?)

I recommend the book Excel VBA for Dummies by John Walkenbach.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1