PDA

View Full Version : [SOLVED] Keeping Formulas after edit



Darren
03-06-2005, 11:17 AM
Dearest Experts

Hope this message find you well.

I have been working on my project for some three months now and its finally coming to a point were it can be used by my reps.

I have one problem that I need your expertise with. I have attached the file as a zip.

Some Background on the worksheet?..3 pick up slips are to generated.

PASSWORD to unprotect document ?press space bar once?

The database you see in ?DATABASE BUILDING? is a test database. I have prepared the real database and will add it when finished testing its about 6 megs.

?Tab? is used for movement in the document.

The sheet is used by the telesales people at a care centre that look after abused women and children it?s a Non Profit organization .

On opening the sheet cell ?C7? is active the reps selects the telephone number they want or they can type in the phone number in, it then uses ?vlookup? to search the sheet ?DATABASE BUILDING? for all the relevant information i.e. company name, building, street and so on down to the fax number.

(I will use the IF(ISNA STAEMENT IN ALL THE CELLS SO THAT #N/A IS NOT DISPLAYED)

Once the sheet is completed the telesales click on ?Process? button at the bottom a few things happen and are as follows

1) the information entered by the reps is placed in the correct cells in the worksheet named ?TAX RECEIPT?

2) 4 sheets of paper are printed = 1 completed pick up slip sheet and 3 Tax Receipts. Each Tax receipt has the relevant information for example click on C7 and choose a telephone number the info will auto fill on the pickup slip and also the Tax receipt. The same applies to ?I7? and ?O7?

3) The Information is stored in the worksheet ?DATABASE BUILDING? which is a hidden sheet and not visible to the telesales. (please hide the sheet if you want to test the ?Process? button.

4) The worksheet ?DEFAULT PICKUP PROTECTED LAYOUT? is reset to opening state and Saved

Ok on to my problem and I could kick myself for not thinking of it before?.

A telesales person enters a phone number and gets no reply from the database! A new customer and the information has to be entered manually. He then proceeds to enter all the relevant information in to the cells lets say for arguments sake he enters 3 new customers and then presses ?Process? ALL MY FORMULAS are gone and the info he or she entered is now the default

Summary

How can I keep my formulas in the worksheet

I have tried to make it ?Read Only? but I get Run time errors and the new information is not stored in DATABASE BUILDING on closing. The Database Building is extremely important.

Keeping the formulas in the cells is also Very important.

Sorry that it?s long winded but I believe explaining the process is important to understanding the problem.

Thank you for taking the time

Kindest Regards

Darren

mdmackillop
03-06-2005, 11:38 AM
Not too problematical I think!
Make a copy of your Pickup sheet, which can be stored as a hidden sheet in your workbook.
Create some code to unhide, copy the sheet, rehide and PasteSpecial/Formulas into your Pickup sheet.
If you need assistance with this, let us know.
MD

Darren
03-06-2005, 11:52 AM
Not too problematical I think!
Make a copy of your Pickup sheet, which can be stored as a hidden sheet in your workbook.
Create some code to unhide, copy the sheet, rehide and PasteSpecial/Formulas into your Pickup sheet.
If you need assistance with this, let us know.
MD
Hi MD

Thanks my friend for the prompt response again. Copy ing the sheet i can do but not sure on the code to hide and unhide. Would the formulas reference the opened pickup worksheet. Its so close to being complete and i thank you for your help the last three months with this project

Kindest Regards



Darren
South Africa
Local time 20H52 Sunday

mdmackillop
03-06-2005, 12:57 PM
Hi Darren,
I've modified your file accordingly. For a demo I've deleted all text and formulae. A Reset button has been added. The hidden sheet is called Pickup Copy and the code is saved in Module2. I've had to delete the other sheets because of size limits on the zip file, but this does not affect this operation.



Sub Macro1()
Application.ScreenUpdating = False
With Sheets("PICKUP COPY")
.Visible = True
.Select
.Cells.Select
End With
Selection.Copy
Sheets("PICKUP COPY").Visible = xlVeryHidden
Sheets("DEFAULT PICKUP PROTECTED LAYOUT").Select
ActiveSheet.Unprotect Password:=" "
Range("A1:S48").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect Password:=" "
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Darren
03-06-2005, 01:50 PM
Hi Darren,
I've modified your file accordingly. For a demo I've deleted all text and formulae. A Reset button has been added. The hidden sheet is called Pickup Copy and the code is saved in Module2. I've had to delete the other sheets because of size limits on the zip file, but this does not affect this operation.



