Using single panel containing checkboxes for multi

Question

I am a VB.NET beginner.

I want to achieve following:

1. Node1 is clicked which opens a panel containing check-boxes.
2. The user will click a few check-boxes.
3. The user clicks node2 which will export check-box information to an Excel sheet column, and reset the panel.
4. New information entered on panel is exported to an adjacent column in the same Excel sheet used in step3.
5. The above process continues for 90 nodes.

How do I do the first part in steps 3, 4 and 5?

This is my first try which is not working:

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
oWB = oXL.Workbooks.Open("F:\open.xlsx")
oSheet = oWB.Worksheets("Sheet1")

'I am not able to think clearly on following loop

For i = 1 To 3
    For j = 1 To 90
        If CheckBox1.Checked Then
            oSheet.Cells(i, j).value = "1"
        Else : oSheet.Cells(i, j).value = "0"
        End If
        If CheckBox2.Checked Then
            oSheet.Cells(i, j).value = "1"
        Else : oSheet.Cells(i, j).value = "0"
        End If
        If CheckBox3.Checked Then
            oSheet.Cells(i, j).value = "1"
        Else : oSheet.Cells(i, j).value = "0"
        End If
    Next
Next

'Following works

CheckBox1.Checked() = False
CheckBox2.Checked() = False
CheckBox3.Checked() = False
ComboBox1.ResetText()

Answer 1

What you clearly need is a way to store the user's selections in memory, before saving them to the spreadsheet. There are several ways you could do this, but given your inexperience I suggest you consider the simplest, which is to define a basic class to represent the user's selections for a single node, and an array – where each item is an instance of the class – to store the entire set of user selections.

Define the node selection class – to represent selections for a single node:

Public Class NodeSelection
    Public CheckA As Boolean
    Public PickAIndex As Integer = -1
    Public CheckB As Boolean
    Public PickBIndex As Integer = -1
    Public CheckC As Boolean
    Public PickCIndex As Integer = -1
    Public ItemProcessed As Boolean
End Class

Define your variables – in your form class (not in a sub):

Private _userPicks(89) As NodeSelection 'Array of user's selections
Private _previousIndex As Integer = -1  'Used to record the previously selected node

Instantiate the array items – in the form's Load event:

'Instantiate the class for each element of the array 
For i As Integer = 0 To _userPicks.Count - 1
    _userPicks(i) = New NodeSelection
Next

Keeping track of user selections:

Whenever a new node is selected, you need to update the array item for the previously selected node then reset the controls for the current node. This is best done in the treeview's AfterSelect event:

Private Sub TreeView1_AfterSelect(sender As Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView1.AfterSelect

    'Exit if the click is on the parent node (Node0)
    If e.Node.GetNodeCount(False) > 0 Then Return

    UpdateNodeInfo()

    'If the currently selected node has already been processed, 
    'restore user selection values to the controls,
    'otherwise reset the controls
    With _userPicks(e.Node.Index)
        CheckBox1.Checked = If(.ItemProcessed, .CheckA, False)
        ComboBox1.SelectedIndex = If(.ItemProcessed, .PickAIndex, -1)
        CheckBox2.Checked = If(.ItemProcessed, .checkB, False)
        ComboBox2.SelectedIndex = If(.ItemProcessed, .PickBIndex, -1)
        CheckBox3.Checked = If(.ItemProcessed, .checkC, False)
        ComboBox3.SelectedIndex = If(.ItemProcessed, .PickCIndex, -1)
    End With

    'Color the previous selection so the user can see it's been processed
    If _previousIndex >= 0 Then TreeView1.Nodes(0).Nodes(_previousIndex).BackColor = Color.AntiqueWhite

    'Record this (selected) node's index for updating when the next node is selected
    _previousIndex = e.Node.Index
End Sub

Private Sub UpdateNodeInfo()
    If _previousIndex < 0 Then Return 'No item has been set yet
    With _userPicks(_previousIndex)
        .CheckA = CheckBox1.Checked
        .PickAIndex = If(.CheckA, ComboBox1.SelectedIndex, -1)
        .CheckB = CheckBox2.Checked
        .PickBIndex = If(.CheckB, ComboBox2.SelectedIndex, -1)
        .checkC = CheckBox3.Checked
        .PickCIndex = If(.checkC, ComboBox3.SelectedIndex, -1)
        .ItemProcessed = True 'Record the fact the item has already been processed
    End With
End Sub

Writing values to the spreadsheet:

Notice that I put the array item update routine in a separate procedure. This is because you will have to update the final selection before writing it all out to the spreadsheet. I presume you will have a button to save their selections, so you just need to call the UpdateNodeInfo sub from there before iterating over the array and writing the values. Here is how you might iterate over the values and update the spreadsheet:

For i As Integer = 0 To _userPicks.Count - 1
    With _userPicks(i)
        oSheet.Cells(3, i + 2) = "Node" & (i + 1).ToString
        oSheet.Cells(9, i + 2) = "Node" & (i + 1).ToString
        oSheet.Cells(4, i + 2) = If(.ItemProcessed AndAlso .CheckA, 1, 0)
        oSheet.Cells(5, i + 2) = If(.ItemProcessed AndAlso .CheckB, 1, 0)
        oSheet.Cells(6, i + 2) = If(.ItemProcessed AndAlso .checkC, 1, 0)
        oSheet.Cells(10, i + 2) = If(.ItemProcessed AndAlso .CheckA, ComboBox1.Items(.PickAIndex).ToString, "")
        oSheet.Cells(11, i + 2) = If(.ItemProcessed AndAlso .CheckB, ComboBox1.Items(.PickBIndex).ToString, "")
        oSheet.Cells(12, i + 2) = If(.ItemProcessed AndAlso .checkC, ComboBox1.Items(.PickCIndex).ToString, "")
    End With
Next

I assume you already know how to open, save and close the spreadsheet, so I'll leave that side of it up to you. Get familiar with the methods outlined here and post another question if you don't.

Finally

The above is a fairly simple way to achieve what you're trying to do. If you think you may need to add more functionality to your class, you should look at substituting public members for properties – some would say you should do that anyway – and you may want to consider storing the complete set of user selections in a List(Of T) object rather than an array.