VBA: Get Spin Button Name

[zero269]

Hello,

I'm looking for a way to get the name of a Form Controls Spin Button on a Worksheet, not in a Form.
I'm working on a macro that builds a calendar, and I'm having to delete the button manually each time I want to clear everything and test my code.

I'm trying to avoid deleting all Objects or all Spin Buttons (only 1 in my case) for a givent Sheet.

Thank you and best regards,

 

[Jeffrey Mahoney]

This code will give you the names of all the controls, but I don't how to help beyond that. If you give that spinner the same name each time, you could delete it very easily

Sub GetNameOfSpinners()
  Dim Cntrl As Shape
  Dim Sht As Worksheet
  
  Set Sht = ActiveSheet
  
  For Each Cntrl In Sht.Shapes
    Debug.Print Cntrl.FormControlType; Cntrl.Name
    
  Next Cntrl
End Sub

XlFormControl enumeration​

xlButtonControl	0	Button.
xlCheckBox	1	Check box.
xlDropDown	2	Combo box.
xlEditBox	3	Text box.
xlGroupBox	4	Group box.
xlLabel	5	Label.
xlListBox	6	List box.
xlOptionButton	7	Option button.
xlScrollBar	8	Scroll bar.
xlSpinner	9	Spinner.

[th]

Name​

[/th][th]

Value​

[/th][th]

Description​

[/th]​

 

[zero269]

Hello @Jeffrey Mahoney

If you give that spinner the same name each time, you could delete it very easily

If only I knew how to create one and name it, I'd be in good shape. I'm currently just using the macro I recorded when I manually inserted it. I couldn't figure out how to modify the recorded code, and there's nothing in there that shows the name; i.e. "Spinner 1" and so on.

I had to record a macro of me deleting it to see what I got...

 ActiveSheet.Shapes.Range(Array("Spinner 5")).Select
 Selection.Delete

I was able to use your code to extract the name and delete it:

Sub Spin_Button_Delete()
  Dim Cntrl As Shape, sName As String
  For Each Cntrl In wsCalendar.Shapes
    sName = Cntrl.Name  'Get Object Name
    wsCalendar.Shapes(sName).Delete 'Delete Object
  Next Cntrl
End Sub

Looping it works just fine in my case. It's only ever one Spin Button. However, in future personal projects, I'll need to figure out how to create them in a more useful way.

Thank you and best regards,

...
If curious, here's my recorded macro:

Sub Spin_Button_New()
  'Spin Button (Cell AF3)
  Range("AF3").Select
  wsCalendar.Spinners.Add(580.2, 41.4, 11.4, 21.6).Select
    With Selection
      .Placement = xlMove
      .PrintObject = False
    End With
    With Selection
      .Value = Year(Date)
      .Min = 0
      .Max = 2050
      .SmallChange = 1
      .LinkedCell = "$AA$3"
      .Display3DShading = True
    End With
    Range("AF3").Select 'Deselect Spin Button
End Sub

 

[Jaafar Tribak]

The macro recorder won't necessarly show you the name given to the newly added spin button. You can however give it a name upon creation with VBA via the Spinner Name Property.

Here is a sanitized version of the code output by the macro recorder:

Sub Spin_Button_New()
    Dim oSpin As Spinner
    Set oSpin = wsCalendar.Spinners.Add(580.2, 41.4, 11.4, 21.6)
    With oSpin
        .Name = "Choose_A_Spinner_Name_Here"  '<=== Choose_A_Spinner_Name_Here
        .Placement = xlMove
        .PrintObject = False
        .Value = Year(Date)
        .Min = 0
        .Max = 2050
        .SmallChange = 1
        .LinkedCell = "$AA$3"
        .Display3DShading = True
    End With
End Sub

Now that you have named the spinner , it is easy to directly delete it without iterating all the controls:

Sub DeleteSpinButton()
    wsCalendar.Spinners("Choose_A_Spinner_Name_Here").Delete
End Sub

 

[zero269]

The macro recorder won't necessarly show you the name given to the newly added spin button. You can however give it a name upon creation with VBA via the Spinner Name Property.

Here is a sanitized version of the code output by the macro recorder:

Sub Spin_Button_New()
    Dim oSpin As Spinner
    Set oSpin = wsCalendar.Spinners.Add(580.2, 41.4, 11.4, 21.6)
    With oSpin
        .Name = "Choose_A_Spinner_Name_Here"  '<=== Choose_A_Spinner_Name_Here
        .Placement = xlMove
        .PrintObject = False
        .Value = Year(Date)
        .Min = 0
        .Max = 2050
        .SmallChange = 1
        .LinkedCell = "$AA$3"
        .Display3DShading = True
    End With
End Sub

Now that you have named the spinner , it is easy to directly delete it without iterating all the controls:

Sub DeleteSpinButton()
    wsCalendar.Spinners("Choose_A_Spinner_Name_Here").Delete
End Sub

Hello Jaafar,

This was soooo super helpful. You didn't even give me a chance to ask this question...

So, I decided to post the question anyways and link to this post. However, perhaps you can copy your post to that thread so I can mark that other thread as the Solution...?

It's here: VBA: Create a Form Controls Spin Button

Thank you and best regards,

 

[Jaafar Tribak]

Glad I could answer your question and thanks for the feedback.

So, I decided to post the question anyways and link to this post. However, perhaps you can copy your post to that thread so I can mark that other thread as the Solution...?

I see you already posted a link to this thread in your new thread but I will link my answer there as well if you wish.

Regards.