Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enabling Insert Toolbar (2003; sp1)

    I have an Enduser who encounters an issue with the following line of code. I have a good number of Users who are not having an issue. We all utilize the same version of Excel and Service Pack.

    With Application.CommandBars("Insert")
    .Controls("Picture").Enabled = False 'This is the line which is causing an issue.
    .Controls("Object...").Enabled = False
    .Controls("Hyperlink...").Enabled = False
    End With

    Does anyone have and idea as to why?

    Thanks,
    John

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

    Re: Enabling Insert Toolbar (2003; sp1)

    Can you provide more information?
    - What error message does the user get?
    - When does the error occur? Is this code run when Excel is started, for example?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Hello Hans,

    The error message the EndUser received is" Run-Time error 5-Invalid procedure call or argument"

    The error occurs when the workbook is opened. The code is part of "ThisWorkbook" and incorporated in Private Sub Workbook_Activate().

    Thanks for taking the time to look at it.

    John

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

    Re: Enabling Insert Toolbar (2003; sp1)

    It might be a timing problem. You could try the following:

    - Put the code to disable context menu items in a separate procedure in a standard module. Let's say you call the procedure DisableInsert.
    - Use Application.OnTime to call DisableInsert from the Workbook_Activate event procedure:

    Private Sub Workbook_Activate()
    Application.OnTime Now + TimeSerial(0,0,2), "DisableInsert"
    End Sub

    You can vary the delay (2 seconds in the above code).

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Hans,

    I tried you suggestion and the EndUser is still encountering the same challenge. I also tried increasing the delay to four seconds with the same end results.

    Regards,
    John

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

    Re: Enabling Insert Toolbar (2003; sp1)

    Perhaps this user has a customized Insert menu without a Picture submenu?
    Or the Insert menu has been protected? (This can be done in code)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Hans,

    There are no custoniezed Insert menus. I verified this prior to making my post. I also check to ensure that the Insert menu was not protected prior to opening the file.

    John

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

    Re: Enabling Insert Toolbar (2003; sp1)

    I'm out of ideas, apart from brute force approaches such as reinstalling the user's Office, or firing the user...

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    If you comment out that line, do the following two lines work OK?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Hans/Rory,

    I tried remarking out the first lines of code to test the other two and continue to encounter the error message.

    I am going to have someone reinstall Excel to see if this issue goes away.

    Thanks for you help. I'll keep you posted.
    John

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Well the reinstall of Excel did not work.

    This one will make one have fewer hairs. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    John

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Does the person in question have an .xlb file in the Documents and settingsusernameapplication datamicrosoftexcel folder? If so , are there definitely no customisations to that toolbar?
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Rory,

    I have checked the folder in question for any "xlb" files. There are none. I also asked the EndUser and he confirmed that there are none. I created the file in question and there are no custom toolbars. I simply turn on/off various features of the standard toolbar.

    I also created a new workbook with only the code to disable the "insert" section and that failed as well.

    Regards,
    John

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

    Re: Enabling Insert Toolbar (2003; sp1)

    There MUST be an .xlb file, it is created automatically.
    Note: the Application Data folder is hidden, so you must show hidden files and folders in Windows Explorer to be able to see Excel11.xlb.

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enabling Insert Toolbar (2003; sp1)

    Hans,

    I saw an Excel11 file and cut/paste it to another folder. Tried to retrieve the file in question and with the same end results. I doubled-back and checked for the Excel11 file again and sure enough there it was.

    I thought Excel was creating this on the fly.

    John

Page 1 of 3 123 LastLast

Posting Permissions

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