Sub Macro1()
Application.ScreenUpdating = False
With Sheets("PICKUP COPY")
.Visible = True
.Select
.Cells.Select
End With
Selection.Copy
Sheets("PICKUP COPY").Visible = xlVeryHidden
Sheets("DEFAULT PICKUP PROTECTED LAYOUT").Select
ActiveSheet.Unprotect Password:=" "
Range("A1:S48").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect Password:=" "
Range("A1").Select
Application.ScreenUpdating = True
End Sub


Hi MD

I will test and let you know and thanks again my friend and mentor, Hows the weather in bonny scotland beautiful part of the world you live in. At the moment its 26 degrees and nearly 23h00 winter is just around the corner though.

Kindest regards


Darren

mdmackillop
03-06-2005, 01:56 PM
It's a mild 3 degrees here and most of the recent snowfall has gone. All in all. not a bad winter.
Regards
Malcolm

Darren
03-07-2005, 01:25 AM
It's a mild 3 degrees here and most of the recent snowfall has gone. All in all. not a bad winter.
Regards
Malcolm
Morning Malcolm

Hope you are well.

I have tested the demo and it works like a dream. One small problem though.

How I tested!

Test one
I selected the phone number from the drop down and the sheet auto filled the info from the Database and that works fine. I did the same for the others and it printed perfect.

Test 2
I then simulated a new customer entry, I had to remove the Validation Error message from cell "C7" This allowed me to enter any phone number. I then proceeded to fill all the relavant cells and when completed I pressed "Process" The document printed fine and the new info was then saved as the default on the sheet. I then pressed restore sheet and the following runtime error appeared.....

Runtime Error '1004'

"the information cannot be pasted becase the Copy area and the paste area are not the same size and shape. Try one of the following:
Click a single cell and paste
Select a rectangle that is the same size and shape, and then paste"

The area highlighted in yellow in the code is


Sub Macro1()
Application.ScreenUpdating = False
With Sheets("PICKUP COPY")
.Visible = True
.Select
.Cells.Select
End With
Selection.Copy
Sheets("PICKUP COPY").Visible = xlVeryHidden
Sheets("DEFAULT PICKUP PROTECTED LAYOUT").Select
ActiveSheet.Unprotect Password:=" "
Range("A1:S48").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Protect Password:=" "
Range("A1").Select
Application.ScreenUpdating = True
End Sub

I have underlined the code that is highlighted and made it bold for you in the runtime error.

Hope this helps Malcolm

Kindest regards


Darren
South Africa
Local time 10h23

Darren
03-07-2005, 10:02 AM
Hi Malcolm

I have worked on the file you sent me, that you modified and i will use that as the foundation of this project. I have managed to get the "tabbing order" to work exactly as it should. Very pleased. Still have small problem with Restore sheet as listed above but i will wait for your expertise on this small problem. I was thinking could it have anything to do with me using excel 2000 and you using excel 2003 or do you think they would be transparent??

The other thing i need to ask is .... If i apply the =IF(ISNA(VLOOKUP STATEMENT TO not show #N/A in the cells, How will the hidden "Pickup copy" respond to this? Will i have to edit this hidden sheet as well and if so, could you explain how i can acces it. If i look in "Format,Sheet,Unhide it is not listed so i think its well hidden my friend well done!

I have attached the current file that i am very please with!! "Tabbing Order"

Kindest regards


Darren
South Africa
Local Time 18h58, Monday

mdmackillop
03-07-2005, 12:10 PM
Simple things first:
You can unhide all xlVeryHidden sheets with the following macro


Sub Unhide()
For each sh in sheets
sh.visible = true
next sh
end sub


You can then delete the Copy Sheet, make the changes to your original sheet and save a copy with the "Copy" name. Running the code should hide the sheet.

I've made a slight modification to Macro1, Give it a try to see if it helps.



Sub Macro1()
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("PICKUP COPY")
.Visible = True
.Select
Range("A1:S48").Select
End With
Selection.Copy
Sheets("PICKUP COPY").Visible = xlVeryHidden
Sheets("DEFAULT PICKUP PROTECTED LAYOUT").Select
ActiveSheet.Unprotect Password:=" "
Range("A1:S48").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect Password:=" "
Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Darren
03-07-2005, 12:36 PM
Hi Malcolm

Thanks for the info will apply your instuctions as laid out. Should i add a macro to the sheet with the code to make it visibile to edit. (Learning all the time and VBA is new to me MD, but I try my best and I DONT GIVE UP!))),"",( it wont beat me!)

