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

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

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

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