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.

 

[AlphaFrog]

Unlink the cell with the formula.

Put the code from below in the worksheet code module.

Change A1 to the cell with the max formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.ScrollBar1.Max = [A1].Value
End Sub

 

[nmounir]

Unlink the cell with the formula.

Put the code from below in the worksheet code module.

Change A1 to the cell with the max formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.ScrollBar1.Max = [A1].Value
End Sub

I tried this code before as I saw your reply on an earlier thread. The issue is that the maxium value changes all the time as my data expands. The range("A1").Value works on the first time but as soon as i scroll the bar, the formula in Cell A1 disappears and the previous maxium value prevails and never changes.

 

[nmounir]

This range("A1").Value should change all the time according to the drop down list. The Code doesn't work when I use the scrolling.

 

[nmounir]

I used this code but still not working
LastRow = Range("A3").End(xlDown)-3
Shapes("ScrollBar1").Control Format.Max = Range("BI1").Value
End Sub

The Range BI1 is the Range of the scroll bar where the number changes as the bar is scrolled.
I tried to link the maxium value in the scrollbar properties to a cell but i got an error message saying "invalid property value "

 

[Gokhan Aycan]

If there are multiple sheets referenced in the formula, change event may fail?

Try this:

In a module at the top (after any "Option" statements and before the first sub)

Public FormulaValue as Variant

Then in worksheet code:

Private Sub Worksheet_Calculate()

    If FormulaValue = "" Then FormulaValue = Main.Range("A1").Value
    
    If FormulaValue <> Main.Range("A1").Value Then
        Me.ScrollBar1.Max = Main.Range("A1").Value
        FormulaValue = Main.Range("A1").Value
    End If
    
End Sub

 

[Gokhan Aycan]

Some questions though...

Does A1 have a formula? How can A1 change value by scrollbar if it has a formula?

 

[nmounir]

Some questions though...

Does A1 have a formula? How can A1 change value by scrollbar if it has a formula?

A table starts from A3 with the name of Wells drilled. If I select 2019 from down down list, I get the name of the Wells drilled in 2019 which are 40 Wells. So the names are written in cells A3 through to cell A42. If I select 2020 I get 20 names of Wells so the 2020 data will only be in cells A3 to A22 and so on. The scroll bar maxium limit needs to be adjusted according to the year selected and its number of Wells.
I had input a fornula in cell BI1 where it calculates the numbers of Wells in cell A3 to the last filled cell using the Counta function.
When I used the code provided online it worked for the very first time, but when the scrollbar is used the formula in cell BI3 Disappears and the limit seen in the first time prevails.

I am not sure if my explanation is clear

 

[nmounir]

Say I select 2019 the formula works OK and the limit is set at 40 Wells when I select 2020 the scroll bar maxium limit remains at 40 and never changes. The formula in cell BI1 also disappeared

 

[Gokhan Aycan]

Well, unlink as AlphaFrog said. And use the code I posted. Change A1 to the cell with formula (B1).

but when the scrollbar is used the formula in cell BI3 Disappears

typo?

Can you also post scrollbar code? Maybe something wrong with it?

Edit: In the code, main is the codename of sheet I tested on. Change to your sheet name.