print board

[lior03]

helllo
i wanted to create my own print command for excel .i created a user form
which will include all my necessary information before print.
first a RefEdit1 for specifing the print area.then 3 checkboxes , one for printing comments ia the prtint area the second for print title row and a third for printing grid lines.i wanted 2 command buttons . one for a preview and the other for printing my work .
i came up with this:
[vba]
Private Sub CheckBox1_Click()
ActiveSheet.PageSetup.PrintComments = xlPrintInPlace
End Sub
Private Sub CheckBox2_Click()
End Sub
Private Sub CommandButton1_Click()
UserForm17.Hide
End Sub
Private Sub CommandButton2_Click()
ActiveSheet.PrintPreview
End Sub
Private Sub RefEdit1_BeforeDragOver(cancel As Boolean, ByVal Data As _
MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, _
ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal _
DragState As MSForms.fmDragState, Effect As _
MSForms.fmDropEffect, ByVal Shift As Integer)
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim ch As CheckBox
For Each ch In UserForm17
ch.Value = ""
Next
End Sub


[/vba]
can anyone help?
thanks

[mdmackillop]

Hi Moshe,
Please use line breaks in your code. I don't have a second screen to see what falls of the edge!

[lior03]

let me be more specific .if i have a userform with several checkboxes of several optionboxes buttons,is it possible through an userform intialized event to loop through all of them and clear them?
thanks

[lior03]

hello
what about:
[VBA]
Private Sub UserForm_Activate()
Dim ctrl As Control
For Each ctrl In UserForm17.Controls
If TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "RefEdit1" Then
ctrl.Value = ""
End If
Next ctrl
End Sub

[/VBA]
it worked on the check boxes but not on the refedit1 why?
i am trying to establish a print area with the refedit:
[VBA]
Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
ActiveSheet.pagesetup.PrintArea = ""
ActiveSheet.pagesetup.PrintArea = UserForm17.RefEdit1.Value
End Sub

[/VBA]
is this rhe way to do it?
thanks

[lior03]

hello
what about:
[vba]
Private Sub UserForm_Activate()
Dim ctrl As Control
For Each ctrl In UserForm17.Controls
If TypeName(ctrl) = "CheckBox" Or TypeName(ctrl) = "RefEdit1" Then
ctrl.Value = ""
End If
Next ctrl
End Sub

[/vba]
it worked on the check boxes but not on the refedit1 why?
i am trying to establish a print area with the refedit:
[vba]
Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)
ActiveSheet.pagesetup.PrintArea = ""
ActiveSheet.pagesetup.PrintArea = UserForm17.RefEdit1.Value
End Sub

[/vba]
is this rhe way to do it?
thanks