We help IT Professionals succeed at work.
Get Started

Application defined or user defined error

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

Watch Question
Top Expert 2008
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