0

So I have a UserForm that works, except for the close button. I have the simple close code...

Private Sub vbClose_Click()
Unload Me
End Sub

But when that part runs, another Private Sub kicks off for some reason...

Private Sub ComboBox_AfterUpdate()
Dim myRng As Range, VIT As Workbook, Sheet As Worksheet, LastRow As Long, myCol As String, LastCol As Long
Dim Loc As Range, CLoc As String, ANLoc As String, CurLoc As String

Set VIT = Workbooks("Workbook.xlsm")
Set Data = Sheets("Sheet")
LastCol = Data.Range("A1").CurrentRegion.Columns.Count
myCol = GetColumnLetter(LastCol)
LastRow = Data.Range("B" & Rows.Count).End(xlUp).Row
Set myRng = Range("H2:H" & LastRow)
Set Loc = myRng.Find(What:=Fund.Value, LookIn:=xlValues)
If Loc.Offset(0, -5).Value = Pool.Value Then <--------- line that breaks
   CLoc = Loc.Offset(0, 60)
   Cusip.Caption = Format(CLoc, "000000000")
   ANLoc = Loc.Offset(0, 40).Value
   AcctNum.Caption = Format(ANLoc, "0000")
   CurLoc = Loc.Offset(0, 11)
   CurrLabel.Caption = CurLoc
   Else
   Set Loc = myRng.FindNext(After:=Loc)
   CLoc = Loc.Offset(0, 60).Value
   Cusip.Caption = Format(CLoc, "000000000")
   ANLoc = Loc.Offset(0, 40)
   AcctNum.Caption = Format(ANLoc, "0000")
   CurLoc = Loc.Offset(0, 11)
   CurrLabel.Caption = CurLoc
End If

End Sub

Any help is appreciated as to why.

AJames
  • 81
  • 1
  • 1
  • 10
  • `Loc` is likely `Nothing` so you need to guard against this situation. In other words, you can't check the `Offset` unless `Loc` is valid. Same thing for `Pool`. – Brian M Stafford Oct 17 '18 at 20:56
  • Correct, but all I want is to close the UserForm. I'm not updating anything. – AJames Oct 17 '18 at 21:02
  • 1
    This is a good example of why you should decouple your business logic from your presentation logic. I'd give [this answer](https://stackoverflow.com/a/47291028/4088852) a read, then remove that entire section of code from the event handler to somewhere more appropriate. – Comintern Oct 17 '18 at 21:21
  • Comintern, that is an awesome link that I have now saved. Thanks so much! – AJames Oct 17 '18 at 23:47

0 Answers0