Blue Screen of Death Using Dialog Boxes

[donna.gough]

Good Morning Chaps and Chapesses,
I have never used Dialog boxes before, I usuually use UserForms.
Are they more temperamental than userforms? This morning i have used
the code from j-walk.com to generate a dialog box on the fly but ever
since then I randomly get the blue screen of death that automatically
reboots your machine for you (isn't it thoughtful!). It doesn't always
happen when the macro is run but happens at a later date when using any
program.

I am using a dialog box to avoid writing too many event procedures
unless anybody can tell be a nifty way of doing it.

I have a varing number of checkboxes named CheckBox1, CheckBox2....etc
If the checkbox is ticked I want a 1 in an array or a 0 if not ticked.
Can I write an event attached to the OK button that will loop through
all CheckBoxes and the condition of them and assign a value into an
array? If so, can somebody give me a bit of sample code as I still get
a little confused when writing action codes.
Thank you all.
Donna

 

[Peter T]

Hi Donna,

Sounds like it might be an idea to dispense with your dialog and write one
set of code for your checkboxes in a "WithEvents" Class module. Not sure if
you need Checkboxes on a worksheet or on a userform, but can use this
approach for either. Plenty of examples in this ng.

Regards,
Peter T

 

[donna.gough]

Thanks Peter,
Before your response, I came up with the following....

With Tempform.codemodule
X = .CreateEventProc("Click", "CommandButton1")
.InsertLines X + 1, "i=1" & Chr(13) & _
"For Each ctrl in Me.Controls" & Chr(13) & _
" ShChanges(i) = ctrl.Value" & Chr(13) & _
" i = i + 1" & Chr(13) & _
"Next ctrl" & Chr(13) & _
"Unload Me"

.......which works for me. Is there a better way of doing this ?
CommandButton1 = OK Button
TempForm is my UserForm

All my CheckBox's represent a sheet name. They all have charts on and
the tagged ones will then have the y-axis min /max altered to suit the
data.

 

[Peter T]

Hi Donna,

I take it you are adding new checkboxes to your form at run time and new
code to the form module. I haven't tried your code but if it works I'm sure
it's fine. What I had in mind was something along the lines of this demo.

Insert a class module named "Class1" and two command buttons near the bottom
of a userform.

'' start code in Class1
Public WithEvents cbx As MSForms.CheckBox

Private Sub cbx_Change()
MsgBox cbx.Tag & " : " & cbx.Value, , cbx.Name
'do stuff
End Sub

'' end code in Class1

''Start code in Userform1
Dim colClsChBoxes As New Collection


Private Sub CommandButton1_Click()
Dim cls As Class1
Dim cb As Control
For i = 1 To 3
Set cb = Me.Controls.Add("Forms.CheckBox.1", "My Check Box " & i, True)
With cb
..Left = 10
..Top = (i - 1) * 30 + 5
..Width = 90
..Height = 30
..Caption = .Name
End With
Set cls = New Class1
Set cls.cbx = cb

colClsChBoxes.Add cls
cb.Tag = colClsChBoxes.Count
Next

End Sub

Private Sub CommandButton2_Click()
Dim cnt As Long
Dim arr() As Boolean
cnt = colClsChBoxes.Count
If cnt Then
ReDim arr(1 To cnt)
For i = 1 To cnt
arr(i) = colClsChBoxes(i).cb.Value
MsgBox arr(i), , colClsChBoxes(i).cb.Name
Next
Else
MsgBox "No checkboxes in collection"
End If
End Sub

'' end code in Userform1
Private Sub UserForm_Click()


Class's for existing checkboxes could have course been instanciated and
added to the collection in the Intitialize event.

If you want to refer to the collection of class's (and hence checkboxes)
elsewhere in your project, declare colClsChBoxes as Public in a normal
module.

Regards,
Peter T

 

[Peter T]

Hi Donna,

I take it you are adding new checkboxes to your form at run time and new
code to the form module. I haven't tried your code but if it works I'm sure
it's fine. What I had in mind was something along the lines of this demo.

Insert a class module named "Class1" and two command buttons near the bottom
of a userform.

'' start code in Class1
Public WithEvents cbx As MSForms.CheckBox

Private Sub cbx_Change()
MsgBox cbx.Tag & " : " & cbx.Value, , cbx.Name
'do stuff
End Sub

'' end code in Class1

''Start code in Userform1
Dim colClsChBoxes As New Collection


Private Sub CommandButton1_Click()
Dim cls As Class1
Dim cb As Control
Dim i As Long
For i = 1 To 3
Set cb = Me.Controls.Add("Forms.CheckBox.1", "My Check Box " & i, True)
With cb
..Left = 10
..Top = (i - 1) * 30 + 5
..Width = 90
..Height = 30
..Caption = .Name
End With
Set cls = New Class1
Set cls.cbx = cb

colClsChBoxes.Add cls
cb.Tag = colClsChBoxes.Count
Next

End Sub

Private Sub CommandButton2_Click()
Dim cnt As Long
Dim i As Long
Dim arr() As Boolean
cnt = colClsChBoxes.Count
If cnt Then
ReDim arr(1 To cnt)
For i = 1 To cnt
arr(i) = colClsChBoxes(i).cb.Value
MsgBox arr(i), , colClsChBoxes(i).cb.Name
Next
Else
MsgBox "No checkboxes in collection"
End If
End Sub

'' end code in Userform1
Private Sub UserForm_Click()


Class's for existing checkboxes could have course been instanciated and
added to the collection in the Intitialize event.

If you want to refer to the collection of class's (and hence checkboxes)
elsewhere in your project, declare colClsChBoxes as Public in a normal
module.

Regards,
Peter T

 

[Peter T]

Whoops, typos in Userform1 CommandButton2_Click

arr(i) = colClsChBoxes(i).cb.Value
MsgBox arr(i), , colClsChBoxes(i).cb.Name

cb should read cbx, so

For i = 1 To cnt
arr(i) = colClsChBoxes(i).cbx.Value
MsgBox arr(i), , colClsChBoxes(i).cbx.Name
Next

I'm mystified as to how I could have sent two posts previously!

Peter T