Consulting

Results 1 to 2 of 2

Thread: Add shape only if shape doesn't already exist

  1. #1

    Add shape only if shape doesn't already exist

    I'm trying to add a text box shape. I have the code written to add the text box where I want it and how I want it formatted.

    Dim Message As Integer
    Dim messagebx As Shape
    Message = MsgBox("Would you like to include a message to the client?", vbYesNo + vbQuestion, "Message")
    If Message = vbYes Then
            Range("C12:L12").Merge
            rows("12:22").insert
                Set messagebx = Shapes.AddTextbox(msoTextOrientationHorizontal, 22, 165, 473, 185)
                messagebx.TextFrame.Characters.Text = "[Insert Message]"
                messagebx.TextFrame.Characters.Font.Size = 9
                messagebx.TextFrame.Characters.Font.Name = "Arial"
                messagebx.Line.Visible = msoFalse
            Else
            'do nothing
            End If


    After this and a bunch of other code is performed, my sheet can be updated and the code re-ran. But if I re-run the code, this text box is replaced with a new blank box. I'd like it to just stay using an error handling statement or something. I'm just not familiar with error handling.
    Last edited by Aussiebear; 12-31-2016 at 03:40 PM. Reason: Added code tags

  2. #2
    I solved my own issue. I simply added an extra user input box and rearranged a few actions. Used a yes/no box 'Message1' to give myself space 'rows("12:22").insert' because if I re-run the entire code, information gets replaced in those cells even if there's already a text box. Message2 is another yes/no box to ask if I'm creating a new text box or if the text box already exists. If I pick yes, it creates a new box, if no it doesn't insert a new box ... aka there's already a box there.

    Dim Message1, Message2 As Integer
    Dim messagebx As Shape
    'User input to ask if there is or will be a text box to insert cells where text box will be located
    Message1 = MsgBox("Is there going to be or is there already a message to the client?", vbYesNo + vbQuestion, "Message")
     If Message1 = vbYes Then
     rows("12:22").insert
    'User input to ask if the text box is a new box or if there is already a box
      Message2 = MsgBox("Would you like to add the message to the client now? If you've already written the message, pick NO!", vbYesNo + vbQuestion, "Message")
    If Message2 = vbYes Then
        Set messagebx = Shapes.AddTextbox(msoTextOrientationHorizontal, 22, 165, 473, 185)
         With messagebx
          .TextFrame.Characters.Text = "[Insert Message] *If new text box is inserted by accident, select box and delete."
          .TextFrame.Characters.Font.Size = 9
          .TextFrame.Characters.Font.Name = "Arial"
          .Line.Visible = msoFalse
          .Placement = xlFreeFloating
         End With
        Else
        'do nothing
       End If
    Else
      'do nothing
        End If
    Last edited by Aussiebear; 12-31-2016 at 03:41 PM. Reason: Added code tags

Tags for this Thread

Posting Permissions

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