Jon,
This was one of the things I tried but it didn't work. I wound up with the
same error. Have you tried this and it worked?
The only thing I found I could import, well actually copy, was the code.
The only way I was successful (after many different configurations) was when
I deleted the form and then inserted the form again and rebuilt the controls
using the same names as before. Then I could copy the code back and have
everything execute as it did in Excel 2003.
Something must be happening during this conversion so that references
(linkage) between the form and the Module and Sheet subroutines is getting
destroyed. Once this is reestablished all the existing code seems to work
fine. I wonder if Microsoft ever tried this or if there is something special
in my prorgam that creates this problem. This workbook was orginally created
in Office 2000 and has worked fine under all subsequent releases until Office
2007.
--
Brad Wylie
"Jon Peltier" wrote:
> Save a load of time by exporting the forms, deleting them, then reimporting
> them.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "Brad Wylie" <(E-Mail Removed)> wrote in message
> news:2233604E-F710-4982-B47D-(E-Mail Removed)...
> > Alan,
> >
> > Thanks for your research.
> > I did look up the references you posted. The one in the last reply is
> > similar to an addin that MS supplies for each of the new office 2007
> > products
> > that you can install called "getting started". It installs a tab on the
> > ribbon so you can run an interactive session to find out what and where
> > the
> > old commands are located in the new version and a few other helpful bits
> > of
> > information.
> >
> > I did do the convert command to initially create the new Excel 2007 in
> > native format (.xlsm). It doesn't appear that MS made any changes in the
> > VBA
> > code functionality as the old code runs fine once the forms problem is
> > resolved.
> >
> > I have pretty much decided after exhausting what knowledge I have that the
> > only solution is to delete the forms from the resaved version of the old
> > file
> > and then recreated them with the controls. Then the old forms code for
> > the
> > controls can be recopied to the new form.
> >
> > Brad
> > --
> > Brad Wylie
> >
> >
> > "Alan" wrote:
> >
> >> From what I've read, 2007 opens a pre 2007 file in Compatibility Mode. I
> >> see
> >> there is a Microsoft Office Button you can click, then click Convert. You
> >> might look into this and find out if there is a way to code the convert
> >> command into your macro. They do strongly suggest to keep the documents
> >> in
> >> the previous version since it will be useless to all that don't have a
> >> reader. Like I said, this office is still on 2002. It does state there
> >> are
> >> functions and features that are not compatible with 2007.
> >>
> >> I found a link to a MS Excel 2003 to Excel 2007 Command Reference guide.
> >> Maybe this will help.
> >>
> >> http://www.microsoft.com/downloads/d...playlang=en&tm
> >>
> >>
> >> Regards,
> >>
> >> Alan
> >>
> >>
> >>
> >> "Brad Wylie" <(E-Mail Removed)> wrote in message
> >> news:BFA0FCE5-A942-4C7C-8F00-(E-Mail Removed)...
> >> > Alan,
> >> >
> >> > There really wasn't much code to post other than the statement which I
> >> > put
> >> > in my original note. The code is posted below and some additional
> >> > information I have uncovered since the original post.
> >> >
> >> > When the workbook opens it makes the call to show the form and
> >> > generates
> >> > the
> >> > error I noted "subscript out of range". It appears somehow when the
> >> > old
> >> > .xls
> >> > workbook is resaved in the new native Excel 2007 .xlsm format the
> >> > worksheet
> >> > macros are unable to find the form code. The references are lost. I
> >> > have
> >> > found that if you delete all the forms from the .xlsm workbook and then
> >> > recreate them along with thier associated code everything works fine.
> >> > However if this what one must do to convert old Excel programs with VBA
> >> > forms
> >> > code to the new format they just may stay in the old format.
> >> >
> >> > Private Sub Workbook_Open()
> >> >
> >> > MsgBox "Do you want to run payroll now?", vbYesNo, "This Workbook
> >> > Coding"
> >> > If vbYes Then
> >> > frmPayrollForm.Show
> >> > MainPayrollModule 'call Main module to finish processing &
> >> > reporting
> >> > End If
> >> > ' Initially place us on the Actual sheet at the top
> >> > Sheets("Actual").Select
> >> > Range("C4").Select
> >> >
> >> >
> >> > End Sub
> >> >
> >> > --
> >> > Brad Wylie
> >> >
> >> >
> >> > "Alan" wrote:
> >> >
> >> >> You will get faster and better results if you post your code.
> >> >>
> >> >> Regards,
> >> >>
> >> >> Alan
> >> >>
> >> >> "Brad Wylie" <(E-Mail Removed)> wrote in message
> >> >> news:7072E6D7-D77D-4D5B-8062-(E-Mail Removed)...
> >> >> > In Excel 2003 I could access my user form from the macro running in
> >> >> > an
> >> >> > an
> >> >> > Excel worksheet using frmPayrollForm.show. In Excel 2007 this same
> >> >> > statement
> >> >> > creates a "subscript out of range" error message. Also when
> >> >> > accessing
> >> >> > subroutines in the userform code from the macro it creates an error
> >> >> > message
> >> >> > "sub or function not defined" error message. The form has to be
> >> >> > accessed
> >> >> > from the spreadsheet to start so how does Excel 2007 link these two
> >> >> > folders
> >> >> > together so the form is accessable.
> >> >> > --
> >> >> > Brad Wylie
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Similar Threads
