programmatically clearing checkboxes

[Regouin]

Hello everyone,

I am trying to clear all checkboxes on a userform and then add a number depending on how many cells are filled on an excel spreadsheet. I got the adding of checkboxes worked out, but i am struggling for the right command to remove all the checkboxes on a userform. It's probably just a stupid typo or wrong order but i cant get it to work.
Please enlighten me.

cheers,
frank

[Regouin]

this is what i got so far


Sub CheckboxSetup()


Dim Frm As Object
Dim Btn As msforms.CheckBox
Dim x As Long
Dim n As Long
Dim BtnName As String
Dim i As Long
Dim j As Long
Dim boxno As String


i = 2

Do
i = i + 1
Loop Until Worksheets("mail").Range("A" & i) = ""

For j = 1 To i - 2
boxno = Worksheets("mail").Range("A" & j + 1)
Set Frm = ThisWorkbook.VBProject.VBComponents(7)
Set Btn = Frm.Designer.Controls.Add("forms.CheckBox.1")
With Btn
.Caption = boxno
.Height = 13
.Width = 30
.Left = 12
.Top = 13 * (j - 1)
End With
' With ThisWorkbook.VBProject.VBComponents(5).CodeModule
' n = .CountOfLines
' .InsertLines n + 1, "Sub Checkbox" & j & "1_Click()"
' .InsertLines n + 2, vbNewLine
' .InsertLines n + 3, vbTab & "MsgBox " & """" & "Hi" & """"
' .InsertLines n + 4, vbNewLine
' .InsertLines n + 5, "End Sub"
' End With
Next j

UserForm1.Show

End Sub

[Regouin]

now it doesnt wanna add any checkboxes anymore, i am getting completely lost here. I get the following error msg:

Invalid forward reference, or reference to uncompiled type

i altered the code slightly to this


Option Explicit
Sub CheckboxSetup()


Dim Frm As Object
Dim Btn As MSforms.CheckBox
Dim n As Long
Dim BtnName As String
Dim x As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim boxno As String


i = 2

Do
i = i + 1
Loop Until Worksheets("mail").Range("A" & i) = ""

For x = 1 To ThisWorkbook.VBProject.VBComponents.Count
If ThisWorkbook.VBProject.VBComponents(x).Type = 3 Then
k = x
End If
Next x

For j = 1 To i - 2
boxno = Worksheets("mail").Range("A" & j + 1)
Set Frm = ThisWorkbook.VBProject.VBComponents(k)
Set Btn = Frm.Designer.Controls.Add("forms.CheckBox.1")
With Btn
.Caption = boxno
.Height = 13
.Width = 30
.Left = 12
.Top = 13 * (j - 1)
End With
' With ThisWorkbook.VBProject.VBComponents(5).CodeModule
' n = .CountOfLines
' .InsertLines n + 1, "Sub Checkbox" & j & "1_Click()"
' .InsertLines n + 2, vbNewLine
' .InsertLines n + 3, vbTab & "MsgBox " & """" & "Hi" & """"
' .InsertLines n + 4, vbNewLine
' .InsertLines n + 5, "End Sub"
' End With
Next j

UserForm1.Show

End Sub


* I used DRJ knowledgebase article on adding buttons to every userform in the project as a basis for this. *

[austenr]

Try this to clear your check boxes:


Sub Clearboxes()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
cb.Value = False
Next cb
End Sub

[Bob Phillips]

This works for me


Sub CheckboxSetup()
Dim Frm As Object
Dim x As Long
Dim n As Long
Dim BtnName As String
Dim i As Long
Dim j As Long
Dim boxno As String

i = 2
Do
i = i + 1
Loop Until Worksheets("mail").Range("A" & i) = ""

For j = 1 To i - 2
boxno = Worksheets("mail").Range("A" & j + 1)
Set Frm = ThisWorkbook.VBProject.VBComponents("Userform1")
With Frm.Designer.Controls.Add("forms.CheckBox.1")
.Caption = boxno
.Height = 13
.Width = 30
.Left = 12
.Top = 13 * (j - 1)
End With
' With ThisWorkbook.VBProject.VBComponents(5).CodeModule
' n = .CountOfLines
' .InsertLines n + 1, "Sub Checkbox" & j & "1_Click()"
' .InsertLines n + 2, vbNewLine
' .InsertLines n + 3, vbTab & "MsgBox " & """" & "Hi" & """"
' .InsertLines n + 4, vbNewLine
' .InsertLines n + 5, "End Sub"
' End With
Next j

UserForms.Add(Frm.Name).Show

End Sub

[Regouin]

well it works the first time around, i got the adding and deleting sorted, but when you try it a few times in a row it gets lost somewhere along the line and the userform is going to be halfway between being there and not being there, it gets impossible to delete stuff and it still shows stuff that is not there anymore, very weird stuff. I am currently working on it and I'll keep you updated.

[johnske]

Without testing this, try adding Set Frm = Nothing or, DoEvents on the line before End Sub to see if that clears up your problem

[Bob Phillips]

well it works the first time around, i got the adding and deleting sorted, but when you try it a few times in a row it gets lost somewhere along the line and the userform is going to be halfway between being there and not being there, it gets impossible to delete stuff and it still shows stuff that is not there anymore, very weird stuff. I am currently working on it and I'll keep you updated.

Why would you want to do a few times in a row. Designer adds to the VBIDE object, so it only needs to be done once.

[Regouin]

Well the system is like this:

There are 500 boxes which can or can not be rented to a customer, i just want a box to pop up when the corresponding box is rented to a customer. So every time you open the userform it should check which boxes have a paying customer and show them on the userform.
I am currently working on the rest of the code but I now have a userform showing 500 checkboxes (which gets rather messy, since the margin for error gets quite big).

[Bob Phillips]

Well the system is like this:

There are 500 boxes which can or can not be rented to a customer, i just want a box to pop up when the corresponding box is rented to a customer. So every time you open the userform it should check which boxes have a paying customer and show them on the userform.
I am currently working on the rest of the code but I now have a userform showing 500 checkboxes (which gets rather messy, since the margin for error gets quite big).

Then surely you just want to add run-time controls, dynamically not permanently using Designer.