CODEWITHSUNDEEP
excelvba
CaryBush
CaryBush

Reputation: 41

Preventing user form from being activated when setting a print area

There is probably a very simple answer to this. I have a designed a capture sheet on an Excel spreadsheet and have used a DatePicker for cell D2. So far, so good. When I go to close the spreadsheet, I have a small macro that sets the print area B1:M37 ready to print. As this happens, the macro activates the Date Picker which has to be manually closed before I can continue with the printing. How can I either suppress the event happening, or use code to close the user form. My code is as follows:

Sub Test_a_Print_again()
    Range("B1:M37").Select 
    ActiveSheet.PageSetup.PrintArea = "$B$1:$M$37"
       ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Range("B1").Select
End Sub  

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("D2")) Is Nothing Then frmCalendar.Show        '
End Sub

Upvotes: 1

Views: 164

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Your SelectionChange event is being triggered by the line:

Range("B1:M37").Select

There's a couple of things you could do:

  • Don't use Select. It's not clear that it's needed here, and it's one of the top VBA bad practices unless absolutely necessary.

  • In your Worksheet_SelectionChange event, if you only want it to run if D2 and only D2 is selected, you could add another condition:

    If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("D2")) Is Nothing Then frmCalendar.Show

But again, I'd just get rid of the Select statement(s).

Upvotes: 1

Related Questions