Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Crashes excel (Excel 200 VBA)

    I have the following code that crashes excel. It causes a close and deletes the code.

    Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    Dim lngLine As Long
    Dim ButtonName, myTotal As String
    With ActiveWorkbook.ActiveSheet

    CaptionName = Range("I42").Value
    ButtonName = "FundType"
    .Shapes.AddOLEObject(Left:=265, Top:=708, Width:=90, Height:=22, _
    ClassType:="Forms.CommandButton.1").Name = ButtonName
    .OLEObjects(ButtonName).Object.Caption = CaptionName
    .OLEObjects(ButtonName).PrintObject = False
    End With

    mySum = "req.xls!" & ButtonName
    myTotal = "Application" & Chr(46) & "Run " & Chr(34) & mySum & Chr(34)
    With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
    lngLine = .CreateEventProc("Click", ButtonName) CRASHES HERE
    .InsertLines lngLine + 1, vbTab & myTotal
    .DeleteLines lngLine + 2
    End With

    Any ideas?

    Regards,

    Deni

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Code Crashes excel (Excel 200 VBA)

    1. Are you sure that your worksheet is named Sheet1?

    2. If you have run the code and failed, the name FundType may be "in use". Try giving the button another name, for example "FundType2"

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Crashes excel (Excel 200 VBA)

    It might also help to move the code to a sub in a normal module and call that sub from workbook_open.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •