Help modify code

[noelmus]

Hi,
I have a worksheet showing 3 tanks where they have not the same capacity and by help I got a code (below). Current code shows that the 3 tanks have a capacity of 400000. My problem is that only Tank A has that capacity. Capacity of Tank B is 272000 and Tank C is 200000.
Will you please someone help me by modify the code below.
Thanks in advance

[COLOR=#000000][FONT='inherit']Option Explicit[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Private Sub AdjustTank(ByVal CurLevel As Double, ByVal TankID As String, _[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Optional MaxLevel As Double = 400000)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Dim Tank As Shape, Frame As Shape, Level As Shape, Number As Shape[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'Refer to he Tank shape[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Set Tank = Me.Shapes("Tank" & TankID)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'Refer to the shapes inside[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Set Frame = Tank.GroupItems("FrameA")[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Set Level = Tank.GroupItems("LevelA")[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Set Number = Tank.GroupItems("NumberA")[/FONT][/COLOR]

[COLOR=#000000][FONT='inherit']  'Be sure the new level is not above the max level[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  If CurLevel > MaxLevel Then CurLevel = MaxLevel[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'Write the new level number into the TextBox[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Number.TextFrame2.TextRange.Text = Format(CurLevel, "#,##0")[/FONT][/COLOR]

[COLOR=#000000][FONT='inherit']  'Calculate the height of the level according to the max. level[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Level.height = (Frame.height - 2) / MaxLevel * CurLevel[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'Move the level to the bottom[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Level.Top = Frame.Top + Frame.height - Level.height - 1[/FONT][/COLOR]

[COLOR=#000000][FONT='inherit']  'Move the number into the middle[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Number.Left = Frame.Left + Frame.Width / 2 - Number.Width / 2[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'And below the level line[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Number.Top = Level.Top - 3[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  'If the number is too low move it to the lowest possible position[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  If Number.Top + Number.height > Frame.Top + Frame.height Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Number.Top = Level.Top - Number.height + 3[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    If CurLevel < 80000 Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']       Level.Fill.ForeColor.RGB = RGB(255, 228, 225)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    ElseIf CurLevel >= 80000 And CurLevel < 400000 Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']       Level.Fill.ForeColor.RGB = RGB(135, 206, 250)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Else[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        Level.Fill.ForeColor.RGB = RGB(152, 251, 152)[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Private Sub Worksheet_Calculate()[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Static LastValueU, LastValueS, LastValueA[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    Dim shp As Shape, height As Double[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With Range("U25")[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If LastValueU <> .Value Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            AdjustTank .Value, "A"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            LastValueU = .Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With Range("S25")[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If LastValueS <> .Value Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            AdjustTank .Value, "B"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            LastValueS = .Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    With Range("A29")[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        If LastValueA <> .Value Then[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            AdjustTank .Value, "C"[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']            LastValueA = .Value[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']        End If[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']    End With[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']
[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']Sub test()[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Me.Shapes("BackGroundA").Left = Me.Shapes("FrameA").Left[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']  Me.Shapes("BackGroundA").Top = Me.Shapes("FrameA").Top[/FONT][/COLOR]
[COLOR=#000000][FONT='inherit']End Sub[/FONT][/COLOR]

 

[oldbrewer]

capacities

tank1

tank2

tank3

OVERFLOW

ladst rownum

400000

272000

200000

12

current levels

211000

232000

155000

click here to run macro

date

addvol

tankid

ok to add

rownum

helper

01/01/2019

35000

tank2

YES

11

7

02/01/2019

70000

tank3

NO

12

8

this macro handles each potential top up

35000 was added to tank 2 correctly

but trying to add 70000 to tank 3 failed

zand the overflow signal appeared in L1

Sub Macro4()

'

' Macro4 Macro

' Macro recorded 23/01/2019 by bob

'

'

If Cells(Cells(3, 2), 4) = "YES" Then GoTo 100 Else GoTo 200

100 Cells(7, Cells(Cells(3, 2), 7)) = Cells(7, Cells(Cells(3, 2), 7)) + Cells(Cells(3, 2), 2)

GoTo 500

200 Cells(1, 12) = "OVERFLOW"

500 End Sub

where it says click here to run macro

put in a suitable shape and assign the macro to it

<colgroup><col><col span="2"><col><col span="15"></colgroup><tbody>
</tbody>

 

[noelmus]

Hi oldbrewer,
Thanks for your reply, but the tanks have a lot of information added to them such as add, subtract, gains and losses.

 

[oldbrewer]

ok - this approach tells you what is added - if you added a negative 5000 to tank 1 it would take 5000 off the current level - the whole table enables you to analyse data by date range - probably using a pivot table - you could make charts of each tank level over time - but good luck !

 

[noelmus]

Hi,
Still thanks a lot, may be I will use your information in the future.

Regards