Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a few questions

    Code:
    ActiveSheet.Buttons.Add(730.5, 26.25, 208.5, 60).Select
    	Selection.OnAction = "XX_Email1"
    	ActiveSheet.Shapes("Button 26").Select
    	Selection.Characters.Text = "SEND EMAIL TO" & Chr(10) & "BLAH BLAH BLAH"
    	With Selection.Characters(Start:=1, Length:=34).Font
    		.Name = "Arial"
    		.FontStyle = "Bold"
    		.Size = 14
    	End With
    Can this recorded piece of code be simplified?
    What does the (730.5, 26.25, 208.5, 60) represent?
    Why button 26? My workbook does not have 26 buttons, it has about eight, so can I reset the numbers of these? Is there a button properties area?
    Can I select "active button" instead of button 26?

    Thanks in advance

    Also, what would the syntax be to check if a specific button exists?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    730.5, 26.25, 208.5, 60 are the left, top, width and height of the button, in points (where 72 points = 1 inch).
    You may have 26 shapes on the sheet, and/or you may have added and removed buttons in the past.

    You could change the code to

    Code:
    With ActiveSheet.Shapes.AddFormControl(xlButtonControl, 730.5, 26.25, 208.5, 60)
      .OnAction = "XX_Email1"
      With .TextFrame.Characters
    	.Text = "SEND EMAIL TO" & Chr(10) & "BLAH BLAH BLAH"
    	.Font.Name = "Arial"
    	.Font.Bold = True
    	.Font.Size = 14
      End With
    End With

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    For anyone else viewing this thread, Hans code is missing a ".font" before the ".Size".

    Hans, much appreciated Thankyou. That code makes so much more sense, why does the recorder create such gibberish?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks, I have corrected my previous reply.

    For you, creating a button and setting its properties is a series of logically related actions.
    The macro recorder can only record individual, non-related actions because it doesn't know what you're going to do next.
    Moreover, the code behind the macro recorder is a strange mixture of relics from older versions of Excel and half-baked adaptations to new versions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •