Need help setting .Min and .Max for Active X spinner

[OaklandJim]

I am attempting to implement dynamic .Min and .Max properties of an ActiveX spinner. I've tried to research this but I've gotten nowhere. I tried supposed solutions that I could find on-line but nothing I tried works. What simple thing am I missing?

Bonus question: does resizing shown address key ActiveX controls' sporadic resizing issue?

Here is hacked up code

Private Sub SpinButton1_Change()

    'Dim oSpinner As OLEObject
   
    Dim oSpinner As Shape
   
    Dim iMinValue As Long
   
    Dim iMaxVal As Long
   
    Dim iSpinner1Height As Long
   
    Dim iSpinner1Width As Long
   
    'Set oSpinner = Me.OLEObjects("SpinButton1")
    'Set oSpinner = Me.Shapes("SpinButton1")
   
'   Get height and width from defined names' RefersTo field.
    iSpinner1Height = [Spinner1Height]
    iSpinner1Width = [Spinner1Width]
   
'   Determine .Min and .Max values for the spinner.
    iMinValue = 5
    iMaxVal = 100 - Me.Range("C2").Value
   
    With Me.OLEObjects("SpinButton1")
       
'       Set spinner height and width.
        .Height = iSpinner1Height
       
        .Width = iSpinner1Width
       
'       Object does not support this property or method
        .Min = iMinValue
       
'       Object does not support this property or method
'        .ControlFormat.Min = iMinValue
   
    End With
   
'   Type Mismatch error if oSpinner is defined as an OLEObject
'   And Object does not support this property or method if oSpinner
'   is defined as Object or shape
    oSpinner.ControlFormat.Min = iMinValue

End Sub

 

[OaklandJim]

Your second post was the solution. It gave me what was needed when I was fiddling with the ActiveX Control. I also tried form spinner before ActiveX but it did not work for me.

You'll get a laugh from this, I suspect, but because ActiveX controls are so notorious for bad behavior, for the need of the person that I was helping I built my own spinner from grouped shapes. It works well. I was surprised that I could use grouped shapes but it works.

I really do appreciate all of your input. Thank you!