Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll bar to change value in cell - PART 2
Hi - Firstly sorry this is so long...Secondly thanks in advance to
anyone who can help. I have a scroll bar on Sheet3 that controls the value in a cell F3 on Sheet 1. This value is then used to calculate the value of variables "invS" and "netS". The value of one affects invS affects the value of netS. . ie the more money you invest the less net income you will have. Values of these variables are then displayed in cells B11 and B12 on Sheet 2. Sheet 3 contains a stacked bar graphs that watch the cells B11 and B12 on sheet 2. B11 is the top part of the graph and B12 the bottom. As the scroll bar varies the value of "invS", "netS" SHOULD change. BUT IT DOESNT DO SO UNTIL I TAB OUT OF THE CELL. I need the re-calculation of "netS" and "invS" to occur every time the scroll bar is moved and feed back to the chart as I do it. can anyone help? I can email the file necessary. The following code changes the value in the Sheet1 F3 successfully however THIS IS NOT THE CELL BEING MONITERED BY THE GRAPH Therefore the change has no effect on the graph until I have tabbed out of the cell and the other variables are calculated POSSIBLE ANSWER: Im thinking maybe I just have to get the curser to move to the adjacent cell then back after each increment/decrement?????? If Im on the right track how is this done if the sheet is not the ACTIVE worksheet? ie the curser is on Sheet 1 while I view Sheet 3 (thanks to len for the code) Private Sub ScrollBar1_Change() 'this updates H3 with the scroll bar value Worksheets("Info").cbxspe = "$" Worksheets("Info").Range("F3") = ScrollBar1.Value End Sub Private Sub Worksheet_Activate() 'sets the scroll bar values on activation 'assumes range "AvailableIncome" is named ScrollBar1.Min = 0 'or whatever you want it to be ScrollBar1.Max = Worksheets("Info").Range("B3").Value ScrollBar1.SmallChange = ScrollBar1.Max / 100 ScrollBar1.LargeChange = ScrollBar1.Max / 10 End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'change the scroll bar max to the available income 'assumes range "AvailableIncome" is named 'if availableincome is a formula, remove the if...then/endif If Target = Worksheets("Info").Range("F3").Value Then ScrollBar1.Max = Worksheets("Info").Range("F3").Value ScrollBar1.SmallChange = ScrollBar1.Max / 100 ScrollBar1.LargeChange = ScrollBar1.Max / 10 End If 'adjust scroll bar position to reflect If Worksheets("Info").Range("H3").Value = ScrollBar1.Min And _ Worksheets("Info").Range("H3").Value <= ScrollBar1.Max Then ScrollBar1.Value = Worksheets("Info").Range("H3").Value End If End Sub - Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I put a scroll bar within a cell to change its contents? | Excel Discussion (Misc queries) | |||
Macro to change part of cell | Excel Discussion (Misc queries) | |||
Can the scroll wheel be used to change cell values | Excel Discussion (Misc queries) | |||
Cannot change part of a merged cell | Excel Programming | |||
Keeping one part of a formula same, but change other cell ref? | Excel Discussion (Misc queries) |