Dynamic ActiveX Control Maxium Value

[nmounir]

How to dynamically change the maxium value of a ActiveX control Scroll bar? As new data are being added or removed, the scroll bar maximum value has to change accordingly. If I link the maxium value to a cell that has a formula visa VBA, it works the first time but then the formula disappears when u use the scroll bar.

I looked online with no answers for a dynamic maxium value based on changing dynamic row numbers.

Your help is greatly appreciated.

 

[nmounir]

I tried this code

Private Sub ScrollBar1_Change()

Dim LastRow As Integer

LastRow = Range("A3").End(xlDown).Row - 3

Shapes("ScrollBar1").ControlFormat.Min = 1

Shapes("Scrollbar1").ControlFormat.Max = LastRow

End Sub

this code works but it sets the maximum value correctly but never allows me to go back to the minimum value. it gets the scroll bar stuck to the maximum value only, however, when the data changes (expands or contracts) the maximum value changes correctly

 

[Gokhan Aycan]

On a new workbook can you try the following?

It even resizes the scrollbar according to data rows, just for kicks. Only formula is B1

= COUNTA(A:A)

Module1:

Option Explicit

Public FormulaValue As Variant

Sheet1:

Option Explicit

Private Sub ScrollBar1_Change()
    
    Range("A2").Offset(ScrollBar1.Value).Select
    Range("F4").Value = ScrollBar1.Value
    Debug.Print ScrollBar1.Value
    
End Sub

Private Sub Worksheet_Calculate()

    If FormulaValue = "" Then FormulaValue = Range("B1").Value
   
    If FormulaValue <> Range("B1").Value Then
        Me.ScrollBar1.Max = Range("B1").Value
        FormulaValue = Range("B1").Value
        Me.ScrollBar1.Height = Range("A3").Resize(Range("B1")).Height + 24
        Range("E4").Value = ScrollBar1.Min
        Range("G4").Value = ScrollBar1.Max
    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print ScrollBar1.Min, ScrollBar1.Value, ScrollBar1.Max
End Sub

 

[nmounir]

Thank you for your help and details.

I copied and pasted everything in a new sheet and I could see that the scroll bar limit exceeded 18.

What I am trying to do is to limit the maximum limit to 18 while allowing the scroll bar to go back to 1 without being stuck at 18

did I do something wrong here? I literally copied and pasted your codes.

 

[Gokhan Aycan]

I believe I know where the issue is. The only time Value is greater than Max would be when FormulaValue has not been calculated or assigned yet. If you click "Reset" in VBA, first time workbook is opened or if there is an error and you click "End" (or issue End in code).

You can add the following to Workbook_Open event code:

Option Explicit

Private Sub Workbook_Open()

    If FormulaValue = "" Then FormulaValue = Range("B1").Value

End Sub

I don't understand what you mean by scrollbar getting stuck at 18 though. Max is assigned by the number of elements in column A, considering there are no headers or anything else in the test sheet on column A.

 

[Gokhan Aycan]

Do you mean when a new year is selected you want scrollbar value to be set to 1?

 

[nmounir]

Do you mean when a new year is selected you want scrollbar value to be set to 1?

when I select a new year using the code I came up with, the scrollbar gets stuck at the maximum value set by the year selected previously. I can scroll back to 1 with the scrollbar but I cannot scroll above the maximum value set of the year prevuosily selected. When checking the properties, the maximum value of the previously selected year has been input. I didn't input it, it is just had been input by Excel.

 

[Gokhan Aycan]

Do you have any "Application.EnableEvents=True/False" in that piece of code?
And yes we are setting the max programmatically with this line: Me.ScrollBar1.Max = Range("B1").Value

 

[nmounir]

Hello Gokhan,

I worked out a code that works fine now but it doesn't get triggered unless make a change to any cell. in other words, I have a scroll bar and a spin button. The maximum values depends on each others. I created a formula and included it in the code. However, clicking on the scrollbar or the spin button doesn't trigger the code unless I change a value in any cell (say in cell A2) by changing "Well name to well or anything else"
I made a sample workbook and would like to share it here. I would be grateful if you could help me find a way how to trigger the code when the scrollbar / Spin button are clicked
Any idea how to share the workbook in here?

 

[Gokhan Aycan]

I don't think you can share it here (I am still new to these forums), maybe put in any file sharing service, google drive, dropbox etc?

 

[nmounir]

Please see below link. Please let me know if u were able to download it.

Dropbox - File Deleted - Simplify your life

www.dropbox.com