PDA

View Full Version : Sleeper: Excel Read Only Problem



Darren
02-14-2005, 10:53 AM
Hi All

I have a excel worksheet that is completed by my reps namely "data input" and the information is saved to a second worksheet in the same workbook named "Database Building" This build a database in the form of columns.

The problem came in when i added vlookup to the cells in the data input sheet and made it read only so that the formula's appear every time the workbook is opened.

What i would like to do is add a string of code to vba to save the "database Building" worksheet with its new information recieved from the "Data Input" sheet which is Read Only.

What happens at the moment is the information is in the "Database Building" sheet but when i close the work book it delete's the information

Your kind assistance with this problem would be greatly appreciated.



Kind regards


Darren
South Africa

mdmackillop
02-14-2005, 11:01 AM
Hi Darren,
Are you importing from DataBase Building, or exporting from DataInput.
If you can attach a sample copy of your files (Zip them and attach using the Manage Attachments button on the "Go Advanced" page) that would help too.
MD

Darren
02-14-2005, 11:42 AM
Hi Darren,
Are you importing from DataBase Building, or exporting from DataInput.
If you can attach a sample copy of your files (Zip them and attach using the Manage Attachments button on the "Go Advanced" page) that would help too.
MD
Hi MD

Thanks for the quick response. need to explain a little about the form.

All three slips need to be completed before the reps can press the "process" button.
If you would like to test drive the "vlookup" function enter only the telephone number from the "database building" sheet the information will fill in the correct boxes.

to unprotect the sheet press "spacebar once" as password.

My problem is when i press process i dont want the vba to ask to save the file it must save it anyway. the most important part is that the new info stays on that database building sheet when excel closes. I use Query from another computer on the network to retrieve the data at the end of the working day.

Kind regards


Darren

Zack Barresse
02-14-2005, 02:19 PM
Thread moved to Excel Help forum as per OP. :)

mdmackillop
02-15-2005, 01:29 PM
Hi Darren
I've had a look at your code and there doesn't seem to be much of a problem. Basically, I've reordered the runBtn_Click() at the end to Print first then Reset the form and Save it. I think that's the only change you really need to make.
Playing around with these things as I do (still learning after all these years), a couple of points which you can take or leave, as it really doesn't affect your process.
Regarding clearing values, you only need to clear those cell where the text/values are manually entered. I've rejigged this a little using Union to crate a range which can all be cleared/reset by the one operation. Its more efficient that selecting each cell/range in turn, and the action of selecting the cells is unnecessary.
It strikes me that a Userform, might make your form filling a little simpler, especially for getting the phone numbers.
The only other "improvement" I would suggest for appearances, is to us an If(ISNA) function to remove the N/A values from the cells (see C8:F8).
I added a GetTelNos routine for my convenience in testing, triggered by a workbook open commandwhich I left in this copy.
Regards
MD

Darren
02-17-2005, 06:58 AM
Hi Darren
I've had a look at your code and there doesn't seem to be much of a problem. Basically, I've reordered the runBtn_Click() at the end to Print first then Reset the form and Save it. I think that's the only change you really need to make.
Playing around with these things as I do (still learning after all these years), a couple of points which you can take or leave, as it really doesn't affect your process.
Regarding clearing values, you only need to clear those cell where the text/values are manually entered. I've rejigged this a little using Union to crate a range which can all be cleared/reset by the one operation. Its more efficient that selecting each cell/range in turn, and the action of selecting the cells is unnecessary.
It strikes me that a Userform, might make your form filling a little simpler, especially for getting the phone numbers.
The only other "improvement" I would suggest for appearances, is to us an If(ISNA) function to remove the N/A values from the cells (see C8:F8).
I added a GetTelNos routine for my convenience in testing, triggered by a workbook open commandwhich I left in this copy.
Regards
MD
Hi Md

I would like to thank you for your superb support and attendance to my problem. What you have done is amazing and once again the biggest thank you.

Kindest regards


Darren

ps... I will test drive it over the weekend and give you more feedback, tried to get in to VBA yesterday but it would not load.