Kim,

Try this, assuming your scroll bars are named Scroll Bar 1 for A1, Scroll Bar 2 for A2, Scroll Bar 3
for A3:

In a regular module, put

Option Explicit

Public myR As Range

Sub ScrollBarMaxMacro()
Application.EnableEvents = False
ActiveSheet.Shapes("Scroll Bar 1").Select
Selection.Max = 100 - Range("A2").Value - Range("A3").Value
ActiveSheet.Shapes("Scroll Bar 2").Select
Selection.Max = 100 - Range("A1").Value - Range("A3").Value
ActiveSheet.Shapes("Scroll Bar 3").Select
Selection.Max = 100 - Range("A1").Value - Range("A2").Value
myR.Select
Application.EnableEvents = True
End Sub

In the worksheet module, use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set myR = Target
End Sub

Then assign the macro ScrollBarMaxMacro to each of the three scroll bars. This assumes that cells
A1:A3 start out blank.


HTH,
Bernie
MS Excel MVP


"Kim" wrote in message
...
Hi Bernie,

Thanks. That's almost what I wanted. The only issue is the entry to cell A1
to A3 is not from entering directly to the cell, but from a scroll bar.

Example
If i move the scroll bar (maybe in cell B1) then that number will be appear
in cell A1
There will be another scroll bar in cell B2 to control the value in B2.

So basically when I move all the scroll bar, it the total should not be more
than 100. If that can be achieved, that would be great !!

But thanks for your help so far.

"Bernie Deitrick" wrote:

You need to use data validation on all three cells.

For A1: =AND(A1=0,A1<=(100-A2-A3))
For A2: =AND(A2=0,A2<=(100-A1-A3))
For A3: =AND(A3=0,A3<=(100-A1-A2))

HTH,
Bernie
MS Excel MVP


"Kim" wrote in message
...
Sorry. Maybe my question wasn't clear.

What I'm trying to do is between cell A1 and A3, you can put a maximum of
100 only.

Ie - If cell A1 is 10 and A2 is 50. Then the maximum I can enter in cell A3
is 40
If cell A2 is 50 and cell A3 is 20 then I can only enter max of 30

Regards,


"Bernie Deitrick" wrote:

Kim,

Select cell A3, the use Data / Validation... choose "Custom" and use the formula

=AND(A3=0,A3<=(100-A1-A2))

HTH,
Bernie
MS Excel MVP


"Kim" wrote in message
...
Can someone please help me with the problem below. I need a formula where I
can set a limit of figure I can input in a cell.

Example:

A B C D E
1 50
2 20
3

I want to set cell A1 to A3 that the total input cannot be more than 100. If
i enter 50 on A1 and 20 in A2 and in cell A3, they can only enter between 0 -
30.

Thanks.