[今週・先週・来週・今月・先月・来月日付をリストボックスに追加]

ダウンロード

 

---

Private Sub CommandButton1_Click()

'====================================
'先週の日付をコンボボックスに追加
'====================================

'先週の期間を取得


Dim sToday As String
Dim dToday As Date

Dim topDate As Date
Dim lstDate As Date

Dim i As Date


ComboBox1.Clear

dToday = Date '基準日
'dToday = "2008/09/17" '基準日

sToday = Format(dToday, "aaa") '基準日を曜日に変換

MsgBox "基準日 --> " & dToday

Select Case sToday

Case "月"
topDate = dToday - 7
lstDate = dToday - 1

Case "火"
topDate = dToday - 8
lstDate = dToday - 2


Case "水"
topDate = dToday - 9
lstDate = dToday - 3

Case "木"
topDate = dToday - 10
lstDate = dToday - 4

Case "金"
topDate = dToday - 11
lstDate = dToday - 5


Case "土"
topDate = dToday - 12
lstDate = dToday - 6

Case "日"
topDate = dToday - 13
lstDate = dToday - 7

End Select



For i = topDate To lstDate

ComboBox1.AddItem i

Next i




End Sub

---

Private Sub CommandButton2_Click()
'====================================
'今週の日付をコンボボックスに追加
'====================================

'今週の期間を取得


Dim sToday As String
Dim dToday As Date

Dim topDate As Date
Dim lstDate As Date

Dim i As Date


ComboBox1.Clear

dToday = Date '基準日
'dToday = "2008/09/17" '基準日

sToday = Format(dToday, "aaa") '基準日を曜日に変換

MsgBox "基準日 --> " & dToday

Select Case sToday

Case "月"
topDate = dToday
lstDate = dToday + 6

Case "火"
topDate = dToday - 1
lstDate = dToday + 5


Case "水"
topDate = dToday - 2
lstDate = dToday + 4

Case "木"
topDate = dToday - 3
lstDate = dToday + 3

Case "金"
topDate = dToday - 4
lstDate = dToday + 2


Case "土"
topDate = dToday - 5
lstDate = dToday + 1


Case "日"
topDate = dToday - 6
lstDate = dToday + 0

End Select



For i = topDate To lstDate

ComboBox1.AddItem i

Next i


End Sub

---

Private Sub CommandButton3_Click()

'====================================
'来週の日付をコンボボックスに追加
'====================================

'来週の期間を取得


Dim sToday As String
Dim dToday As Date

Dim topDate As Date
Dim lstDate As Date

Dim i As Date


ComboBox1.Clear

dToday = Date '基準日
'dToday = "2008/09/17" '基準日

sToday = Format(dToday, "aaa") '基準日を曜日に変換

MsgBox "基準日 --> " & dToday

Select Case sToday

Case "月"
topDate = dToday + 7
lstDate = dToday + 13

Case "火"
topDate = dToday + 6
lstDate = dToday + 12


Case "水"
topDate = dToday + 5
lstDate = dToday + 11

Case "木"
topDate = dToday + 4
lstDate = dToday + 10

Case "金"
topDate = dToday + 3
lstDate = dToday + 9


Case "土"
topDate = dToday + 2
lstDate = dToday + 8


Case "日"
topDate = dToday + 1
lstDate = dToday + 7

End Select



For i = topDate To lstDate

ComboBox1.AddItem i

Next i




End Sub

---

Private Sub CommandButton4_Click()

'====================================
'先月の日付をコンボボックスに追加
'====================================


Dim dToday '基準日

Dim dDate1 As Date
Dim dDateA As Date
Dim dDateB As Date
Dim i


'-------------------------------
'前回のコンボボックスをクリアー
'-------------------------------
ComboBox1.Clear



'-----------
'基準日
'-----------
dToday = Date '今日の日付
'dToday = "2009/1/3"

MsgBox "基準日 --> " & dToday


'----------
'今月の1日
'----------
dDate1 = DateValue(Year(dToday) & "/" & Month(dToday) & "/1")

'----------
'先月の末
'----------
dDateB = dDate1 - 1

'----------
'先月の1日
'----------
dDateA = DateValue(Year(dDateB) & "/" & Month(dDateB) & "/1")




'-------------------------
'日付をコンボボックスへ
'-------------------------
For i = dDateA To dDateB

ComboBox1.AddItem i

Next i


End Sub

---

Private Sub CommandButton5_Click()

'====================================
'今月の日付をコンボボックスに追加
'====================================


Dim dToday '基準日

Dim dYear
Dim dMonth
Dim dDay

Dim dString As String

Dim dDateA As Date
Dim dDateB As Date

Dim i


'-------------------------------
'前回のコンボボックスをクリアー
'-------------------------------
ComboBox1.Clear


'-----------
'基準日
'-----------
dToday = Date '今日の日付 //
'dToday = "2008/12/1"


MsgBox "基準日 --> " & dToday


'-------------------------------
'月のチェック
'-------------------------------
Select Case Month(dToday)

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
dYear = Year(dToday)
dMonth = Month(dToday) + 1
dDay = 1
dString = dYear & "/" & dMonth & "/" & dDay

Case 12 '12月
dYear = Year(dToday) + 1
dMonth = 1
dDay = 1
dString = dYear & "/" & dMonth & "/" & dDay

End Select


'----------
'今月の1日
'----------
dDateA = DateValue(Year(dToday) & "/" & Month(dToday) & "/1")

'------------
'今月の最終日
'------------
dDateB = DateValue(dString) - 1


'-------------------------
'日付をコンボボックスへ
'-------------------------

For i = dDateA To dDateB

ComboBox1.AddItem i

Next i



End Sub

---

Private Sub CommandButton6_Click()

'====================================
'来月の日付をコンボボックスに追加
'====================================


Dim dToday '基準日

Dim dYear
Dim dMonth
Dim dDay

Dim dString As String

Dim dDateA As Date
Dim dDateB As Date

Dim i


'-------------------------------
'前回のコンボボックスをクリアー
'-------------------------------
ComboBox1.Clear

'-----------
'基準日
'-----------
dToday = Date '今日の日付 //
'dToday = DateValue("2008/11/5")

MsgBox "基準日 --> " & dToday


'-------------------------------
'月のチェック
'-------------------------------

Select Case Month(dToday)

Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
dYear = Year(dToday)
dMonth = Month(dToday) + 2
dDay = 1
dString = dYear & "/" & dMonth & "/" & dDay

Case 11
dYear = Year(dToday) + 1
dMonth = 1
dDay = 1
dString = dYear & "/" & dMonth & "/" & dDay

Case 12
dYear = Year(dToday) + 1
dMonth = 2
dDay = 1
dString = dYear & "/" & dMonth & "/" & dDay


End Select


'---------------
'来月の最終日
'---------------
'文字列を日付に変換。 1を引くことで、来月の最終日が取得できる。
dDateB = DateValue(dString) - 1


'----------
'来月の1日
'----------
dDateA = DateValue(Year(dDateB) & "/" & Month(dDateB) & "/1")





'-------------------------
'日付をコンボボックスへ
'-------------------------
For i = dDateA To dDateB

ComboBox1.AddItem i

Next i




End Sub