VBA Runtime Error with XLSB file

D3allamerican07

Board Regular
Joined
Jul 22, 2015
Messages
101
I have a large macro enabled file I saved as a XLSB to reduce size and opening time. Now however, if it is not the first excel file open, I get a Runtime Error '1004' when my macro events run. However, when it is the first file open, the macro events perform perfectly.

Hopefully someone has the answer to this infuriating problem. Thanks.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
What line of code throws the error?

Can you post the complete code?
 

D3allamerican07

Board Regular
Joined
Jul 22, 2015
Messages
101
Code:
Private Sub oCombo1_Change()
[B]Sheets("Sheet1").Range("A:E").ClearContents[/B]

    Sheets("FESheets").AutoFilterMode = False
    Sheets("FESheets").Columns("A:H").AutoFilter Field:=1, Criteria1:=oCombo1.Value
        
LR = Sheets("FESheets").Range("B" & Rows.Count).End(xlUp).Row

Sheets("FESheets").Range("B2:C" & LR).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Sheet1").Range("A1")

Sheets("Sheet1").Range("A:A").AdvancedFilter _
    Action:=xlFilterCopy, _
   CopyToRange:=Sheets("Sheet1").Range("C1"), _
    Unique:=True
    
Sheets("Sheet1").Range("B:B").AdvancedFilter _
    Action:=xlFilterCopy, _
   CopyToRange:=Sheets("Sheet1").Range("D1"), _
    Unique:=True

LR2 = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

    For X = 1 To LR2
        Sheets("Sheet1").Range("E" & X).Value = Sheets("Sheet1").Range("C" & X).Value & " - " & Sheets("Sheet1").Range("D" & X).Value
    Next X


    For I = 1 To LR2
        oCB2.ListFillRange = "Sheet1!E2:E" & LR2
    Next I
    
    oCB3.ListFillRange = ""

End Sub
It errors out on the line 2 (bolded code)
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
Try fully qualifying the object:

Code:
Private Sub ComboBox1_Change()
Workbooks("uform").Sheets("Sheet1").Range("A:E").ClearContents
End Sub
 

D3allamerican07

Board Regular
Joined
Jul 22, 2015
Messages
101
Worf, still no luck. Still receiving the same error

Run-time error '1004'
ClearContents method of Range class failed
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Where is the control? Is it on a worksheet and linked to cells?
 

D3allamerican07

Board Regular
Joined
Jul 22, 2015
Messages
101
The control is on the worksheet and not linked directly to any cell.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,938
- Is it a form or ActiveX control?
- How are you using it, typing or selecting from a list?
- If it’s a list, how is it populated?
 

D3allamerican07

Board Regular
Joined
Jul 22, 2015
Messages
101
I had an ActiveX control populated manually and then a second ActiveX combo box populated from a filtered list upon the selection of the first.

However I had it as a ComboBox_Change event. Changed it to a ComboBox_DropDownList and it has been working perfectly. I appreciate everyone's help though.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,277
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top