VBA password box appears after closing Excel

andrewb90

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
Hello,

about 50% of the time when I close Excel, and VBA password box appears asking for a password (duh). I am having a hard time identifying what is causing it to appear, and when I try to cancel the box it causes Excel to crash and restart. I do have before close code, however I have ruled that out on the basis that if it were causing the problem, it would happen every time. If anybody has any ideas or thoughts, it would be greatly appreciated.

AB
 
Sort by date Sort by votes
The article you suggested in the thread is what I was looking at. (after trying the one line in beforeClose). My problem, is the code in my userform isn't too complicated, so I'm not really sure what is still causing it:

Code: 
Private Sub CB13_Click()
If Sheets("SALES").Range("M7").Value = "13 Week Average" Then
    Me.Hide
    Averagingno.Show
Else
    ActiveSheet.Unprotect "password"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[13]C)-MAX(R[2]C:R[13]C)-MIN(R[2]C:R[13]C))/(COUNT(R[2]C:R[13]C)-2)"
    Range("M7").Value = "13 Week Average"
End If
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True
    Application.ScreenUpdating = True
Me.Hide
End Sub


Private Sub CB26_Click()
If Sheets("SALES").Range("M7").Value = "26 Week Average" Then
    Me.Hide
    Averagingno.Show
Else
    ActiveSheet.Unprotect "password"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[26]C)-MAX(R[2]C:R[26]C)-MIN(R[2]C:R[26]C))/(COUNT(R[2]C:R[26]C)-2)"
    Range("M7").Value = "26 Week Average"
End If
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True
    Application.ScreenUpdating = True
Me.Hide
End Sub


Private Sub CB52_Click()
If Sheets("SALES").Range("M7").Value = "52 Week Average" Then
    Me.Hide
    Averagingno.Show
Else
    ActiveSheet.Unprotect "password"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[52]C)-MAX(R[2]C:R[52]C)-MIN(R[2]C:R[52]C))/(COUNT(R[2]C:R[52]C)-2)"
    Range("M7").Value = "52 Week Average"
End If
    ActiveSheet.Protect "majinbuu", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True
    Application.ScreenUpdating = True
Me.Hide
End Sub


Private Sub CB7_Click()
If Sheets("SALES").Range("M7").Value = "7 Week Average" Then
    Me.Hide
    Averagingno.Show
Else
    ActiveSheet.Unprotect "password"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=(SUM(R[2]C:R[8]C)-MAX(R[2]C:R[8]C)-MIN(R[2]C:R[8]C))/(COUNT(R[2]C:R[8]C)-2)"
    Range("M7").Value = "7 Week Average"
End If
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True
    Application.ScreenUpdating = True
Me.Hide
End Sub


Private Sub CBNO_Click()
Me.Hide
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


        If CloseMode = 0 Then Cancel = True


End Sub
There are 5 click buttons, the 5th being a cancel button.

Additionally, I tried changing Me.Hide to Unload Me in all places, but that didn't fix the issue either.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Excel F8 - Shut Down PC
2
Replies
14
Views
394
Pacman52
P
A
  • Solved
Using Cell value in VBA as Password to Open file
Replies
3
Views
246
Kaper
Kaper
C
  • Question Question
If User Selects Cancel All Sheets are Unprotected
Replies
4
Views
272
christianbiker
C
A
  • Question Question
VBA help...Excel keeps crashing
Replies
2
Views
131
Peter_SSs
Peter_SSs
F
  • Question Question
Error when closing Workbook with a sheet list box
Replies
1
Views
93
Artik
Artik

Forum statistics

Threads
1,226,849
Messages
6,193,330
Members
453,790
Latest member
yassinosnoo1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top