Very Computer

Is there a macro to turn off ALL check boxes in a
dialog box before the dialog box is brought up? I
have a dialog box with 50 check boxes in it, so
the "With...EndWith" statements would get pretty
long if I were to do it that way.

Thanks,
Teri

Sub Entertain()
   With DialogSheets("EntDialog").checkboxes(1)
    .Value = xlOff
   End With
    DialogSheets("EntDialog").Show
End Sub

and thinking that I had to use the With...End With
statement for all 50 checkboxes. I just discovered
that if I use this one With...End With statement,
but substitute checkboxes() for checkboxes(1), voila!
It does 'em all!

Teri

Teri....glad you figured it out....just one more note (if you didn't
already know it)...if you attach your procedure to the Dialog frame, the
code will run automatically when you Show it.  This is a great place for
initializations.

Take it easy,

--
Jennifer A.  Campion, MCT, MVP Candidate
ExecuTrain of Phoenix
www.etphx.com

  Is there a macro to turn off ALL check boxes in a
  dialog box before the dialog box is brought up? I
  have a dialog box with 50 check boxes in it, so
  the "With...EndWith" statements would get pretty
  long if I were to do it that way.

  Thanks,
  Teri

 Hi Teri.

For some collections of objects, you can set them all at once using the
collections object.

Try this:

dialogsheets("name of your dialogsheet").checkboxes = xlOff

for example, if your dialogsheet is Dialog1 then use

Dialog1.Checkboxes = xlOff

--
Rob Quinan

Teri,
Create a simple dialog sheet and place onto it, two or more checkboxes. Use
the follow macro snippet connected to a button.

Sub ClearDLGChecks1()
    For Each obj In DialogSheets("Dialog1").CheckBoxes
        obj.Value = xlOff  ' Use xlon to turn them on
    Next

    DialogSheets("Dialog1").Show
End Sub

The FOR EACH uses a handle to an object (I chose Obj, its up to you). The
object we want is CheckBoxes. In this case, objects from the dialog called
Dailog1. As the syntax implies, FOR EACH CHECKBOX ...

I agree, to change one object the For Each is overkill. If you have any
more questions, let me know.

-James