vba 6.3 Office 2003/2000 Error

[anathema]

Hi,

I've got a bit of an issue regarding vba 6.3

I've written a piece of code to ease some data entry issues we have at work
in Office 2000. The code is pretty simple - 3 forms (1 form lauched from
another), 2 of them with combiboxes, the contents of which are changed
depending on selections in other boxes i.e. they offer selection options
unique to the previous selection.

However, my computer has Project 2003 installed, which the users' computers
do not and when they try and open my file, they are told that certain objects
or libraries do not exist. If they try and run the code, the debugger throws
up errors with the Date and Mid functions despite the syntax being correct.
They also can't view spreadsheet objects I have embedded in one of the forms.

After a bit of trial and error I've found that the code will run on
computers with Project 2003 installed, but not computers without. Also, if i
try and save the workbook as an Excel 2000 file, my version of Excel crashes.

As project 2003 licenses cost money, and the users have no need for this
software, how can I resolve this issue?

Thanks in advance!

 

[Jan Karel Pieterse]

Hi Anathema,

As project 2003 licenses cost money, and the users have no need for this
software, how can I resolve this issue?

By using ONLY controls that are native to plain vanilla Excel. You have
obviously used the "More controls" choice on the control toolbox in the VBE and
selected one or more which are part of Project, not of Excel.
There is no way around your problem except either install project on every
machine, OR use controls that are not part of project (or maybe something else
that comes with your office version and does not come with the version of the
others).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[anathema]

By using ONLY controls that are native to plain vanilla Excel. You have
obviously used the "More controls" choice on the control toolbox in the VBE and
selected one or more which are part of Project, not of Excel.
There is no way around your problem except either install project on every
machine, OR use controls that are not part of project (or maybe something else
that comes with your office version and does not come with the version of the
others).

I'll admit I used the Microsoft Office spreadsheets object because one of
the forms runs through entered data to find out which months the sheet has
been used for and prompt for some further information. This is therefore a
dynamic function so i didn't want to uses a limited number of textboxes in
the form. That also complicates using the requested information in
calculations.

However I got the spreadsheet object via more controls in Exce and it is an
Office object. Also, if I open the sheet in an older version of Excel and
manually reproduce the spreadsheet with the Office 2000 version, it still
throws up issues with the Mid and Date functions as mentioned previously, so
it is not just the spreadsheet causing an issue.

 

[Jan Karel Pieterse]

Hi Anathema,

However I got the spreadsheet object via more controls in Exce and it is an
Office object. Also, if I open the sheet in an older version of Excel and
manually reproduce the spreadsheet with the Office 2000 version, it still
throws up issues with the Mid and Date functions as mentioned previously, so
it is not just the spreadsheet causing an issue.

Whether it is an office object or not does not mean you are guaranteed to find
it on all systems with Office, they may have chosen not to install the Office
web components.

Check Tools, References (VBE). If any are noted MISSING, you get those compile
errors on DATE and MID, but they are unrelated to these functions, they are
caused by the missing reference.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[anathema]

Thanks Jan, that seems to have done the trick.

I rebuilt the spreadsheet on a computer without Office 2003, or any other
Microsoft 2003 programmes, and it seems to be working now. I am still using
the spreadsheet object, so I realise I may have some web components issues
occasionally, but that is not a problem to resolve as/when it occurs.

Thanks for your help!

 

[Jan Karel Pieterse]

Hi Anathema,

I rebuilt the spreadsheet on a computer without Office 2003, or any other
Microsoft 2003 programmes, and it seems to be working now. I am still using
the spreadsheet object, so I realise I may have some web components issues
occasionally, but that is not a problem to resolve as/when it occurs.

Great you have solved your issue, thanks for letting us know.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi Anathema,

I rebuilt the spreadsheet on a computer without Office 2003, or any other
Microsoft 2003 programmes, and it seems to be working now. I am still using
the spreadsheet object, so I realise I may have some web components issues
occasionally, but that is not a problem to resolve as/when it occurs.

Great you have solved your issue, thanks for letting us know.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com