Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
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" 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
Brad -
I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
Hi Jon,
You said: "I never link userform controls directly to the worksheet through RowSource". If you don't use RowSource, what method would you use that is = to RowSource. I am behind on the times, and trying to catch up. Thanks, Alan "Jon Peltier" wrote in message ... Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
Jon,
Thanks for your reply. Since the data in this program is not passed between the userforms and the worksheet and module subroutines using properties you probably are correct as to why this linakage may have been lost. It's too bad your best practices are published somewhere with examples so we can receive the benefit of your experience. -- Brad Wylie "Jon Peltier" wrote: Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
1. The procedure which calls the userform reads the worksheet data, converts
it into an array or other suitable variable, and passes it into the userform as a property. 2. The userform accepts the data through the property, and uses it as appropriate, for example populating a listbox with an array, inserting a string into a label, or putting a default value into a textbox. 3. When the userform hides itself (when the user clicks OK or Cancel, for example), the calling procedure extracts the necessary information from the userform using properties, then uses it appropriately, perhaps by updating the range in the worksheet. Through the use of these properties, I have separated the userform from worksheet ranges. This means the calling procedure can get the data from any arbitrary range or from some other array, and the userform doesn't need to know about it or be reprogrammed. The userform can be redesigned, perhaps changing a listbox to a combobox, and the calling procedure doesn't need to know about it or be reprogrammed. I have one page on my web site which discusses userform properties: http://peltiertech.com/Excel/PropertyProcedures.html I have ideas for more userform tutorials, but limited time to write these pages. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alan" wrote in message ... Hi Jon, You said: "I never link userform controls directly to the worksheet through RowSource". If you don't use RowSource, what method would you use that is = to RowSource. I am behind on the times, and trying to catch up. Thanks, Alan "Jon Peltier" wrote in message ... Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
Maybe instead of writing my own pages, I should just tell you where to find
the information. Chapter 10 of Professional Excel Development by Bullen, Bovey, and Green is entitled Userform Design and Best Practices. I base much of my use of Userforms on topics in this chapter. I consider the book itself the definitive Excel Programming resource, because it contains many useful and practical examples in a form which can be easily implemented in your own projects. Here's a link to the book: http://www.amazon.com/exec/obidos/re...&creative=9325 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jon Peltier" wrote in message ... 1. The procedure which calls the userform reads the worksheet data, converts it into an array or other suitable variable, and passes it into the userform as a property. 2. The userform accepts the data through the property, and uses it as appropriate, for example populating a listbox with an array, inserting a string into a label, or putting a default value into a textbox. 3. When the userform hides itself (when the user clicks OK or Cancel, for example), the calling procedure extracts the necessary information from the userform using properties, then uses it appropriately, perhaps by updating the range in the worksheet. Through the use of these properties, I have separated the userform from worksheet ranges. This means the calling procedure can get the data from any arbitrary range or from some other array, and the userform doesn't need to know about it or be reprogrammed. The userform can be redesigned, perhaps changing a listbox to a combobox, and the calling procedure doesn't need to know about it or be reprogrammed. I have one page on my web site which discusses userform properties: http://peltiertech.com/Excel/PropertyProcedures.html I have ideas for more userform tutorials, but limited time to write these pages. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alan" wrote in message ... Hi Jon, You said: "I never link userform controls directly to the worksheet through RowSource". If you don't use RowSource, what method would you use that is = to RowSource. I am behind on the times, and trying to catch up. Thanks, Alan "Jon Peltier" wrote in message ... Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
I believe I will call you Mr. Peltier from now on, lol. Being self taught
through Help, Internet searches, and hands on, I see that I have missed some important steps alone the way. I really appreciate the time you took to enlighten me. I know have another path to research and learn, thanks to you. I do have the Excel 2002 VBA Programmer's Reference but am unimpressed with their form section. It is very limited, and I do not remember reading this philosophy in their pages, but maybe I should go back and read it again to see. I will acquire your recommended literature for I see I have a lot to learn. Who says you can't teach old dogs new tricks. Is it possible that my lengthy use of QBasic has caused irreparable damage? LOL. Thanks & Regards, Alan "Jon Peltier" wrote in message ... 1. The procedure which calls the userform reads the worksheet data, converts it into an array or other suitable variable, and passes it into the userform as a property. 2. The userform accepts the data through the property, and uses it as appropriate, for example populating a listbox with an array, inserting a string into a label, or putting a default value into a textbox. 3. When the userform hides itself (when the user clicks OK or Cancel, for example), the calling procedure extracts the necessary information from the userform using properties, then uses it appropriately, perhaps by updating the range in the worksheet. Through the use of these properties, I have separated the userform from worksheet ranges. This means the calling procedure can get the data from any arbitrary range or from some other array, and the userform doesn't need to know about it or be reprogrammed. The userform can be redesigned, perhaps changing a listbox to a combobox, and the calling procedure doesn't need to know about it or be reprogrammed. I have one page on my web site which discusses userform properties: http://peltiertech.com/Excel/PropertyProcedures.html I have ideas for more userform tutorials, but limited time to write these pages. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alan" wrote in message ... Hi Jon, You said: "I never link userform controls directly to the worksheet through RowSource". If you don't use RowSource, what method would you use that is = to RowSource. I am behind on the times, and trying to catch up. Thanks, Alan "Jon Peltier" wrote in message ... Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Excel 2007 Userforms from Macros
Alan -
"Jon" is fine. I'm self-taught as well through the same avenues as you list, plus all of these forums. But I've been doing it for a long time, since about Excel 4. A lot of trial and error. The 2002 Programmer's Reference is not a bad resource, and I refer to it often, but it's a reference. The PED book, in contrast, is almost a narrative, and it does a great job of providing useful self-contained examples and also a broader overall view of how the technologies can work together. After having this book for less than a week, I felt I had made a great step forward in my own development abilities, and it still teaches me things. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alan" wrote in message ... I believe I will call you Mr. Peltier from now on, lol. Being self taught through Help, Internet searches, and hands on, I see that I have missed some important steps alone the way. I really appreciate the time you took to enlighten me. I know have another path to research and learn, thanks to you. I do have the Excel 2002 VBA Programmer's Reference but am unimpressed with their form section. It is very limited, and I do not remember reading this philosophy in their pages, but maybe I should go back and read it again to see. I will acquire your recommended literature for I see I have a lot to learn. Who says you can't teach old dogs new tricks. Is it possible that my lengthy use of QBasic has caused irreparable damage? LOL. Thanks & Regards, Alan "Jon Peltier" wrote in message ... 1. The procedure which calls the userform reads the worksheet data, converts it into an array or other suitable variable, and passes it into the userform as a property. 2. The userform accepts the data through the property, and uses it as appropriate, for example populating a listbox with an array, inserting a string into a label, or putting a default value into a textbox. 3. When the userform hides itself (when the user clicks OK or Cancel, for example), the calling procedure extracts the necessary information from the userform using properties, then uses it appropriately, perhaps by updating the range in the worksheet. Through the use of these properties, I have separated the userform from worksheet ranges. This means the calling procedure can get the data from any arbitrary range or from some other array, and the userform doesn't need to know about it or be reprogrammed. The userform can be redesigned, perhaps changing a listbox to a combobox, and the calling procedure doesn't need to know about it or be reprogrammed. I have one page on my web site which discusses userform properties: http://peltiertech.com/Excel/PropertyProcedures.html I have ideas for more userform tutorials, but limited time to write these pages. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Alan" wrote in message ... Hi Jon, You said: "I never link userform controls directly to the worksheet through RowSource". If you don't use RowSource, what method would you use that is = to RowSource. I am behind on the times, and trying to catch up. Thanks, Alan "Jon Peltier" wrote in message ... Brad - I have a handful of utilities which I've tested in Excel 2007, just to see how much rework they were going to need. Most of these utilities use some manner of userforms. While I had a few minor problems with the utilities, none of the problems were related to the userforms. However, I'm pretty obsessive about minimizing the linkage between modules and userforms and worksheets. I'm careful with userform initialization routines, my forms are passed their data using properties, and I never link userform controls directly to the worksheet through RowSource and other traps. I only use a handful of public subs in the userform, and I never reference a control directly from outside of the userform. There are too many ways that you can lose control over your program, and perhaps you've stumbled across one or more. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Brad Wylie" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Anyway of accessing the internet from within excel 2007? | Excel Discussion (Misc queries) | |||
Accessing Excel Macros from VB.Net | Excel Programming | |||
Accessing Web Service in the Excel 2007 | Excel Programming | |||
accessing .mpp file using excel VBA macros | Excel Programming | |||
excel 2002 -Userforms and macros help | Excel Programming |