CODEWITHSUNDEEP
vbaexcel
Will Clein
Will Clein

Reputation: 79

After Running Macro Excel will not close, sheet will not scroll, and arrow key causes selection to jump wildly

I have created a report template that allows users to select various filters and then creates specific reports based on those filters. Below is the code for the filter selection button:

Sub Filter_Select()
 rptSelect.Show
End Sub

After this, users are presented with UserForms to select different filters. Each UserForm calls the next user form until they reach the last UserForm whose code is such:

Private Sub UserForm_Activate()
    With milSelect
        .Top = Application.Top + 250
        .Left = Application.Left + 250
    End With
End Sub
--------------------------------------------------
Private Sub UserForm_Initialize()
    milDV.RowSource = Range("Q1:Q8").Address
End Sub
--------------------------------------------------
Private Sub CancelmilSelect_Click()
    Unload Me
End Sub
--------------------------------------------------
Private Sub OKmilSelect_Click()
    Sheets("Report Selection").Unprotect
    Range("B6").ClearContents

    Range("B6") = milDV.Text

    Unload Me

    Call Create_Report
End Sub

Once they select the final filter, the Create_Report macro is run to determine which reports to create and what formulas to use based on the filters selected. The code is as such:

Option Explicit
Sub Create_Report()
'
'   Macro to update fields in different reporting sections after user selects report type
'

'
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
'
'   Display Tabs
'
    Dim z As Integer
        For z = 2 To Worksheets.Count
            Sheets(z).Visible = xlSheetHidden
        Next z
    If Range("B1").Value = "National" And Range("B6") = "Military" Then
        Sheets("Program Summary").Visible = xlSheetVisible
    Else
        If Range("B1") = "National" Then
            Sheets("Program Summary").Visible = xlSheetVisible
            Sheets("Family & Household Demographics").Visible = xlSheetVisible
            Sheets("Registered Member Dues").Visible = xlSheetVisible
        Else
            Sheets("Program Summary").Visible = xlSheetVisible
            Sheets("Summary").Visible = xlSheetVisible
            Sheets("Financial Summary").Visible = xlSheetVisible
        End If
    End If
'
'   Financial Summary
'
    If Sheets("Financial Summary").Visible = True Then
    Sheets("Financial Summary").Select
    Sheets("Financial Summary").Unprotect
'
'       Name Ranges
'
        Dim Operating_Expenses, Income_Private1, Income_Private2, Income_Private3, Income_Gov, Income_Other1, Income_Other2 As Range
        Set Operating_Expenses = Range("C5:C7")
        Set Income_Private1 = Range("C13")
        Set Income_Private2 = Range("C14:C16")
        Set Income_Private3 = Range("C17")
        Set Income_Gov = Range("C19:C22")
        Set Income_Other1 = Range("C25:C26")
        Set Income_Other2 = Range("C27:C30")
