Scroll Bar and Current Value. Min and Max Values

You can not link those values to cells.

You could however use worksheet event code to determine a change in a particular cell and then update the control.

No, by using the worksheet change event

right click sheet tab and pick View Code.
You can then select the worksheet object from the left drop down at top of window.
The use right drop down to select the change event.

you can then use code something like this

Please Login or Register  to view this content.

Then when any cell content changes the event will fire, The code will test that only A1 was changed and then execute your code to determine and set properties.

so what determines when the scroll bar min and max changes??

I added a table of values with corresponding min/max.
The resulting min/max values are in to named ranges.

when C7 changes the code should update the scroller.

Please Login or Register  to view this content.

Have you even tried to make those changes yourself?

of course. thats why i sent all the test data above

the coded says min max and the result is either min or maximum not value which is what i am trying to compute . also i cant see any way to change the incremental change.

thanks in advance

Looking at help on ControlFormat would have shown the other properies than could be altered.
Even using the macro recorder would have shown you them.

Please Login or Register  to view this content.

revised code

Please Login or Register  to view this content.

There are 2 new named ranges on sheet2, which define all settings for a given control.

You code did not work for 2 reasons first the cell being checked for change was not the actual cell changing.
And now the cell that was changing contains a formula, which does not fire the change event, so you need to check the cell that drives the formula.

The event code checks for a change in either of the 2 selection cells and then updates the appropriate control with the relevant parameters.

You need to expand the event code to handle each scrollbar and changing cell.
If each scroll bar has it's own set of parameters then you also need to add named ranges on the other sheet for each set.

If you copy sheets the code should move too

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


Have you tried doing it manually? The error box will tell you the problem.

The usual way around the scroller limits is to use a helper cell and factor the resulting value in some way

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CtlFmt As ControlFormat
Dim ScrollData As Range

If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("B3")) Is Nothing Then
Set CtlFmt = Target.Parent.Shapes("Scroll Bar 2").ControlFormat
Set ScrollData = [SCROLL_INFO_1]
ElseIf Not Intersect(Target, Range("B6")) Is Nothing Then
Set CtlFmt = Target.Parent.Shapes("Scroll Bar 4").ControlFormat
Set ScrollData = [SCROLL_INFO_2]
ElseIf Not Intersect(Target, Range("B9")) Is Nothing Then
Set CtlFmt = Target.Parent.Shapes("Scroll Bar 6").ControlFormat
Set ScrollData = [SCROLL_INFO_3]
End If

If Not ScrollData Is Nothing Then
With CtlFmt
.Min = ScrollData.Cells(2, 1)
.Max = ScrollData.Cells(3, 1)
.SmallChange = ScrollData.Cells(4, 1)
.LargeChange = ScrollData.Cells(5, 1)
.Value = ScrollData.Cells(6, 1)
End With
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


the error box highlights

.Min = ScrollData.Cells(2, 1)

i have no idea what are cells 2,1 ? and i changed both cells to 1,2,3,4 and all variations still get error.

Anyone else ?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


Did you do as I suggested and try setting the values manually?

1097467.png

I will give you time to re read my previous post

you cant get any maximum number in any of the cells over 30,000.

must be solution. use if and factor up and factor down ?

my values go up to 1 million

cheese

the control will only accept positive Integer values upto 30000.

You would need to redesign sheet1 to work with acceptable values.
So new formula in F9 =E9*10

And divide all the value in sheet2!J24:J29 by 10

Your VLOOKUP formula needs to be updated to use the correct value to be compared in first column

J15: =VLOOKUP(Sheet1!C6/L13,B14:G19,2)/L13

you need to factor that down too. repeat for all formula in that table

named range scroll_info_3 is not the correct area.
the contents of scroll_info_3 are incorrect as they exceed controls limits.

the event code has not be changed to match the change in you sheet layout.

Please Login or Register  to view this content.

your link generates error.

Why not use the forum upload, like everybody else?

all of the code references to the named ranges were wrong, as it appears you altered the size those ranges

Please Login or Register  to view this content.

i pretty sure i did not change the references ! I note the above is different from the previous range. This one works after a quite a bit of jigging with the calculations . thanks,

When i try to add scroll bar 4 i dont know what is the correct Scroll Bar number (no idea how this is referenced as its not referenced anywhere i can find in the spreadsheet/named range/lookuptable)

ie scroll bar ("Scroll Bar 2") = scroll bar 1 in B4
("Scroll Bar 4") = scroll bar 2 in B5
("Scroll Bar 9") = scroll bar 3 in B6
("Scroll Bar ?") = scroll bar 4 in B7
(and next one will be ?) = scroll bar 5 in B8. etc etc

im planning to add another 10 or so......

When you add a control the name is displayed in the Name Box to the Left of the formula bar. Or you can use ALT+F10 to display selection task pane.
1097467_pic1.png

Someone did change the contents of the named ranges.
Originally it was values in J4:J9

Value 50
Min 25
Max 75
Small 5
Large 10
Value 50

and now the it's values in L4:L8
Min 0
Max 2
Increment 0
Page 10
St. Value 1

The location is not a problem as the code works relative to named range. The problem is the row position within the named range.
So the MIN value was originally in row 2. The new layout it's in row 1. Without the adjustment to the code in the Change event the wrong values were being used.

As the help says, "Use the Name Manager dialog box to work with all of the defined names and table names in the workbook". It does not do shapes or controls.

In terms of where the controls name appears it is the same as any other shape on the spread sheet.

In your workbook it says "< CHANGE TO 1 GET ERROR ". You would get a vlookup result of #N/A if you look for a value that does not exist.

Why would you set that cell to 1?

Try changing the formula from VLOOKUP to and INDEX formula.

Currently you a looking up based on the value whereas the information is based on company selected. So instead use Match to get the Company
position within the table and use those values.

You still need to incorporate the factoring values on the final output value.