Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog sheet / Checkboxes
Hi All,
I have built a spreadsheet which uses VBA to present the user with a list of choices by creating a dialog sheet "on the fly". The user checks the checkbox to turn it on The list can be up to approx 100 items in length. The problem that l am having is that after 36 iterations the VBA code stops. Each iteration copies an existing worksheet, places it in the workbook before a sheet named "end" then renames it according the the name on the dialog sheet. I am wondering if there are any limitations in Excel re the number of times you can continually copy a worksheet, or there is a limit to the number of worksheets in a workbook, or there is a limit to the number of checkboxes on a dialog sheet or indeed anything else. If exit from break mode where the code stops l am unable to manually copy any sheet and Excel appears to be doing something but nothing happens, Excel just returns to normal. I have tried the code with Excel97 & Excel2000 under various OS and the result is always the same All suggestions greatly received Regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog sheet / Checkboxes
Rob Bovey has previously posted that the problem appears to be the number of
copy actions rather than the number of sheets. His suggested work around was to copy several sheets at once. So perhaps you could copy all the selected sheets at once, then loop through them and do the renaming. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have built a spreadsheet which uses VBA to present the user with a list of choices by creating a dialog sheet "on the fly". The user checks the checkbox to turn it on The list can be up to approx 100 items in length. The problem that l am having is that after 36 iterations the VBA code stops. Each iteration copies an existing worksheet, places it in the workbook before a sheet named "end" then renames it according the the name on the dialog sheet. I am wondering if there are any limitations in Excel re the number of times you can continually copy a worksheet, or there is a limit to the number of worksheets in a workbook, or there is a limit to the number of checkboxes on a dialog sheet or indeed anything else. If exit from break mode where the code stops l am unable to manually copy any sheet and Excel appears to be doing something but nothing happens, Excel just returns to normal. I have tried the code with Excel97 & Excel2000 under various OS and the result is always the same All suggestions greatly received Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog sheet / Checkboxes
See this KB
Copying Worksheet Programmatically Causes Run-Time Error 1004 http://support.microsoft.com/default...84&Product=xlw -- Regards Ron de Bruin http://www.rondebruin.nl "Tom Ogilvy" wrote in message ... Rob Bovey has previously posted that the problem appears to be the number of copy actions rather than the number of sheets. His suggested work around was to copy several sheets at once. So perhaps you could copy all the selected sheets at once, then loop through them and do the renaming. -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have built a spreadsheet which uses VBA to present the user with a list of choices by creating a dialog sheet "on the fly". The user checks the checkbox to turn it on The list can be up to approx 100 items in length. The problem that l am having is that after 36 iterations the VBA code stops. Each iteration copies an existing worksheet, places it in the workbook before a sheet named "end" then renames it according the the name on the dialog sheet. I am wondering if there are any limitations in Excel re the number of times you can continually copy a worksheet, or there is a limit to the number of worksheets in a workbook, or there is a limit to the number of checkboxes on a dialog sheet or indeed anything else. If exit from break mode where the code stops l am unable to manually copy any sheet and Excel appears to be doing something but nothing happens, Excel just returns to normal. I have tried the code with Excel97 & Excel2000 under various OS and the result is always the same All suggestions greatly received Regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dialog sheet / Checkboxes
Tom / Ron
Many thanks for the quick response. Looking @ the information you pointed me in the direction of has enabled me to use a workaround. Not the most elegant of solutions but it works ! Good old Microsoft, "known error", could not see any info about it being fixed in later versions of Excel Regards "Michael Beckinsale" wrote in message ... Hi All, I have built a spreadsheet which uses VBA to present the user with a list of choices by creating a dialog sheet "on the fly". The user checks the checkbox to turn it on The list can be up to approx 100 items in length. The problem that l am having is that after 36 iterations the VBA code stops. Each iteration copies an existing worksheet, places it in the workbook before a sheet named "end" then renames it according the the name on the dialog sheet. I am wondering if there are any limitations in Excel re the number of times you can continually copy a worksheet, or there is a limit to the number of worksheets in a workbook, or there is a limit to the number of checkboxes on a dialog sheet or indeed anything else. If exit from break mode where the code stops l am unable to manually copy any sheet and Excel appears to be doing something but nothing happens, Excel just returns to normal. I have tried the code with Excel97 & Excel2000 under various OS and the result is always the same All suggestions greatly received Regards Michael Beckinsale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add either radio buttons or checkboxes to an excel sheet? | Excel Worksheet Functions | |||
Copy checkboxes to another sheet | Excel Discussion (Misc queries) | |||
Pre-checking checkboxes in a dialog box | Excel Programming | |||
Dialog Sheet not updating | Excel Programming | |||
Custom dialog box on protected sheet | Excel Programming |