Will advise as to the progress

Kindest regards


Darren
South Africa
local Time 21h35, Monday

mdmackillop
03-07-2005, 12:42 PM
Put it in a standard module, not the sheet module, as it applies to the whole workbook and will unhide all sheets. You can create a keyboord shortcut or create a button and assign the macro to it.
I use the unhide regularly and so keep it personal.xls, so it can be used on any workbook. If you do this, you should create another macro to hide individual sheets


Sub Hide()
activesheet.visible = xlveryhidden
end sub

Darren
03-07-2005, 03:07 PM
Hi Malcolm

thanks for the hide and unhide it works like a charm..

I tried the new code and i still have the same problem. "Run Time Error 1004" May I suggest something ?

To simulate the runtime error on your PC type any phone number in and then "tab" database does not recognise the number still #N/A in cells instead of typing in all the cells i hold down the alphabet keys to fill the cells i.e. aaaaaaaaaaaaaaa "tab" bbbbbbbbbbbb "tab" CCCCCCCCCCCCCCC and so on till all cells are filled for the telephone number i use number Keys and then I click the "Process" button the sheet prints okay and stores the info in the database no problem

But

When I "Restore Sheet" it shows the Run Time Error 1004

This is the only problem i have and then the worksheet is complete. I am sorry for taking up so much of your time on this problem

Kindest regards


Darren
South Africa
Local Time 00h06 Tuesday

mdmackillop
03-08-2005, 01:31 AM
Thanks go to Jake for spotting the problem.
Replace Macro1 with the following:


Sub Macro1()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect Password:=" "
With Sheets("PICKUP COPY")
.Visible = True
.Select
.Cells.Select
End With
Selection.Copy
Sheets("DEFAULT PICKUP PROTECTED LAYOUT").Select
Range("A1").PasteSpecial Paste:=xlPasteFormulas
Sheets("PICKUP COPY").Visible = xlVeryHidden
Application.CutCopyMode = False
ActiveSheet.Protect Password:=" "
Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Darren
03-08-2005, 09:37 AM
Hi Malcolm & Jake:friends:

Well what can I say !!!! You Guys are just simply the BEST of the Best, Problem Solved and Pickup Sheet now has wings and is flying through the the process. Dont know what i would have done without you Guys, Thank you both:thumb

Thanks a Million

I have attached the final cut maybe someone else could use it someday somewhere
I have removed the name and tested as is and "Process" & Restore Sheet Work like a bomb.

Kindest Regards


Darren
South Africa
Local Time 18H20:bow: :bow: :bow:
:bow: :bow: :cloud9:

mdmackillop
03-08-2005, 11:46 AM
Pleased you've got things working, so now you have time to think further about your workbook design. Here's a few thoughts
Your data source, addresses etc. should contain only unique items, so while your Database Building contains all of your records, I think you should have a separate Source sheet of unique records. It should be possible to code your Process to 1. Record transactions and 2. Check if a company exists, and if not, add it to the source sheet then 3. Sort it in a useful order.
The use of phone numbers as the index seems unusual, why not have a "friendly" company name, eg Smith & Co (Glasgow), Smith & Co (London) etc, as an extra field, which maintains your "proper" address field, but is less prone to input error.
While your formulae work, I would suggest that you could consider filling the fields using "offset" references within your code. It is not necessary in this application, but is useful to know. Not all form layouts can be predermined like this.
Looking forward to your first KB entry!
Malcolm

Darren
03-08-2005, 02:08 PM
Hi Malcolm & Jake

After reading the above i feel compelled to say the following........not sure how to word this ..............the VBAX forum has been a sense of ispiration for me, to find others who would give up there time and effort to help others is a blessing,,,,,, who are less knowlegable than me and the thosands of people out there ............ the Haven gives shelter to those people that would complicate your's or any others life, Malcolm and Jake your help is a God Send for the Centre and May your names be written in stone for the help and support the both of you provide and Thank you once gain............


Darren
SouthAfrica
Local Time 23h07