Thursday, September 24, 2020

TreeView Conditional Format Useform Excel VBA

 TreeView Conditional Format Useform Excel VBA

Create Mini Calendar


Watch Video

VBA code
Private Sub UserForm_Initialize()
Dim i, x As Integer
With Me.TreeView1
'Title
.Nodes.Add , , "A1", "Calendar  " & Year(Date)
'Month Name
For i = 1 To 12
.Nodes.Add , , "B" & i, MonthName(i)

'Calendar
For C = 1 To 31
On Error Resume Next
.Nodes.Add "B" & 1, tvwChild, "C" & C, CDate(C & "/" & 1 & "/" & Year(Date))
.Nodes.Add "B" & 2, tvwChild, "D" & C, CDate(C & "/" & 2 & "/" & Year(Date))
.Nodes.Add "B" & 3, tvwChild, "E" & C, CDate(C & "/" & 3 & "/" & Year(Date))
.Nodes.Add "B" & 4, tvwChild, "F" & C, CDate(C & "/" & 4 & "/" & Year(Date))
.Nodes.Add "B" & 5, tvwChild, "G" & C, CDate(C & "/" & 5 & "/" & Year(Date))
.Nodes.Add "B" & 6, tvwChild, "H" & C, CDate(C & "/" & 6 & "/" & Year(Date))
.Nodes.Add "B" & 7, tvwChild, "I" & C, CDate(C & "/" & 7 & "/" & Year(Date))
.Nodes.Add "B" & 8, tvwChild, "J" & C, CDate(C & "/" & 8 & "/" & Year(Date))
.Nodes.Add "B" & 9, tvwChild, "K" & C, CDate(C & "/" & 9 & "/" & Year(Date))
.Nodes.Add "B" & 10, tvwChild, "L" & C, CDate(C & "/" & 10 & "/" & Year(Date))
.Nodes.Add "B" & 11, tvwChild, "M" & C, CDate(C & "/" & 11 & "/" & Year(Date))
.Nodes.Add "B" & 12, tvwChild, "N" & C, CDate(C & "/" & 12 & "/" & Year(Date))
Next C
Next i
'Days
For x = 2 To .Nodes.Count
.Nodes.Add .Nodes(x).Key, tvwChild, "O" & x, Format(.Nodes(x).Text, "DDDD")

'Conditional format
If .Nodes(x).Text = Format(Date, "MMMM") Then
.Nodes(x).ForeColor = &H8000&
End If
If Format(.Nodes(x).Text, "DDDD") = "Sunday" Then
.Nodes(x).ForeColor = vbRed
.Nodes(x).Child.ForeColor = vbRed
End If
Next x
End With
End Sub




Thursday, September 3, 2020