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

 

[Flavien]

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

Hello DanteAmor,

Thank you for your patience and especially thank you for your answer. Your code works wonderfully!

I've modified your code a bit in order to use it with a UserForm.
On the other hand, I have to close the userform and I would like to keep in memory the last value (e.g. "E") the next time I open it.
Do you have an idea?

 

[Flavien]

Below the new code:

Private Sub CommandButton1_Click()

Dim shp As Shape

 
  If UserForm1.TextBox1.Value = "" Then
    TextBox1.Value = "A"
  ElseIf Asc(UCase(TextBox1.Value)) = 90 Then
    TextBox1.Value = "A"
  Else
    TextBox1.Value = Chr(Asc(UCase(TextBox1.Value)) + 1)
  End If
 
  Set shp = ActiveSheet.Shapes.AddShape(msoShape8pointStar, 350, 350, 28.35, 28.35)
  shp.Line.Visible = msoFalse

couleur = TextBox2.Value
    
    'Liste des différentes couleurs
    If couleur = "1" Then colori = RGB(0, 242, 0)       'Vert
    If couleur = "2" Then colori = RGB(255, 140, 45)    'Orange
    If couleur = "3" Then colori = RGB(51, 51, 255)     'Bleu
    If couleur = "4" Then colori = RGB(0, 218, 254)     'Bleu Ciel
    If couleur = "5" Then colori = RGB(112, 48, 160)    'Violet
    If couleur = "6" Then colori = RGB(255, 0, 255)     'Rose
    

  With shp.Fill
    .Visible = msoTrue
    .ForeColor.RGB = colori
    .Transparency = 0
    .Solid
  End With

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


End Sub

 

[Flavien]

Hello DanteAmor,

Thank you for your patience and especially thank you for your answer. Your code works wonderfully!

I've modified your code a bit in order to use it with a UserForm.
On the other hand, I have to close the userform and I would like to keep in memory the last value (e.g. "E") the next time I open it.
Do you have an idea?

As you suggested in your initial post, I opted to write the value of the bubble in a cell and copy this value into the textbox1 when opening the userform.

=> If you ever have a solution to avoid writing in a cell, it would be cleaner.

Thank you in advance for your help.
Below is the code to retrieve the value of a cell when opening a userform.

Private Sub UserForm_initialize()

    
   UserForm1.TextBox1.Value = Worksheets("Feuil1").Range("AG1")
   

End Sub

 

[DanteAmor]

As you suggested in your initial post, I opted to write the value of the bubble in a cell and copy this value into the textbox1 when opening the userform.

It's a good option.
When you use a userform, all the values of the userform controls (textbox, combobox, listbox, etc.) are in memory. When you close the userform the memory is cleaned and all the values are lost, so putting the values in cells is a good option.

 

[Flavien]

It's a good option.
When you use a userform, all the values of the userform controls (textbox, combobox, listbox, etc.) are in memory. When you close the userform the memory is cleaned and all the values are lost, so putting the values in cells is a good option.

Thank you!