Set the Maximum value for a Form Scrollbar

[HangMan]

I am totally new to VBA and Macros, so it would seem I need an idiots step by step guide as to how and where to place the appropriate code.

I have a dynamic table where the number of rows change based on a user selection. I have set up a Form Scrollbar and I want to be able to change the 'Maximum Value' setting for the scrollbar using VBA.

I get the number of rows in the table using a 'MATCH' function in cell A2 to give me an Integer value.

I have looked at a number of solutions, such as:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

and many variations along similar lines, however everything I try generates an error.

The errors are either saying I need to declare a variable, but I don't know what I should be declaring, do I need to declare the scrollbar itself as a variable or the Range? or that

'it Cannot run the Macro because it may not be available in this workbook or all macros may be disabled' which they aren't because I have other macros running.

Questions

1. Should I be right clicking on the scrollbar and using the Assign Macro to add any code or should I be right clicking the sheet tab and using the 'View Code' option?
   
   
2. Could anyone please show me the code I need to add and where and how I need to add it in full, including any variables I may need to declare.

I'm sure it is simple when you know how, but when you get an error and you don't understand it, it is hard to fix...

Many thanks

[royUK]

I would use the ScrollBar from the Controls ToolBox

Please Login or Register  to view this content.

[Andy Pope]

Assuming scrollbar is of the Forms type AND it is called 'Scroll Bar 1' then the following code can be pasted into the sheet object. So right click sheet tab and view code.

Please Login or Register  to view this content.

Post example workbook if still having problems.

[HangMan]

Many thanks, though sorry to sound stupid here.

1. Should I assume that I change "A1" to the cell reference where my calculation is?
   
   
2. How do I find the name of my Scrollbar. If I right Click it and select Macro, I get 'ScrollBar24_Change' as a Macro Name, so should I assume the scrollbar is called ScrollBar24?
   
   
3. Should this be Value or Value2 in the line 'If IsNumeric(Target.Value2) Then' and if Value2, what does this refer to.
   
   
4. I added the code and even though I'm no longer getting any errors, it doesn't seem to make any difference to the Max Value the scrollbar is using.

What am I missing here?

[Andy Pope]

Change A1 to whatever cell on the sheet contains the Max value.
With the scrollbar selected the name will appear in the Name Box, next to the formula bar.

The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.

You will have to post an example as I just created a working example using the code I posted.

[HangMan]

Hi Andy

Thanks for your response. I can't post the spreadsheet I'm working on because it contains highly senstive data so I have replicated the problem in the attached spreadsheet.

[Andy Pope]

We only need to see an example. I would have posted my example but your problem is constructing a working version so it's easier if you post what you have.

BTW no attachment in your previous post.

[HangMan]

Hi Andy

Thanks for your reply. I can't post the version I'm working on as it contains highly sensitive data, so I have replicated the problem in the attached spreadsheet.

The Calculated Cell Ref is Cell H18
The Scrollbar is called Scroll Bar 1

Please Login or Register  to view this content.

I have put information on the spreadsheet itself to explain the problem.
I would be extremely grateful if you are able to point out where I am going wrong.

Many thanks

[Andy Pope]

The problem is the cell with the maximum value is not being changed. It contains a formula so only the Calculate event will trigger when it updates.
But the calculate event does not identify a Target cell.

So instead you need to move the code to the other sheet where the parameters that affect the calculated max value are.

remove existing code from Pivot sheet.
add following code to Repayments sheet.

Please Login or Register  to view this content.

[HangMan]

Hi Andy

Apologies for seeming thick. On the actual spreadsheet I'm trying to get this to work with - the sheet where the parameters that affect the calculated max value appear are on the same sheet as the scrollbar itself.

I tried adapting your revised code as follows where:

Cell containing the calculated value is Y18
Cell Range that affects the calculation is D6:D9
The Sheet is Sheet 52, tried using both Sheet 52 and ActiveSheet

Please Login or Register  to view this content.

I am still obviously missing the concept...

Does it affect things if everything appears on the same sheet?

[HangMan]

Thinking about it further it is actually slightly more complicated in that Sheet 1 uses Data Validation Drop Downs to control a Pivot Table via VBA. Sheet 2 houses the Pivot Table and a further sheet holds the scrolling table data.

Could I possibly email you the sheet privately, as it gets quite complex to explain?

[HangMan]

Hi Andy

OK, problem solved, I realsied I had to reference the sheet holding the Pivot Table rather than the sheet holding the controls that were controlling the pivot table.

Many many thanks for your patience and help, it has made a fantastic difference...