Sub TBS12_BackupRestoreColor()
'Backup Color(ColorColumnFrom, ColorColumnTo,ColorNumber
Dim BackupColorArr() As Variant
Call TBF46_BackupColor(BackupColorArr)
'Clear Color
Dim WS As Worksheet
Set WS = ThisWorkbook.ActiveSheet
Dim TmpRange As Range
Set TmpRange = WS.Range(WS.Cells(5, 6), WS.Cells(77, 36))
TmpRange.Interior.Color = 16777215
Call TBS08_DayoffFillCellColor
'Restore Color
Call TBF47_RestoreColor(BackupColorArr)
End Sub
Sub TBS13_CreatNewSheet()
'New or Edit?
Dim MsgBoxResult As VbMsgBoxResult
Dim MsgPrompt As String
Dim MsgButton As VbMsgBoxStyle
Dim MsgTitle As String
MsgPrompt = "‚Í‚¢F‘S‚ăf[ƒ^‚ð휂µ‚ÄV‹KƒV[ƒg‚ð쬂µ‚Ü‚·B" & vbNewLine & _
"‚¢‚¢‚¦Fƒf[ƒ^‚ð‚»‚Ì‚Ü‚ÜB"
MsgButton = vbYesNoCancel + vbSystemModal
MsgTitle = "ƒV[ƒgV‹KE•ÒW"
MsgBoxResult = MsgBox(MsgPrompt, MsgButton, MsgTitle)
Select Case MsgBoxResult
Case vbYes
Call TBF48_CreatNewSheet("New")
Case vbNo
Call TBF48_CreatNewSheet("Edit")
Case vbCancel
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function TBF41_SetFormular(SUS As Worksheet)
Dim RNo As Integer
Dim CNo As Integer
Dim GouKei As Double
Dim GaiChuu As Double
Dim JinIn As Double
Dim TrungBinhNguoi As Variant
Dim TBNguoiThucTe As Variant
Dim TBNguoiMucTieu As Variant
Dim ChuuKi As String
Dim PhanTram As Variant
'If value="" then value=0
For RNo = 5 To 14
Select Case RNo
Case 5, 6, 7, 11, 12, 13
For CNo = 48 To 50
If SUS.Cells(RNo, CNo).Value = "" Then SUS.Cells(RNo, CNo) = 0
Next
End Select
Next
For RNo = 4 To 14
Select Case RNo
Case 4, 10 'chuuki
ChuuKi = SUS.Cells(RNo + 1, 47).Value
SUS.Range(SUS.Cells(RNo, 48), SUS.Cells(RNo, 50)).Value = Left(ChuuKi, 3)
SUS.Cells(RNo, 51).Value = Mid(ChuuKi, 5, 5)
SUS.Cells(RNo, 52).Value = Right(ChuuKi, 5)
Case 5, 6, 7, 11, 12, 13 'Shanai,Kyoto
SUS.Cells(RNo, 51).Value = SUS.Cells(RNo, 48).Value + SUS.Cells(RNo, 49).Value
SUS.Cells(RNo, 52).Value = SUS.Cells(RNo, 48).Value + SUS.Cells(RNo, 49).Value + SUS.Cells(RNo, 50).Value
Case 8, 14 '(xx.x/hito)
For CNo = 48 To 52
GouKei = SUS.Cells(RNo - 3, CNo).Value
GaiChuu = SUS.Cells(RNo - 2, CNo).Value
JinIn = SUS.Cells(RNo - 1, CNo).Value
If JinIn = 0 Then
TrungBinhNguoi = 0
Else
TrungBinhNguoi = (GouKei - GaiChuu) / JinIn
If TrungBinhNguoi >= 100 Then
TrungBinhNguoi = Format(TrungBinhNguoi, "000.0")
Else
TrungBinhNguoi = Format(TrungBinhNguoi, "00.0")
End If
End If
SUS.Cells(RNo, CNo).Value = TrungBinhNguoi
Next
End Select
Next
'%
RNo = 9
For CNo = 48 To 52
TBNguoiThucTe = SUS.Cells(RNo - 1, CNo).Value
TBNguoiMucTieu = SUS.Cells(RNo + 5, CNo).Value
TBNguoiThucTe = CDbl(TBNguoiThucTe)
TBNguoiMucTieu = CDbl(TBNguoiMucTieu)
If TBNguoiMucTieu = 0 Then
PhanTram = 0
Else
PhanTram = TBNguoiThucTe / TBNguoiMucTieu * 100
If PhanTram >= 100 Then
PhanTram = Format(PhanTram, "000.0")
Else
PhanTram = Format(PhanTram, "00.0")
End If
End If
SUS.Cells(RNo, CNo).Value = PhanTram
Next
End Function
Function TBF42_CreatNote(WS As Worksheet, SUS As Worksheet)
'Xac dinh chieu dai text lon nhat va co dau cham khong
Dim RowNo As Integer
Dim ColumnNo As Integer
Dim MaxLengthText_GouKei As String
Dim MaxLengthText_GaiChuu As String
Dim MaxLengthText_JinIn As String
Dim MaxLengthText_TrungBinh As String
Dim TmpText As String
For ColumnNo = 48 To 52
For RowNo = 5 To 14
TmpText = SUS.Cells(RowNo, ColumnNo).Value
Select Case RowNo
Case 5, 11
If Len(TmpText) > Len(MaxLengthText_GouKei) Then MaxLengthText_GouKei = TmpText
Case 6, 12
If Len(TmpText) > Len(MaxLengthText_GaiChuu) Then MaxLengthText_GaiChuu = TmpText
Case 7, 13
If Len(TmpText) > Len(MaxLengthText_JinIn) Then MaxLengthText_JinIn = TmpText
Case 8, 14
If Len(TmpText) > Len(MaxLengthText_TrungBinh) Then MaxLengthText_TrungBinh = TmpText
End Select
Next
Next
'Define MaxSpaceQty
Dim MaxSpaceQty_GouKei As Integer
Dim MaxSpaceQty_GaiChuu As Integer
Dim MaxSpaceQty_JinIn As Integer
Dim MaxSpaceQty_TrungBinh As Integer
MaxSpaceQty_GouKei = TBF44_ConvertTextLength2SpaceQty(MaxLengthText_GouKei)
MaxSpaceQty_GaiChuu = TBF44_ConvertTextLength2SpaceQty(MaxLengthText_GaiChuu)
MaxSpaceQty_JinIn = TBF44_ConvertTextLength2SpaceQty(MaxLengthText_JinIn)
MaxSpaceQty_TrungBinh = TBF44_ConvertTextLength2SpaceQty(MaxLengthText_TrungBinh)
'Creat BeforeTextArr
Dim BeforeTextArr(5 To 14, 48 To 52) As String
Dim TmpSpaceQty As Integer
Dim AddSpaceQty As Integer
Dim BeforeText As String
Dim TmpNote As String
For ColumnNo = 48 To 52
For RowNo = 5 To 14
TmpText = SUS.Cells(RowNo, ColumnNo).Value
TmpSpaceQty = TBF44_ConvertTextLength2SpaceQty(TmpText)
Select Case RowNo
Case 5, 11
AddSpaceQty = MaxSpaceQty_GouKei - TmpSpaceQty
If AddSpaceQty = 0 Then
BeforeText = SUS.Cells(RowNo - 1, ColumnNo).Value & TmpText
Else
BeforeText = SUS.Cells(RowNo - 1, ColumnNo).Value & TmpText & Space(AddSpaceQty)
End If
Case 6, 12
AddSpaceQty = MaxSpaceQty_GaiChuu - TmpSpaceQty
If AddSpaceQty = 0 Then
BeforeText = SUS.Cells(RowNo, 47).Value & TmpText
Else
BeforeText = SUS.Cells(RowNo, 47).Value & TmpText & Space(AddSpaceQty)
End If
Case 7, 13
AddSpaceQty = MaxSpaceQty_JinIn - TmpSpaceQty
TmpNote = SUS.Cells(RowNo, 47).Value
If AddSpaceQty = 0 Then
BeforeText = Replace(TmpNote, "xx.x", TmpText)
Else
BeforeText = Replace(TmpNote, "xx.x", TmpText) & Space(AddSpaceQty)
End If
Case 8, 14
AddSpaceQty = MaxSpaceQty_TrungBinh - TmpSpaceQty
TmpNote = SUS.Cells(RowNo, 47).Value
If AddSpaceQty = 0 Then
BeforeText = Replace(TmpNote, "xx.x", TmpText)
Else
BeforeText = Replace(TmpNote, "xx.x", TmpText) & Space(AddSpaceQty)
End If
Case 9
BeforeText = TmpText & "%"
Case 10
BeforeText = ""
End Select
BeforeTextArr(RowNo, ColumnNo) = BeforeText
Next
Next
'Creat AfterTextArr
Dim AfterTextArr(5 To 14, 48 To 52) As String
Dim SpaceNhat As String: SpaceNhat = "@"
Dim AfterText As String
For ColumnNo = 48 To 52
For RowNo = 5 To 14
BeforeText = BeforeTextArr(RowNo, ColumnNo)
Select Case RowNo
Case 5, 11
Select Case ColumnNo
Case 48, 49, 50
AfterText = BeforeText & SpaceNhat & SpaceNhat & SpaceNhat
Case 51, 52
AfterText = BeforeText & SpaceNhat
End Select
Case 6, 7, 8, 12, 13
AfterText = BeforeText & SpaceNhat
Case 9, 10, 14
AfterText = BeforeText
End Select
AfterTextArr(RowNo, ColumnNo) = AfterText
Next
Next
'Creat NoteArr(MeiRowNo,GouKeiRowNo,MokuRowNo,MeiNote,GouKeiNote,MokuNote)
Dim NoteArr(2 To 6, 0 To 5) As Variant
Dim MeiRowNo As Integer
Dim GouKeiRowNo As Integer
Dim MokuRowNo As Integer
Dim MeiNote As String
Dim GouKeiNote As String
Dim MokuNote As String
For i = 2 To 6
MeiRowNo = SUS.Cells(i, 43).Value
GouKeiRowNo = SUS.Cells(i, 44).Value
MokuRowNo = SUS.Cells(i, 45).Value
NoteArr(i, 0) = MeiRowNo
NoteArr(i, 1) = GouKeiRowNo
NoteArr(i, 2) = MokuRowNo
Next
For ColumnNo = 48 To 52
MeiNote = SUS.Cells(7, ColumnNo).Value
MeiNote = MeiNote & Tukhoa5
NoteArr(ColumnNo - 46, 3) = MeiNote
Next
For ColumnNo = 48 To 52
GouKeiNote = ""
MokuNote = ""
For RowNo = 5 To 9
AfterText = AfterTextArr(RowNo, ColumnNo)
GouKeiNote = GouKeiNote & AfterText
Next
For RowNo = 11 To 14
AfterText = AfterTextArr(RowNo, ColumnNo)
MokuNote = MokuNote & AfterText
Next
NoteArr(ColumnNo - 46, 4) = GouKeiNote
NoteArr(ColumnNo - 46, 5) = MokuNote
Next
'Write to WS sheet
For i = 2 To 6
MeiRowNo = NoteArr(i, 0)
GouKeiRowNo = NoteArr(i, 1)
MokuRowNo = NoteArr(i, 2)
MeiNote = NoteArr(i, 3)
GouKeiNote = NoteArr(i, 4)
MokuNote = NoteArr(i, 5)
If MeiRowNo > 0 Then WS.Cells(MeiRowNo, 3).Value = MeiNote
If GouKeiRowNo > 0 Then WS.Cells(GouKeiRowNo, 2).Value = GouKeiNote
If MokuRowNo > 0 Then WS.Cells(MokuRowNo, 2).Value = MokuNote
Next
End Function
Function TBF44_ConvertTextLength2SpaceQty(InputText As String) As Integer
Dim CoDauCham As Integer
Dim TextLength As Integer
Dim SpaceQty As Integer
If InStr(InputText, ".") <> 0 Then CoDauCham = 1
TextLength = Len(InputText)
SpaceQty = TextLength * 2 - CoDauCham
TBF44_ConvertTextLength2SpaceQty = SpaceQty
End Function
Function TBF45_DefineColorColumnFrom_ColorColumnTo_ColorNumber(WS As Worksheet, RowNo As Integer, ColorColumnFrom As Integer, ColorColumnTo As Integer, ColorNumber As Long)
'Setting
Dim ColorRefRow As Integer: ColorRefRow = 4
Dim ColumnFrom As Integer: ColumnFrom = 6
Dim ColumnTo As Integer: ColumnTo = 36
'Define ColorInder,Comment,ColorColumnFrom
Dim CheckColor As Variant
Dim i As Integer
For i = ColumnFrom To ColumnTo
CheckColor = TBF1810_CheckSatSunDayOffColorIndex(WS, RowNo, i)
If VarType(CheckColor) <> vbBoolean Then
ColorNumber = CheckColor
ColorColumnFrom = i
GoTo ExitFor
End If
Next
ExitFor:
'Define ColorColumnTo
For i = ColumnTo To ColumnFrom Step -1
CheckColor = TBF1810_CheckSatSunDayOffColorIndex(WS, RowNo, i)
If VarType(CheckColor) <> vbBoolean Then
ColorColumnTo = i
GoTo ExitFor2
End If
Next
ExitFor2:
End Function
Function TBF46_BackupColor(BackupColorArr() As Variant)
'Define DataSheet and SetupSheet
Dim WS As Worksheet
Set WS = ThisWorkbook.ActiveSheet
'Define RowFrom, RowTo, ColumnFrom, ColumnTo
Dim RowFrom As Integer: RowFrom = 5
Dim RowTo As Integer
Dim ColumnFrom As Integer: ColumnFrom = 6
Dim ColumnTo As Integer: ColumnTo = 36
Call TBF43_DefineRowEndNo_ByColor(WS, RowTo, 2)
'Backup Color(ColorColumnFrom, ColorColumnTo,ColorNumber
ReDim BackupColorArr(RowFrom To RowTo, 0 To 2)
Dim RowNo As Integer
Dim ColorColumnFrom As Integer
Dim ColorColumnTo As Integer
Dim ColorNumber As Long
For RowNo = RowFrom To RowTo
ColorColumnFrom = 0
ColorColumnTo = 0
ColorNumber = 0
Call TBF45_DefineColorColumnFrom_ColorColumnTo_ColorNumber(WS, RowNo, ColorColumnFrom, ColorColumnTo, ColorNumber)
BackupColorArr(RowNo, 0) = ColorColumnFrom
BackupColorArr(RowNo, 1) = ColorColumnTo
BackupColorArr(RowNo, 2) = ColorNumber
Next
End Function
Function TBF47_RestoreColor(BackupColorArr() As Variant)
'Define DataSheet and SetupSheet
Dim WS As Worksheet
Set WS = ThisWorkbook.ActiveSheet
Dim RowFrom As Integer: RowFrom = 5
Dim RowTo As Integer
Dim ColumnFrom As Integer: ColumnFrom = 6
Dim ColumnTo As Integer: ColumnTo = 36
Call TBF43_DefineRowEndNo_ByColor(WS, RowTo, 2)
Dim RefValue As String
Dim RefRow As Integer: RefRow = 4
Dim RowNo As Integer
Dim ColumnNo As Integer
Dim FillColorRange As Range
Dim ColorColumnFrom As Integer
Dim ColorColumnTo As Integer
Dim ColorNumber As Long
For RowNo = RowFrom To RowTo
ColorColumnFrom = BackupColorArr(RowNo, 0)
ColorColumnTo = BackupColorArr(RowNo, 1)
ColorNumber = BackupColorArr(RowNo, 2)
If ColorNumber <> 0 Then
For ColumnNo = ColorColumnFrom To ColorColumnTo
RefValue = WS.Cells(RefRow, ColumnNo).Value
If RefValue <> "" Then
Set FillColorRange = WS.Cells(RowNo, ColumnNo)
FillColorRange.Interior.Color = ColorNumber
End If
Next
End If
Next
End Function
Function TBF48_CreatNewSheet(NewEditMode As String)
'Get Filename from Inputbox
'InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
Dim StrTitle As String
Dim StrDefautValue As String
Dim StrPrompt As String
StrPrompt = "V‹KƒV[ƒg–¼‚ð‹L“ü‚µ‚ĉº‚³‚¢B"
Dim InputBoxType As Integer: InputBoxType = 2 'chuoi ki tu
Dim StrResult As Variant
StrResult = Application.InputBox(StrPrompt, StrTitle, StrDefautValue, , , , , InputBoxType)
If VarType(StrResult) = vbBoolean Then StrResult = ""
'Creat NewSheet
Dim BeforeSheet As Worksheet
Dim OriginSheet As Worksheet
Dim AfterSheet As Worksheet
Set OriginSheet = ThisWorkbook.Sheets("Œ´–{")
Set BeforeSheet = ThisWorkbook.ActiveSheet
Select Case NewEditMode
Case "New"
OriginSheet.Copy After:=Sheets(Sheets.Count)
Case "Edit"
BeforeSheet.Copy After:=Sheets(Sheets.Count)
End Select
Set AfterSheet = Sheets(Sheets.Count)
AfterSheet.Visible = xlSheetVisible
If StrResult <> "" Then
AfterSheet.Name = StrResult
End If
AfterSheet.Select
BeforeSheet.Visible = xlSheetHidden
End Function