Convert Macros Enabled 2003 to Excel 2007

[Guest]

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

 

[Guest]

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

That is the very first line of the Sub in question? That is, it is the very
start of the SUB and not a line within it?

You might try changing the name of the Sub from SubUpdateLists() to simply
UpdateLists() and seeing if that makes a change. Excel 2007 may not like:
Sub Sub...()
for some reason.

I'll check and see if I can create such an error here by building a file
with a sub with that name in 2003 and see how it acts in 2007. Might help if
you can email me the entire file (the 2003 version) to [remove spaces]
HelpFrom @ jlathamsite.com and maybe I can duplicate it here.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

Well, just having a sub with a name like that didn't cause me any problems.

Yes, you can send screen shots, and even copies of both workbooks (if
they're not too large) to HelpFrom @ jlathamsite.com (remove the spaces). My
email total attachment size limit is about 8MB (which gets expanded to over
the limit of 10MB by the time the email servers get done with it all). I can
deal with .zip files if you need to send anything that way.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

I sent you a file. It is in a zip file, so you may need to check your junk
file.
Thanks

That is the very first line of the Sub in question? That is, it is the very
start of the SUB and not a line within it?

You might try changing the name of the Sub from SubUpdateLists() to simply
UpdateLists() and seeing if that makes a change. Excel 2007 may not like:
Sub Sub...()
for some reason.

I'll check and see if I can create such an error here by building a file
with a sub with that name in 2003 and see how it acts in 2007. Might help if
you can email me the entire file (the 2003 version) to [remove spaces]
HelpFrom @ jlathamsite.com and maybe I can duplicate it here.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

:

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

Using the file that you sent to me, I found the problem - it was with the
name of the list box on that sheet. The same problem was on one or two other
sheets also. I'm not sure where the problem really was created. More on
that later.

In your code the list box on sheet7, Chart-Heat Flux, was being referred to
as Sheet7.LST_Energy.Clear and on sheet 71, Chart-Diel Temp, the list box was
being referred to as Sheet71.LST_Temps.Clear. I think a similar situation
may have existed in a 3rd sheet. Problem was that those list boxes were not
named LST_Energy or LST_Temps, they had the original name given to them by
the system, ListBox1 and so Excel 2007 could not find them. I changed their
names and the code appears to function properly now.

The reason I said that I'm not sure where the problem really is/was because
I went back and looked at the Excel 2003 file and the names were incorrect in
it also, so it's curious that the code functions properly in that file.
Initially I thought that the conversion to 2007 had 'lost' the names of some
of them, but that doesn't appear to be the case.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

What did you change the names of the sheets to?

Using the file that you sent to me, I found the problem - it was with the
name of the list box on that sheet. The same problem was on one or two other
sheets also. I'm not sure where the problem really was created. More on
that later.

In your code the list box on sheet7, Chart-Heat Flux, was being referred to
as Sheet7.LST_Energy.Clear and on sheet 71, Chart-Diel Temp, the list box was
being referred to as Sheet71.LST_Temps.Clear. I think a similar situation
may have existed in a 3rd sheet. Problem was that those list boxes were not
named LST_Energy or LST_Temps, they had the original name given to them by
the system, ListBox1 and so Excel 2007 could not find them. I changed their
names and the code appears to function properly now.

The reason I said that I'm not sure where the problem really is/was because
I went back and looked at the Excel 2003 file and the names were incorrect in
it also, so it's curious that the code functions properly in that file.
Initially I thought that the conversion to 2007 had 'lost' the names of some
of them, but that doesn't appear to be the case.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

:

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR

 

[Guest]

I didn't change the names of sheets at all. I tried to send the repaired
file back to you this morning, came home only to find that the email had
bounced due to its size. I'm uploading it to my site and I will email you
with a link to it. It's odd, the Excel 2007 file size is about 9MB or half
the size of the original .xls file, but it compresses less - only went down
to roughly 8MB vs the 5MB that the original 18M .xls file compressed to.

What did you change the names of the sheets to?

Using the file that you sent to me, I found the problem - it was with the
name of the list box on that sheet. The same problem was on one or two other
sheets also. I'm not sure where the problem really was created. More on
that later.

In your code the list box on sheet7, Chart-Heat Flux, was being referred to
as Sheet7.LST_Energy.Clear and on sheet 71, Chart-Diel Temp, the list box was
being referred to as Sheet71.LST_Temps.Clear. I think a similar situation
may have existed in a 3rd sheet. Problem was that those list boxes were not
named LST_Energy or LST_Temps, they had the original name given to them by
the system, ListBox1 and so Excel 2007 could not find them. I changed their
names and the code appears to function properly now.

The reason I said that I'm not sure where the problem really is/was because
I went back and looked at the Excel 2003 file and the names were incorrect in
it also, so it's curious that the code functions properly in that file.
Initially I thought that the conversion to 2007 had 'lost' the names of some
of them, but that doesn't appear to be the case.

Is there a way to get a screen shot to you?
The initial error says:
"Compile Error Method or data member not found" Then it shows what sheet
is missing or has an error which is sheet 7.lst.energy.clr
However, the sheet is fine before it is converted. I have tried this with
more than one file and it happens each time...The line of code highlighted is
Sub SubUpdateLists ()

Thanks


:

A starting point for analysis:
First - what does the VB Error say? What error number does it report?
Second, when the error comes up next time, click the [Debug] button and see
what line of code is highlighted - that's the line generating the error. It
would then help if you post the entire section of code (entire sub or
function) and let us know which line in it is the problem statement.

:

I am having a problem converting an excel 2003 spreadsheet with macros. It
opens and runs fine if I open it as the original version. However if I
convert it or save as a excel macro-enabled workbook (2007) then try to run
the macros, I get a visual basic error. Any suggestions?
Thank you
DMR