Printing range on combox criteria?
 

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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=564691

Printing range on combox criteria?
 

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

--
Regards,
Tom Ogilvy



"Simon Lloyd" wrote:


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


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=564691

Printing range on combox criteria?
 

As ever.......Thanks Tom it worked well!

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=56469