PDA

View Full Version : Help with creating Array from Checkboxes



jayhoward
05-11-2010, 08:28 AM
Hi,

I'm a bit of a newbie at VB and need a little help with a userform.

I have a userform that shows checkboxes that refer to worksheet names that a user can check to tell the form to include that worksheet in the print, a textbox that allows them to put number of copies in and a print button.

Due to the fact I have "Copies=" in each line it creates seperate print jobs.

What I want to do is have the code create only one print job, so it sees the worksheets as an array to print together, but I can't figure out how to get the worksheets into an array.

Can anyone help me out, it's completely stumped me.

Heres the code:

Private Sub CommandButton1_Click()
Dim intNum As Integer
intNum = CInt(txtNum.Text)
Application.ScreenUpdating = False
If CheckBox1.Value = True Then Sheets("AAA").PrintOut Copies:=intNum
If CheckBox2.Value = True Then Sheets("BBB").PrintOut Copies:=intNum
If CheckBox3.Value = True Then Sheets("CCC").PrintOut Copies:=intNum
If CheckBox4.Value = True Then Sheets("DDD").PrintOut Copies:=intNum
If CheckBox5.Value = True Then Sheets("EEE").PrintOut Copies:=intNum
Unload UserForm1

End Sub

Bob Phillips
05-11-2010, 08:55 AM
See if this works



Private Sub CommandButton1_Click()
Dim SheetNames As String
Dim intNum As Long
intNum = CInt(txtNum.Text)
Application.ScreenUpdating = False
If CheckBox1.Value = True Then SheetNames = SheetNames & Sheets("AAA") & ", "
If CheckBox2.Value = True Then SheetNames = SheetNames & Sheets("BBB") & ", "
If CheckBox3.Value = True Then SheetNames = SheetNames & Sheets("CCC") & ", "
If CheckBox4.Value = True Then SheetNames = SheetNames & Sheets("DDD") & ", "
If CheckBox5.Value = True Then SheetNames = SheetNames & Sheets("EEE") & ", "

If SheetNames <> "" Then

Sheets(Array(Left$(SheetNames, Len(SheetNames) - 2))).PrintOut Copies:=intNum
End If
Unload UserForm1

End Sub

jayhoward
05-11-2010, 09:02 AM
Tried and I get a runtime error 438 - Object doesn't support this property or method, and points to SheetNames = SheetNames & Sheets("AAA") & ", "

GTO
05-11-2010, 12:09 PM
I could not manage to get a string into Array() (for multiple args) and get it to work. It would be nice to see how.

I did manage this, which althought no printer to check, seems to work based on msg, "Now printing page 1 of xx"


Private Sub CommandButton1_Click()
Dim SheetNames As String
Dim intNum As Long

intNum = Application.Min(IIf(IsNumeric(txtNum.Value), txtNum.Text, 1), 1)
Application.ScreenUpdating = False
If CheckBox1.Value = True Then SheetNames = SheetNames & "AAA,"
If CheckBox2.Value = True Then SheetNames = SheetNames & "BBB,"
If CheckBox3.Value = True Then SheetNames = SheetNames & "CCC,"
If CheckBox4.Value = True Then SheetNames = SheetNames & "DDD,"
If CheckBox5.Value = True Then SheetNames = SheetNames & "EEE,"

If SheetNames <> "" Then
Sheets(Split(Left$(SheetNames, Len(SheetNames) - 1), ",")).PrintOut Copies:=intNum
End If
Unload UserForm1

End Sub

Mark

jayhoward
05-11-2010, 01:37 PM
Hey thanks, I really need it to go into a single Print Job or an array, this is to allow us at times to print to PDF.

Anybody have any ideas, would really appreciate any help :-)

Paul_Hossler
05-11-2010, 06:17 PM
Is this what you were looking for?

Sheets ( ).PrintOut needs to have a real array


Sub drv2()
Dim SheetNames
Dim v As Variant
Dim intNum As Long

intNum = 1

'If CheckBox1.Value = True Then SheetNames = SheetNames & "AAA,"
'If CheckBox2.Value = True Then SheetNames = SheetNames & "BBB,"
'If CheckBox3.Value = True Then SheetNames = SheetNames & "CCC,"
'If CheckBox4.Value = True Then SheetNames = SheetNames & "DDD,"
'If CheckBox5.Value = True Then SheetNames = SheetNames & "EEE,"


'for testing
SheetNames = "AAA,BBB,CCC,DDD,EEE,"
If Len(SheetNames) > 0 Then SheetNames = Left(SheetNames, Len(SheetNames) - 1)

v = Split(SheetNames, ",")

Sheets(v).PrintOut Copies:=intNum

End Sub


Paul

jayhoward
05-12-2010, 06:25 AM
Hey Paul,

I'm not sure how your code would work, bearing in mind that you've removed the checkbox references.

I'm running the code and getting subscript out of range.

Cheers,

Jay

Paul_Hossler
05-12-2010, 08:39 AM
I did not try to create your workbook from scratch, and I didn't have any idea what your sheets were actually called. I started with XLD's code that built a string of sheets to be printed togeather


SheetNames = "AAA,BBB,CCC,DDD,EEE,"


and just added the array as a parameter to the .PrintOut

You'd have to build your own string based on the checkboxs and then Split ( ) it into an array for .PrintOut to use

If you post a sample WB, I could see it works.

Paul

jayhoward
05-12-2010, 08:50 AM
Hi Paul,

The actual sheets are called AAA, BBB, CCC, DDD, EEE

The user form has 5 checkboxes that relate to the above, so the actual names are right.

Thanks for your help though.

Cheers,

Jay

GTO
05-12-2010, 09:09 AM
Greetings Jay,

Am I correct in presuming that you tried #4? Split produces an array, and it seems to be one print job in the print que.

Mark

jayhoward
05-12-2010, 09:32 AM
Hi Mark,

When I ran #4 it came up with Subscript out of Range and it debugs to
Sheets(v).PrintOut Copies:=intNum

I also need the array to build depending on what checkboxes are ticked on the userform.

Hope that makes sense.

Jay

GTO
05-12-2010, 09:46 AM
...it debugs to Sheets(v).PrintOut Copies:=intNum

I also need the array to build depending on what checkboxes are ticked on the userform.

Hope that makes sense.

Not so far, but a thought occurred. Do you have any/all of the sheets hidden?

If yes, add code to display, run the print, re-hide.

Edit: PS - In my test, it does print based on checkboxes ticked.

Paul_Hossler
05-12-2010, 05:06 PM
Jay

What version of Excel are you using?

Can you post a workbook? Mark's approach works for me also, but I don't have anything hidden or anything else tricky.

Are you checking all the boxes, some, or none when it fails?

I just print to CutePDF and I only get one job

Paul

jayhoward
05-13-2010, 05:31 AM
Hey,

Im using Excel 2007.

I haven't got anything hidden that is in the print array required.

I'm trying to get Marks code to work, but it doesn't print as a single job, it prints as seperate jobs, im also using CutePDF.

Jay

Paul_Hossler
05-13-2010, 08:57 AM
I'm also using 2007, and mine comes out as one job

No other ideas

Paul