shared workbook runtime error 1004

Hi

I have a small excel estimate form which needs to be able to expand to accomodate extra information when required. When finished I simply print the form off and want to return the form back to its original size with all the entered data removed. I recorded a macro which deleted the form and pasted in its place a copy of the original stored on a different worksheet. Worked great untill I tried to share it.........now I get "run time error 1004" The way the worksheet is setup means I would rather not just delete the whole sheet and replace it with one stored as a template which is what the microsoft knowledge base suggests. If anybody has any ideas I would be grateful..........to date my ability to compile macros is limited to the "record" function but im happy to have a go if someone can point me in the right direction.

Many thanks

Hi Nigel,

>The way the worksheet is
> setup means I would rather not just delete the whole sheet and replace
> it with one stored as a template which is what the microsoft knowledge
> base suggests.

If the template sheet (workbook?) shows the blank form exactly as you want
it, the suggested approach would seem to present few problems.

Why would this appoach not appeal?

---
Regards,
Norman



"Nigel" <Nigel.1swh2n_1122588368.772@excelforum-nospam.com> wrote in message
news:Nigel.1swh2n_1122588368.772@excelforum-nospam.com...
>
> Hi
>
> I have a small excel estimate form which needs to be able to expand to
> accomodate extra information when required. When finished I simply
> print the form off and want to return the form back to its original
> size with all the entered data removed. I recorded a macro which
> deleted the form and pasted in its place a copy of the original stored
> on a different worksheet. Worked great untill I tried to share
> it.........now I get "run time error 1004" The way the worksheet is
> setup means I would rather not just delete the whole sheet and replace
> it with one stored as a template which is what the microsoft knowledge
> base suggests. If anybody has any ideas I would be grateful..........to
> date my ability to compile macros is limited to the "record" function
> but im happy to have a go if someone can point me in the right
> direction.
>
> Many thanks
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile:
> http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=391122
>

Hi Norman and thanks for the reply.

If the truth is known havent been able to use the template solution because I cant figure out the VB script needed to do it and thought there might be an easier way. I did try but got a "syntax" error on the address line (which I know was correct as I tried the same address in a hyperlink and it worked fine). If you could give me a script example it would be a great help.

Regards and thanks

Nigel

Hi Nigel,

As I read your requirements, no code would be required.

To set up an Estimates template sheet:

Open a new workbook
Copy (or create) a sheet with the estimate form.
Set all of the form / sheet formats as desired.
Delete all the remaining sheets.
File | SaveAs | File Name: 'Estimate Sheet' | Save as type: Template(*.xlt)
| Save

Once created and saved, the template sheet can be added to any workbook
(right-click the worbook tab | Insert)

If, rather than a single template sheet, you require a template workbook,
retain/add any sheets you need, add any required formats and data. Save as
above with a suitable identifying name.

---
Regards,
Norman



"Nigel" <Nigel.1sx648_1122621119.2366@excelforum-nospam.com> wrote in
message news:Nigel.1sx648_1122621119.2366@excelforum-nospam.com...
>
> Hi Norman and thanks for the reply.
>
> If the truth is known havent been able to use the template solution
> because I cant figure out the VB script needed to do it and thought
> there might be an easier way. I did try but got a "syntax" error on the
> address line (which I know was correct as I tried the same address in a
> hyperlink and it worked fine). If you could give me a script example it
> would be a great help.
>
> Regards and thanks
>
> Nigel
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile:
> http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=391122
>

Many many thanks for taking the time to reply Norman, I really appreciate it. I did try your suggestion and it worked untill I ticked "share workbook" then after that it just came up with "this command is not available in a shared workbook". The estimate form which is causing me so many sleepless nights needs to be cleared of all entered data and resized back to the original with a single click on a button located on the spread sheet. The intention is that it would be used very frequently by lots of different people in various parts of the building. Data may need to be entered by 2 people at the same time so it has to be shared and to expect them to manually cut and paste when the form was finished with just would not happen. As I said the Micosoft workaround says.."To insert the template progammatically, use the following code: Sheets.Add Type:=path\filename" Sadly I am not experienced enough to work out the rest of the code needed to incorperate that into a macro which will give me the single button click operation I am looking for.........any ideas?

Regards

Nigel

Hi Nigel,

You are correct that Shared workbooks will not allow the insertion of a new
worksheet.

You can, however, run pre-existing macros.

Therefore, temporarily unshare the workbook,

Remove any input data from the form etc.. Create a copy of the form sheet
(right-click the sheet tab | Move or Copy | Create Copy). Name the new
sheet as FormTemplate and hide it. On the original sheet, add a button from
the Control Toolbox. Right-click the sheet tab and paste the folowing code:

Private Sub CommandButton1_Click()
Me.Cells.ClearContents
Sheets("MyForm").Cells.Copy Destination:=Me.Range("A1")
End Sub

Re-Share the workbook and save it.

Thereafter, clicking the button will replace the sheet's contents wth a
blank, resized form.


---
Regards,
Norman



"Nigel" <Nigel.1sy3ej_1122663956.0621@excelforum-nospam.com> wrote in
message news:Nigel.1sy3ej_1122663956.0621@excelforum-nospam.com...
>
> Many many thanks for taking the time to reply Norman, I really
> appreciate it. I did try your suggestion and it worked untill I ticked
> "share workbook" then after that it just came up with "this command is
> not available in a shared workbook". The estimate form which is causing
> me so many sleepless nights needs to be cleared of all entered data and
> resized back to the original with a single click on a button located on
> the spread sheet. The intention is that it would be used very frequently
> by lots of different people in various parts of the building. Data may
> need to be entered by 2 people at the same time so it has to be shared
> and to expect them to manually cut and paste when the form was finished
> with just would not happen. As I said the Micosoft workaround says.."To
> insert the template progammatically, use the following code: Sheets.Add
> Type:=path\filename" Sadly I am not experienced enough to work out the
> rest of the code needed to incorperate that into a macro which will
> give me the single button click operation I am looking for.........any
> ideas?
>
> Regards
>
> Nigel
>
>
> --
> Nigel
> ------------------------------------------------------------------------
> Nigel's Profile:
> http://www.excelforum.com/member.php...o&userid=25696
> View this thread: http://www.excelforum.com/showthread...hreadid=391122
>