Printing range on combox criteria?

S

Simon Lloyd

Hi all, I have writen the code below to print a range depending on the
criteria of a combobox, the first combobox selects the sheet name
(works fine) the If Then statements are to look for the match to
combobox2 and if it matches then select the required range and print
it..............sounds simple enough!, but the code below for some
reason is only selecting a single cell to print (which seems to be the
activecell), What am i doing wrong?

Regards,
Simon

Private Sub CommandButton1_Click()
Dim r As Variant
w = ComboBox1.Text
Worksheets(w).Visible = True
Worksheets(w).Select
If ComboBox2.Value = "Tuesday" Then
Set r = Range("A2:M46")
End If
If ComboBox2.Value = "Wednesday" Then
Set r = Range("A50:M94")
End If
If ComboBox2.Value = "Thursday" Then
Set r = Range("A98:M142")
End If
If ComboBox2.Value = "Friday" Then
Set r = Range("A146:M190")
End If
If ComboBox2.Value = "Saturday" Then
Set r = Range("A194:M238")
End If
With r
Application.EnableEvents = False
ActiveSheet.PageSetup.PrintArea = r
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End With
Unload UserForm3
Range("A1").Select
Application.EnableEvents = True
Call Back
End Sub
 
T

Tom Ogilvy

Possibly,
ActiveSheet.PageSetup.PrintArea = r.address(1,1,xlA1,True)
Activesheet.Printout
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top