We help IT Professionals succeed at work.
Get Started

Application defined or user defined error

839 Views
Last Modified: 2012-05-05
I have created a userform (UserForm1)  with a listbox (ListBox1) using Excel VBA on Windows XP. The purpose of the userform is to enter property investing information into corresponding ranges on Sheet 2.  I want current and previous entries to be displayed in the listbox. On my spreadsheet I have 10 buttons next to various cells displaying the various categories of property investing data.  Depending on what button is pressed the userform's caption is successfully updating i.e. the UserForm automtically displays "Deposit" as it's caption after the button next to the "Deposit" category is pressed). Also dependant on the button activation I want the rowsource of the listbox  to reflect the corresponding target ranges on sheet 2. I have attached code which I thought would achieve the above objective but am getting the error: "Run time Error 1004 - Application defined or object defined error".     The line:   "UserForm1.Show"  is highlighted as the culprit in the macro "ShowForm".. Can someone please explain why I am getting the error and whether the way I have written my code is the most efficent way to achieve the above objective.  Regards.
Private Sub UserForm_Initialize()
 
    GetCaption   ' Initiates the "GetCaption" sub procedure to assign caption to userform. (See below)
            
    GetRowSource ' Initiates the "GetRowSource" sub procedure to match correpsonding target ranges to   
                   captions. (See below)
    
    'Set properties of listbox1
    With Me.ListBox1
        .BoundColumn = 1
        .ColumnCount = 3
        .ColumnHeads = False
        .TextColumn = True
        .ListStyle = fmListStylePlain
        .ListIndex = 0
    End With
    
 
End Sub
 
 
 
Option Explicit
 
Dim Rng As Range
Dim OffCell As String
 
Sub ShowForm()
 
  UserForm1.Show    (This is the cuplrit line when ShowForm sub procedure attempts to execute. 
    
End Sub
 
 
Sub GetCaption()
' This sub procedure assigns the caption to the userform.
      
    ' "OffCell" variable assigned to cell two columns to left of button
    OffCell = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Offset(0, -2).Text
 
    ' String (text) in "OffCell" variable equals UserForm1's caption
    UserForm1.Caption = OffCell
 
End Sub
 
 
Sub GetRowSource() 
' This sub procedure then displays the corresponding target range with the caption in the list box.   
 
    Dim LastRow As Long
    
    If OffCell = "Deposit" Then
        
          
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A2:C4")
          
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A2:C2") + LastRow
                    
    ElseIf OffCell = "Legal Costs" Then
    
          
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A6:C8")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A6:C6") + LastRow
          
    
    ElseIf OffCell = "Mortgage Registration" Then
    
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A10:C12")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A10:C10") + LastRow
           
    ElseIf OffCell = "Loan Application Fees" Then
           
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A14:C16")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A14:C14") + LastRow
          
           
    ElseIf OffCell = "Mortgage Duty" Then
          
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A18:C21")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A18:C18") + LastRow
           
    ElseIf OffCell = "Mortgage Insurance" Then
           
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A23:C26")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A23:C23") + LastRow
           
    ElseIf OffCell = "Other Borrowing Costs" Then
           
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A28:C31")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A28:C28") + LastRow
           
    ElseIf OffCell = "Stamp Duty" Then
                      
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A33:C36")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A33:C33") + LastRow
           
    ElseIf OffCell = "Building Inspections" Then
                      
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A38:C40")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A38:C38") + LastRow
           
    ElseIf OffCell = "Initial Repairs" Then
     
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A42:C44")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A42:C42") + LastRow
          
           
    ElseIf OffCell = "Miscellaneous Costs" Then
 
          LastRow = Application.CountA(Sheets("Sheet2")).Range("A46:C48")
                    
          UserForm1.ListBox1.RowSource = Sheets("Sheet2").Range("A46:C46") + LastRow
          
           
           
           
    End If
    
    
End Sub

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2008
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE