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

 

[DanteAmor]

For the size.
I don't understand how you have the names defined but it should be something like this:

  iSpinner1Height = [Spinner1Height].Height
  iSpinner1Width = [Spinner1Width].Width

For the min and max

    .Object.Min = iMinValue
    .Object.Max = iMaxVal

I don't understand why you have the code in the Change event.
You should set the initial values in a macro, opr example:

Private Sub Set_Spinner()
  Dim oleObj As OLEObject
  Dim iMinVal As Long
  Dim iMaxVal As Long
  Dim iSpinner1Height As Long
  Dim iSpinner1Width As Long
 
  Set oleObj = ActiveSheet.OLEObjects("SpinButton1")

  'Get height and width from defined names' RefersTo field.
  iSpinner1Height = [Spinner1Height].Height
  iSpinner1Width = [Spinner1Width].Width

  iMinVal = 5
  iMaxVal = 100 - Range("C2").Value
  With Me.OLEObjects("SpinButton1")
    .Height = iSpinner1Height
    .Width = iSpinner1Width
    .Object.Min = iMinVal
    .Object.Max = iMaxVal
  End With

End Sub

Try and comment.

 

[OaklandJim]

DanteAmor: Oh well, I'm still learning. I thought of storing the two static values for ActiveX control's .Width and .Height in RefersTo for defined names so I can refer to/use those values when the workbook reopens. It does work by [evaluating] the two names but I realized/remembered that using/setting Const values is better.

Thanks very much for the code. I'll give it a try. However I figured out a way to do what is needed by setting the changing cell's value based on conditions.

Option Explicit

Const SpinnerHeight As Long = 33
Const SpinnerWidth As Long = 19

Private Sub SpinButton1_Change()

'   Value for control's height.
    Dim iSpinner1Height As Long
    
'   Value for control's width.
    Dim iSpinner1Width As Long
    
'   Three cells: 1. static value cell, 2. changing value cell,
'   3. result value cell
    Dim rStaticCell As Range
    Dim rChangingCell As Range
    Dim rResultCell As Range
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
'   Cell containing the static input value.
    Set rStaticCell = Me.Range("C2")
    
'   Cell containing the input value that changes.
    Set rChangingCell = Me.Range("D2")
    
'   Cell containing the result.
    Set rResultCell = Me.Range("E2")
    
'   Get height from the Const SpinnerHeight
    iSpinner1Height = SpinnerHeight
    
'   Get width from the Const Spinner1Width.
    iSpinner1Width = SpinnerWidth
    
'   Reset the spinner's height and width in case of self-resizing
'   of the ActiveX control.
    With Me.OLEObjects("SpinButton1")
        .Height = iSpinner1Height
        .Width = iSpinner1Width
    End With
    
'   Check for two conditions: 1. result cell value < 0, and
'   2. static cell value + changing cell value > 100
    If rResultCell.Value < 0 _
     Then
        rChangingCell.Value = 100 - rStaticCell.Value
    ElseIf (rStaticCell.Value + rChangingCell.Value) > 100 _
     Then
        rChangingCell.Value = rChangingCell.Value - 1
    End If
    
    Application.EnableEvents = True

End Sub

Regarding self-resizing issue with ActiveX controls, is it necessary to resize the control with events such as worksheet activate or workbook open to address the issue fully?

 

[OaklandJim]

DanteAmor: Thank you for the answer to my question. I marked your answer as the solution!

The control change event does what I need. Am I not following best practices if I do it that way?

As I asked in the previous post: Regarding self-resizing issue with ActiveX controls, is it necessary to resize the control with events such as worksheet activate or workbook open to address the issue fully?

 

[DanteAmor]

As I asked in the previous post: Regarding self-resizing issue with ActiveX controls, is it necessary to resize the control with events such as worksheet activate or workbook open to address the issue fully?

I don't know exactly what you mean.

If the control is not going to resize and you need it to be the same size every time, I don't understand why you need it to be resized.
You already set the size of the control with constants.
You can set the size of the control when you open the book or simply leave it a size from the time you create it, I don't understand why you want to change a size if you are setting it to a size with constant values.
I don't see the need for lines to change or set the size of the object. Simply adjust the size you want with the mouse and you're done.
But if you want to determine it, then you can put it when you open the book, in Thiswokbook's open event, for example:

Private Sub Workbook_Open()
  With Sheets("Sheet1").OLEObjects("SpinButton1")
    .Height = 33
    .Width = 19
  End With
End Sub

 

[OaklandJim]

I am trying to help someone who posted a request to the list. I have always avoided ActiveX controls as I have read often that they don't behave well, that they resize sometimes without an apparent reason. The request seems to require the functionality of ActiveX. I tried a form button but cannot get it to do what is needed. I did not want to give a solution to someone who is not that familiar with a solution that may get effed up. So I just resize every time that the control is clicked. Is that not a good idea?

 

[DanteAmor]

resize every time that the control is clicked

You would have mentioned it from the beginning.

Check if any of these solutions work for you:

ActiveX Controls Resizing Bug

Hello, I'm having issues with a spreadsheet I've created. I've added in various Active X buttons/scroll bars/checkboxes and textboxes with macros linked to some. When someone else uses the spreadsheet (it doesn't seem to happen on mine) when any of the controls are clicked strange things start...

www.mrexcel.com

Activex Controls Changing in Size

I am using the command button, option button and checkbox activex controls in a workbook. I have set the font type and size in the properties, set the format control lock aspect ratio on and object positioning to Don't move or size with cells. With this, the activex controls will randomly get...

www.mrexcel.com

 

[OaklandJim]

My bad. Original post did have this.

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

 

[OaklandJim]

You provided what was requested. I did mark one of your posts as the solution.

 

[DanteAmor]

My bad. Original post did have this.

In post #7 I posted 2 links with possible solutions for your control size problem.
Did any of them work for you?