'
'       State Non-Military Financial Summary
'
        If Worksheets("Report Selection").Range("B1").Value = "State" Then
            If Worksheets("Report Selection").Range("B6").Value = "Non-Military" Then
                Range("B1").Value = Worksheets("Report Selection").Range("B4").Value
                Range("C1").Value = "Non-Military"
                Operating_Expenses.Formula = "=SUMIFS(INDEX('Financial Data'!$BE:$BG, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Private1.Formula = "=SUMIFS(INDEX('Financial Data'!$AO:$AO, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Private2.Formula = "=SUMIFS(INDEX('Financial Data'!$AL:$AN, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Private3.Formula = "=SUMIFS(INDEX('Financial Data'!$AQ:$AQ, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Gov.Formula = "=SUMIFS(INDEX('Financial Data'!$BA:$BD, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Range("C24").Formula = "=SUMIFS('Financial Data'!$AP:$AP,'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Other1.Formula = "=SUMIFS(INDEX('Financial Data'!$AV:$AW, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Income_Other2.Formula = "=SUMIFS(INDEX('Financial Data'!$AR:$AU, 0, ROW(1:1)),'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Range("C31").Formula = "=SUMIFS('Financial Data'!$AZ:$AZ,'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Range("C32").Formula = "=SUMIFS('Financial Data'!$AY:$AY,'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
                Range("C33").Formula = "=SUMIFS('Financial Data'!$AK:$AK,'Financial Data'!$E:$E,$B$1,'Financial Data'!$H:$H,""0"")"
            End If
        End If
    End If
'
'   Select First Sheet of Report
'
    Dim i As Integer
    Dim ws As Worksheet
    For i = 1 To Worksheets.Count
        If Sheets(i).Visible = True Then
            Sheets(i).Protect
        End If
    Next i
    For i = 2 To Worksheets.Count
        If Sheets(i).Visible = True Then
            Sheets(i).Select
            Exit For
        End If
    Next i
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

All of the code runs properly and the reports are created, but I'm having a bug where when I try to scroll on the sheet that is displayed after the code runs, the scroll bar moves but the cells seem stuck in place. If I try to select a cell and move with the arrow key, the selection jumps around. Both of these things are solved by going to another sheet and then coming back.

Additionally, after this code runs, I cannot close Excel with any method other than through Task Manager. Since none of this happens when I first open the workbook, I am left to assume that my above code is somehow causing this.

Upvotes: 0

Views: 1095

Answers (1)

Will Clein
Will Clein

Reputation: 79

Sorry it took so long to get back to this. I've been swamped the past week. Anyways, I've finally solved the problem thanks to some direction from @Comintern. Basically, I took all of the grunt work out of each of my forms and put it in the Module that the filter button calls.

Not only is this a more streamlined and efficient way to work with the code, it also provides a centralized location to assess any errors that may crop-up essentially eliminating a few possible points of failure. Below is both the code for the filter button and the code for one of the filter forms.

New form code:

Private Sub UserForm_Activate()
    With milSelect
        .Top = Application.Top + 250
        .Left = Application.Left + 250
    End With
End Sub
---------------------------------------------------    
Private Sub UserForm_Initialize()
    milDV.RowSource = Range("Q1:Q8").Address
End Sub
Private Sub CancelmilSelect_Click()
Unload Me
End Sub
---------------------------------------------------    
Private Sub OKmilSelect_Click()
    Sheets("Report Selection").Unprotect
    Range("B6").ClearContents

    Range("B6") = milDV.Text

    Me.Hide
End Sub

Filter button code:

Sub Filter_Select()
    Dim ReportType As String
    Dim Region As String
    Dim ServiceUnit As String
    Dim State As String
    Dim Military As String

    With New rptSelect
        .Show vbModal
        If Not Cancel = True Then
            If Range("B1").Value = "National" Then
                With New milSelect
                    .Show vbModal
                    If Not Cancel = True Then
                        Unload rptSelect
                        Unload milSelect
                        Call Create_Report
                    End If
                End With
            Else
                If Range("B1").Value = "Service Unit" Then
                    With New SUSelect
                        .Show vbModal
                        If Not Cancel = True Then
                            With New milSelect
                                .Show vbModal
                                If Not Cancel = True Then
                                    Unload rptSelect
                                    Unload milSelect
                                    Unload SUSelect
                                    Call Create_Report
                                End If
                            End With
                        End If
                    End With
                    Else
                        If Range("B1").Value = "Regional" Then
                            With New rgnSelect
                                .Show vbModal
                                If Not Cancel = True Then
                                    With New milSelect
                                        .Show vbModal
                                        If Not Cancel = True Then
                                            Unload rptSelect
                                            Unload milSelect
                                            Unload rgnSelect
                                            Call Create_Report
                                        End If
                                    End With
                                End If
                            End With
                            Else
                                If Range("B1").Value = "State" Then
                                    With New stateSelect
                                        .Show vbModal
                                        If Not Cancel = True Then
                                            With New milSelect
                                                .Show vbModal
                                                If Not Cancel = True Then
                                                    Unload rptSelect
                                                    Unload milSelect
                                                    Unload stateSelect
                                                    Call Create_Report
                                                End If
                                            End With
                                        End If
                                    End With
                                End If
                            End If
                        End If
            End If
        End If
    End With
End Sub

I'm sure there is probably a cleaner way to write this other than how it appears here and welcome any suggestions anyone may have for making it look better, but for now at least, it is working like a charm. Thanks for everyone's input and patience.

Upvotes: 1

Related Questions