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
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
Code:
[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]
Last edited by a moderator:
