Increment a series in a bubble

[Flavien]

Hello,

The code below allows me to insert an image (shape 8 point star) and write the letter "A" on it.
Would it be possible to change the code so that on the second click the letter "A" is replaced by "B" and so on? with the possibility of starting again at "A" if I wish?

Thanks a lot for your help!

Have a nice day

Sub Keyence_220424()

    ActiveSheet.Shapes.AddShape(msoShape8pointStar, 400, 400, 28.35, 28.35).Select
    Selection.ShapeRange.Line.Visible = msoFalse

    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 255) 'pink
        .Transparency = 0
        .Solid
    End With

    Selection.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
        
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "A"
    
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter

    End With
    
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 1).Font
        .Size = 14
    End With


End Sub

 

[DanteAmor]

It works as follows:

• To change the letter we need an available cell in your sheet, for example, cell Z1, adjust it in the macro.
• Every time you run the macro, the letter will change to B, C and so on.
• Note: The letter will change, but the position of the bubble on the sheet will always be the same.
• To start again in A, simply delete the letter in cell Z1 or the one you have chosen and run the macro.

User the following macro.

Sub Keyence_220424()
  Dim shp As Shape
  Dim cell As Range
 
  Set cell = Range("Z1")          'Fit to your cell
 
  If cell.Value = "" Then
    cell.Value = "A"
  ElseIf Asc(UCase(cell.Value)) = 90 Then
    cell.Value = "A"
  Else
    cell.Value = Chr(Asc(UCase(cell.Value)) + 1)
  End If
 
  Set shp = ActiveSheet.Shapes.AddShape(msoShape8pointStar, 400, 400, 28.35, 28.35)
  shp.Line.Visible = msoFalse

  With shp.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 0, 255) 'pink
    .Transparency = 0
    .Solid
  End With

  With shp.TextFrame2.TextRange.Characters
    .Parent.VerticalAnchor = msoAnchorMiddle
    .Text = cell.Text
    .Font.Size = 14
    .ParagraphFormat.Alignment = msoAlignCenter
  End With
End Sub