Hi

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

Many thank

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112

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" wrote in message
...

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 solutio
because I cant figure out the VB script needed to do it and though
there might be an easier way. I did try but got a "syntax" error on th
address line (which I know was correct as I tried the same address in
hyperlink and it worked fine). If you could give me a script example i
would be a great help.

Regards and thanks

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112

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" wrote in
message ...

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 reall
appreciate it. I did try your suggestion and it worked untill I ticke
"share workbook" then after that it just came up with "this command i
not available in a shared workbook". The estimate form which is causin
me so many sleepless nights needs to be cleared of all entered data an
resized back to the original with a single click on a button located o
the spread sheet. The intention is that it would be used very frequentl
by lots of different people in various parts of the building. Data ma
need to be entered by 2 people at the same time so it has to be share
and to expect them to manually cut and paste when the form was finishe
with just would not happen. As I said the Micosoft workaround says.."T
insert the template progammatically, use the following code: Sheets.Ad
Type:=path\filename" Sadly I am not experienced enough to work out th
rest of the code needed to incorperate that into a macro which wil
give me the single button click operation I am looking for.........an
ideas?

Regards

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112

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" wrote in
message ...

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

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
------------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...o&userid=25696
View this thread: http://www.excelforum.com/showthread...hreadid=391122

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

Thanks again

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112

Sorry to be a pain.........but these shared workbook restrictions ar
making life very difficult for a newbie like me! I want to be able t
expand my estimate sheet by 1 row when required and retain any formula
or conditional formats that are present from the original row.
recorded a macro which again is fine in unshared mode but returns
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
shared workbook?

Regards

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112

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" wrote in
message ...


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

Hi Norman

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

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

Regards

Nige

--
Nige
-----------------------------------------------------------------------
Nigel's Profile: http://www.excelforum.com/member.php...fo&userid=2569
View this thread: http://www.excelforum.com/showthread.php?threadid=39112