Hope you guys can help me out as I´m stuck with my vba code. I´ve tried to find my answer on several sites but I haven´t found what I am looking for (so far).
What I am trying to do:
I have a userform from which I want to import all the values into a predefined table (called "Overzicht"). However, I have 12 checkboxes (1 for each month) and I want 1 row for each month if that checkbox is checked. What I also want, is to put the Caption of the checked checkbox in one of the columns in the table. So, for example, if I only check January, February and March, I want the vba to import all values into a table into three rows where the first row the 'Month' columns says "January", the second one "February" etc. I have the code in such a way that it checks the number of checked checkboxes and creates the same number of rows, but I don´t know how to get the last step - getting the correct captions in the correct rows. This is what I have so far:
Private Sub CommandButton1_Click()
Dim rng As Range
Dim newrow As ListRow
Set rng = ThisWorkbook.Worksheets("Kostenoverzicht").Range("Overzicht")
Dim answer As Integer
'check number of rows to insert based on # of checked months
Dim ctl As MSForms.Control
Dim rows As Long
For Each ctl In Kostenoverzicht.Frame2.Controls
If TypeOf ctl Is MSForms.CheckBox Then
If Kostenoverzicht.Frame2.Controls(ctl.Name).Value = True Then
rows = rows + 1
End If
End If
Next
answer = MsgBox("Are you sure you want to continue?", vbQuestion + vbYesNo + vbDefaultButton1, "Zet in overzicht?")
If answer = vbYes Then
rng.Select
Set newrow = Selection.ListObject.ListRows.Add(alwaysinsert:=True)
With ws
For rows = 1 To rows
newrow.Range.Cells(rows, 1).Value = Me.TextBox1.Value
newrow.Range.Cells(rows, 2).Value = Me.CategorieBox.Value
newrow.Range.Cells(rows, 3).Value = Me.SubCategorieBox.Value
newrow.Range.Cells(rows, 4).Value = Me.BankrekeningBox.Value
If OptionButton1.Value = True Then newrow.Range.Cells(rows, 5).Value = "Af" Else newrow.Range.Cells(1, 5).Value = "Bij"
newrow.Range.Cells(rows, 6).Value = Me.TextBox2.Value
newrow.Range.Cells(rows, 7).Value = Me.CheckBox1.Caption
If OptionButton3.Value = True Then newrow.Range.Cells(rows, 8).Value = "Ja" Else newrow.Range.Cells(1, 8).Value = "Nee"
Next
End With
End If
End Sub
so I need some code to replace the newrow.Range.Cells(rows, 7).Value = Me.CheckBox1.Caption. The way it is now, it will give the value "January" to all rows but I want it to take the caption of the checked checkbox.
Hope someone can help me out, thanks.
Aucun commentaire:
Enregistrer un commentaire