Moonstone



MoonstoneSub MMT_Resource_Diagram(control As IRibbonControl) -md_RibbonXCall uf_MMTChart_Initialize--------------------------->MMT_Chart_CommonSub MMT_Resource_Diagram(control As IRibbonControl)Md_RibbonCall uf_MMTChart_InitializeMMT_Chart_CommonUf_MMTChartUf_MMTChartCall MMTChart_CreateMMT_Chart_Common -Call MMT_Chart_Arrival -Call MMT_Chart_Crane_LOMMT_Chart_1Delivery_SPMTMMT_Chart_1LOLO -Call MMT_Chart_SPMTMMT_Chart_1Delivery_SPMT -Call MMT_Chart_Crane -Call MultiliftMMT_Chart_2CraneOthers -Call MMT_Chart_OthersMMT_Chart_2CraneOthers -MMTChart_FinalFormatMMT_Chart_CommonAlgorithmUnique itemUnique item (using Array) Dim item As Variant, CargoCount As Long Dim myshippingList() As Variant, myship As Collection Dim LastArrival As Date, Loadin_Start As Date, BerthingPeriod As Long Set myship = New Collection ReDim myshippingList(UBound(myActivity)) For i = 1 To UBound(myActivity) myshippingList(i) = myActivity(i, 12) Next i On Error Resume Next For Each item In myshippingList myship.Add item, CStr(item) Next On Error GoTo 0Return list of Unique items in ascending orderFunction Unique_Type(myArray, ColNo) Dim i As Long, j As Long, k As Long, m As Long, n As Long Dim temp1 As Variant, anotherIteration As Boolean Set wb = ActiveWorkbook Set wsppd = wb.Worksheets("Project Data") Set WSPPe = wb.Worksheets("Resource Chart") For i = 1 To UBound(myArray) For j = 1 To UBound(myArray) If i <> j And myArray(i, ColNo) = myArray(j, ColNo) Then myArray(j, ColNo) = vbNullString End If Next j Next i n = 0 For i = 1 To UBound(myArray) If myArray(i, ColNo) <> vbNullString Then n = n + 1 ReDim Preserve UniqueType(1 To n) UniqueType(n) = myArray(i, ColNo) End If Next i Do anotherIteration = False For i = 1 To UBound(UniqueType) - 1 If UniqueType(i) > UniqueType(i + 1) Then temp1 = UniqueType(i) UniqueType(i) = UniqueType(i + 1) UniqueType(i + 1) = temp1 anotherIteration = True End If Next i Loop While anotherIteration = True Unique_Type = UniqueTypeEnd FunctionRangeCounting the number of total row and total column of data in a worksheet Finalrow = Activesheet.Cells(Rows.Count, 1).End(xlUp).Row TotalCol = Activesheet.Cells(r1 + 2, Columns.Count).End(xlToLeft).ColumnAvoid overlapping new item with existing items organized in the worksheet rangeFor k = 0 To nMax If .Cells(RowNumber - k, ADateCol).Offset(-ItemCount, 0). _Resize(ItemCount, LoadColSpan).MergeCells = False Then Exit For End If Next kUserformPosition at the previous locationDim PreTop As Double, PreLeft As Double With uf_PlotPlan .StartUpPosition = 0 PreTop = .Top: PreLeft = .Left .Repaint .Top = PreTop: .Left = PreLeft .Show End WithComboBox List Update at runtimeInitial populate ComboBox from the Addin hidden worksheet, add element to ComboBox, remove element from ComboBox, store ComboBox in the Addin hidden worksheet for next sessionInitial population of ComboBoxRead Data in the hidden worksheet and populate the ComboBox Dim Holrow as long, myHol() as variant If Holrow <= 2 Then ReDim Preserve myHol(2) myHol(1) = Format(DateSerial(Year(Date), 1, 1), dateformatX) myHol(2) = Format(DateSerial(Year(Date), 12, 25), dateformatX) Else ReDim Preserve myHol(Holrow) For i = 1 To Holrow myHol(i) = wbs7.Cells(i, 1).Value Next i End If With uf_MMTSchTemp .ComboBox1.List = myHol .show End withAdd item to ComboBox at the runtimeComboBox AfterUpdate Event ProcedurePrivate Sub ComboBox1_AfterUpdate() HolidayAddEnd SubAdd new item to ComboBoxSub HolidayAdd() Dim n As Long n = UBound(myHol) ReDim Preserve myHol(n + 1) With uf_boBox1 myHol(n + 1) = Format(.Value, dateformatX) myHol = MysortProcedure(myHol) .List = myHol .Value = "" End WithEnd SubSort unique items according to bubble sort algorithmFunction MysortProcedure(myList) Dim Mytemp() As Variant Dim anotherIteration As Boolean, Temp As Variant Dim i As Single, j As Long, n As Long With Application .DisplayAlerts = False .ScreenUpdating = False End With For i = LBound(myList) To UBound(myList) For j = LBound(myList) To UBound(myList) If i <> j And myList(i) = myList(j) Then myList(i) = vbNullString End If Next j Next i For i = 1 To UBound(myList) If myList(i) <> vbNullString Then n = n + 1 ReDim Preserve Mytemp(n) Mytemp(n) = myList(i) End If Next i n = UBound(Mytemp) If n > 1 Then Do anotherIteration = False For i = 1 To UBound(Mytemp) - 1 If Mytemp(i) > Mytemp(i + 1) Then Temp = Mytemp(i) Mytemp(i) = Mytemp(i + 1) Mytemp(i + 1) = Temp anotherIteration = True End If Next i Loop While anotherIteration = True End If MysortProcedure = MytempEnd FunctionRemove item which is selected by clickingComboBox Click Event Procedure, highlighting the item to be removedPrivate Sub ComboBox1_Click() HolidayDeleteEnd Sub Remove the clicked item (as selected) form the ComboBoxSub HolidayDelete() Dim i As Long With uf_boBox1 For i = 1 To .ListCount If .List(i - 1) = .Value Then .RemoveItem (i - 1) Exit For End If Next i .Value = "" myHol=.list End WithEnd SubStore list of ComboBox in the Addin hidden worksheet for next sessionClose Commanbutton event before Userform is unloaded.Private Sub CommandButton2_Click() Call HolidaySave Unload MeEnd SubStore ComboBox list in the hidden worksheet of AddIn.Sub HolidaySave() Dim i As Long Set wbc = ThisWorkbook Set wbs7 = wbc.Worksheets("Reference") wbs7.Cells.Clear For i = 1 To uf_boBox1.ListCount wbs7.Cells(i, 1) = uf_boBox1.List(i - 1) Next iEnd SubBitness 32 vs 64 bit machine for Window API'for 64-bit Excel 2010 and later#If VBA7 And Win64 Then Declare PtrSafe Function GetWindowsDirectoryA Lib "kernel32" _ (ByVal lpBuffer As String, ByVal nSize As Long) As Long#Else Declare Function GetWindowsDirectoryA Lib "kernel32" _ (ByVal lpBuffer As String, ByVal nSize As Long) As Long#End IfRegional Date FormatOption ExplicitOption Base 1Option Compare Text'for 64-bit Excel 2010 and later#If VBA7 And Win64 Then Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" _ (ByVal Locale As Long, ByVal LCType As Long, _ ByVal lpLCData As String, ByVal cchData As Long) As Long Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long#Else Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" _ (ByVal Locale As Long, ByVal LCType As Long, _ ByVal lpLCData As String, ByVal cchData As Long) As Long Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long#End IfPrivate Const LOCALE_SSHORTDATE = &H1FPublic dateformat As Variant‘**********************************************************************************Private Sub DateFormatProcedure() Dim SDateFormat As String * 9 Dim lLocale As Long Dim LocaleInfo As Long 'System date format using API functions lLocale = GetUserDefaultLCID() LocaleInfo = GetLocaleInfo(lLocale, &H1F, SDateFormat, 9) SDateFormat = Replace(SDateFormat, Chr(0), "") 'Regional date format Dim DateOrder As String Dim DateSeparator As String Dim RDateFormat As String With Application DateSeparator = .International(xlDateSeparator) Select Case .International(xlDateOrder) Case Is = 0 DateOrder = "month-day-year" RDateFormat = "mmm" & DateSeparator & "dd" & DateSeparator & "yy" Case Is = 1 DateOrder = "day-month-year" RDateFormat = "dd" & DateSeparator & "mmm" & DateSeparator & "yy" Case Is = 2 DateOrder = "year-month-day" RDateFormat = "yy" & DateSeparator & "mm" & DateSeparator & "dd" Case Else DateOrder = "Error" End Select End With dateformat = RDateFormat End IfEnd SubRemove objectsCell contentsActivesheet.Cells.ClearComboBox Item With uf_boBox1 For i = 1 To .ListCount If .List(i - 1) = .Value Then .RemoveItem (i - 1) Exit For End If Next i .Value = "" myHol=.list End WithClear ArrayErase MMT_Array()Redim Preserve MMT_ArrayCreate Legend using shapeSub LegendMaking() With Application .ScreenUpdating = False .DisplayAlerts = False End With Set wb = ActiveWorkbook Set ws = wb.Worksheets("Construction Sch") With ws.Cells(4, 19) ws.Shapes.AddShape(msoShapeRectangle, Left:=.Left, Top:=.Top, _ Width:=.Resize(1, 20).Width, Height:=.Resize(10, 1).Height).Select With Selection .Name = "legend" .ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255) End With End With With ws.Cells(5, 20) ws.Shapes.AddShape(msoShapeRectangle, Left:=.Left, Top:=.Top + 10, Width:=.Resize(1, 5).Width, _ Height:=.Resize(1, 1).Height / 2).Select With Selection .Name = "critical" .ShapeRange.Fill.ForeColor.RGB = RGB(scb2, scb3, scb4) End With End With With ws.Cells(7, 20) ws.Shapes.AddShape(msoShapeRectangle, Left:=.Left, Top:=.Top + 5, Width:=.Resize(1, 5).Width, _ Height:=.Resize(1, 1).Height / 2).Select With Selection .Name = "noncritical" .ShapeRange.Fill.ForeColor.RGB = RGB(scb5, scb6, scb7) End With End With With ws.Cells(9, 20) ws.Shapes.AddShape(msoShapeDiamond, Left:=.Left, Top:=.Top - 5, Width:=15, Height:=15).Select With Selection .Name = "arrivalP" .ShapeRange.Fill.ForeColor.RGB = RGB(scb8, scb9, scb10) End With End With With ws.Cells(11, 20) ws.Shapes.AddShape(msoShapeDiamond, Left:=.Left, Top:=.Top - 5, Width:=15, Height:=15).Select With Selection .Name = "arrivalL" .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 0) End With End With With ws.Cells(13, 20) ws.Shapes.AddShape(msoShapeRectangle, Left:=.Left, Top:=.Top, Width:=.Resize(1, 5).Width, _ Height:=.Resize(1, 1).Height / 2).Select With Selection .Name = "loadin" .ShapeRange.Fill.ForeColor.RGB = RGB(200, 200, 200) End With End With ActiveSheet.Shapes.Range(Array("Legend")).Select Selection.ShapeRange.TextFrame2.TextRange.Characters.Text = Space(10) & "Legend" & _ Chr(13) & Space(25) & "Critical" & _ Chr(13) & "" & Chr(13) & Space(25) & "Non-critical" & _ Chr(13) & "" & Chr(13) & Space(25) & "Arrival (plan)" & _ Chr(13) & "" & Chr(13) & Space(25) & "Arrival (Last)" & _ Chr(13) & "" & Chr(13) & Space(25) & "Load-in" With Selection.ShapeRange.TextFrame2.TextRange.Characters(1, 1) .ParagraphFormat.FirstLineIndent = 0 With .Font .size = 14 .Fill.Visible = msoTrue .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1 .Fill.ForeColor.TintAndShade = 0 .Fill.ForeColor.Brightness = 0 .Fill.Transparency = 0 .Fill.Solid End With End With ActiveSheet.Shapes.Range(Array("legend", "critical", "noncritical", "arrivalP", "arrivalL", "loadin")).Select Selection.ShapeRange.Group.Select With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorText1 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0 .Transparency = 0 .Solid End WithEnd SubWeb code&#60 < ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download