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

You will get faster and better results if you post your code.

Regards,

Alan

"Brad Wylie" wrote in message
...
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

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" wrote in message
...
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

Hopefully, someone with Excel 2007 will step in and help out. We are behind
the times, 2002. I did look around a bit for you. Maybe you will find
something to help you he

"Brad Wylie" wrote in message
...
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" wrote in message
...
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

Sorry, I forgot to paste the link.

Hopefully, someone with Excel 2007 will step in and help out. We are behind
the times, 2002. I did look around a bit for you. Maybe you will find
something to help you he

http://office.microsoft.com/en-us/ex...648071033.aspx

Regards,

Alan


"Brad Wylie" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"how does Excel 2007 link these two folders together"

I don't know what this means.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Brad Wylie" wrote in message
...
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