shared workbook runtime error 1004

[Nigel]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Nigel]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Nigel]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Nigel]

Norman................thanks..........will try it over the weekend...............and let you know how I got on on monday..................thanks very much for your time and trouble

Regards

Nigel

[Nigel]

Hi Norman.......It worked! I am very grateful to you.

Thanks again

Nigel

[Nigel]

Sorry to be a pain.........but these shared workbook restrictions are making life very difficult for a newbie like me! I want to be able to expand my estimate sheet by 1 row when required and retain any formulas or conditional formats that are present from the original row. I recorded a macro which again is fine in unshared mode but returns a runtime error when shared.

Keyboard Shortcut: Ctrl+l
'
Range("b10..h10").Select
Selection.Insert Shift:=xlDown
Range("H10").Select
ActiveCell.Formula = "=sum(f10*$o$3)"
End Sub

Is there a way of modifying that code which will allow it to run in a shared workbook?

Regards

Nigel

[Norman Jones]

Hi Nigel,

Shared workbooks are subject to a number of restrictions. See 'Limitations
of shared workbooks' in Excel help,

These restrictions include inserting blocks of cells (as you are trying to
do); you can however insert complete rows or columns.

Another restriction prevents the new application of conditional formats:
conditional formatting applied prior to sharing are permitted.

Taking these two restrictions in conjunction, if your application is to use
conditional formatting, you cannot insert any cells once the workbook is
shared.

It is therefore incumbent on you to design the application with all the
potentially needed cells in place and conditionally formatted prior to
sharing. If size is an issue, perhaps you could consider hiding and
selectively unhiding entire rows.

As a possible alternative to the insertion of entire rows, perhaps you could
have differently sized templates on hidden sheets and use a button's click
event to replace the used form sheet with the larger template sheet and
copy the form's data to the larger template.

I would suggest that, during your development cycle, you perform all desired
operations manually. That way, you will immediately be made aware of
feasibility and any restricted activity. Once you have established how you
can accomplish your objectives, write your code.

Given the restrictions and my lack of familiarity with shared workbooks, I
regret that I can only suggest an empirical approach.


---
Regards,
Norman



"Nigel" <[email protected]> wrote in
message news:[email protected]...
>

a.. Insert or delete blocks of cells. You can insert or delete entire rows
and columns.
I want to be able to
> expand my estimate sheet by 1 row when required and retain any formulas
> or conditional formats that are present from the original row. I
> recorded a macro which again is fine in unshared mode but returns a
> runtime error when shared.
>
> Keyboard Shortcut: Ctrl+l
> '
> Range("b10..h10").Select
> Selection.Insert Shift:=xlDown
> Range("H10").Select
> ActiveCell.Formula = "=sum(f10*$o$3)"
> End Sub
>
> Is there a way of modifying that code which will allow it to run in a
> shared workbook?
>
> 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
>

[Nigel]

Hi Norman

By the sound of it I have some redesigning to do! Its so frustraiting as I didnt realise the restrictions effecting a shared workbook when I set out on my project.......so I suppose if nothing else Ive learnt something!

Thanks again for your time and I will experiment with the different solutions you have given me.

Regards

Nigel