'Get English Name of Column PublicFunction Fun_GetEngName(ByVal argColumn AsInteger) AsString Dim strEngName AsString Dim iNum, iMod AsInteger
iNum = argColumn \ 26 iMod = argColumn Mod26 If (iMod = 0) Then If (iNum = 1) Then strEngName = Chr(90) Else strEngName = Chr(65 + iNum - 2) + Chr(90) EndIf Else If (iNum = 0) Then strEngName = Chr(65 + iMod - 1) Else strEngName = Chr(65 + iNum - 1) + Chr(65 + iMod - 1) EndIf EndIf Fun_GetEngName = strEngName EndFunction
PrivateSub CommandButton1_Click() 'Clear Background Color Rows("1:1").Interior.ColorIndex = 2 Rows("2:2").Interior.ColorIndex = 2 'Get Current Date Dim idate AsDate idate = Format(Now, "yyyy/m/d") MsgBox "Today is " & idate & ", have a nice day!", , "Auto Dater, by H.Q." 'Set the bgcolor of the cell of current date Worksheets(1).Cells(171, 3).Value = idate Dim i i = Worksheets(1).Cells(171, 5).Value Worksheets(1).Cells(2, i).Interior.ColorIndex = 8 Worksheets(1).Cells(1, i).Interior.ColorIndex = 8 'Set data source of charts (till yesterday) Dim s AsString s = Fun_GetEngName(i - 1) ActiveSheet.ChartObjects("TimingStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$107:$" & s & "$111") ActiveSheet.ChartObjects("SleepingStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$107:$" & s & "$107") ActiveSheet.ChartObjects("EntertainmentStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$110:$" & s & "$110") ActiveSheet.ChartObjects("StudyStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$108:$" & s & "$108") ActiveSheet.ChartObjects("NormalStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$109:$" & s & "$109") ActiveSheet.ChartObjects("TechStatistics").Activate ActiveChart.SetSourceData Source:=Range("Main!$A$2:$" & s & "$2,Main!$A$111:$" & s & "$111") EndSub