Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error in sheet... sometimes?? (ExcelXP)

    I built a sheet with some VBA code and tested it on my system (and various others). All worked fine. Then I 'rolled it out' and some people get errors that are almost unbelievable... (I think).

    I use:

    [Name] to refer to a named range, I had to change this to Range("Name") to avoid an error (library)

    Now the VBA FORMAT commend creates the same error.


    I found out that COMCT332.OCX Microsoft Windows Common Controls-3 6.0 (SP5) appears to be missing on target systems that show errors.

    (I also found that the target system is ExcelXP SP2 and mine is SP3 --- but I doubt if that's the reason).


    I did some Internet searches and found that this might have to do with VB6 (which I have installed) but I cannot believe that if I use the FORMAT command in Excel this would have anything to do with that....

    Anyone who can shed some light on this mystery and maybe tell me what can be done about this?? (I doubt if I can change the target systems, can I do something myself maybe??)

    Thanks,

    Erik Jan

  2. #2
    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: Error in sheet... sometimes?? (ExcelXP)

    Hi,
    If you have a missing reference in a workbook, you will get code errors which often bear no relation to the missing reference - standard functions like Mid, Left, Format etc. In this case, as you have found, your missing Common Controls reference is breaking the rest of the code. The only way around it is to either install the Common Controls on the systems in question (which may not be possible) or code around it.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error in sheet... sometimes?? (ExcelXP)

    OK, but how did I get to require that common controls in the first place... I think I used only "normal" VBA code so it should work on all out Excel installs.

    I think I must have selected the wrong button (or other graphical element) in my (very simple) user form and picked one that 'belongs' to VB6 and not VBA.... How do I know and better still: how do I correct?

    Erik Jan

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

    Re: Error in sheet... sometimes?? (ExcelXP)

    It is errors like these that make me avoid using controls that are not listed in the standard Excel VBA set.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    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: Error in sheet... sometimes?? (ExcelXP)

    If you don't actually use the Common Controls in your workbook, uncheck the reference to it then compile the workbook code. Assuming that compiles OK, save it and then redistribute it. If you do use the control, I would suggest the easiest thing is to check what, if any, version appears on the computers that don't currently work and try and locate that one on your own machine to use instead.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error in sheet... sometimes?? (ExcelXP)

    Yes... I don't know what happened or how it happened but I did as you suggested and simply unchecked the control in the "References" dialog. I recompiled and got no errors (much to my surprise) so I re-distributed. First signals indicate the problem is now solved :-)

    Erik Jan

Posting Permissions

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