Completely Stumped

Some kind souls help is requested to make my worksheet more user
friendly:

1) When data is filled in across cell range B14 � R14 and  I then press
�enter�....    a new row is automatically inserted either above or
below. 

2) The new row would retain the formulas in the cells just copied, but
none of the calculations or text. I am using using the following code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)

Dim lastrow As Long
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents

End Sub

All is well until I "Protect" the worksheet. I do this to limit tab
order and because other cells above cell range B14 - R14 must not be
erased or changed by mistake. If anyone is interested in looking at the
relevant portion of this worksheet please let me know.

"Thank you" ... in advance for any suggestions you might have.


-- 
ZimBoy
------------------------------------------------------------------------
ZimBoy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24273
View this thread: http://www.excelforum.com/showthread.php?threadid=382737

0
6/28/2005 10:02:32 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
639 Views

Similar Articles

[PageSpeed] 32

Hi Zimboy

In your macro, just before you do the row insert, set worksheet protection 
to false, and then, after doing your thing, set it to true again.

    ActiveSheet.Unprotect
and then do your thing
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells


"ZimBoy" wrote:

> 
> Some kind souls help is requested to make my worksheet more user
> friendly:
> 
> 1) When data is filled in across cell range B14 – R14 and  I then press
> “enter”....    a new row is automatically inserted either above or
> below. 
> 
> 2) The new row would retain the formulas in the cells just copied, but
> none of the calculations or text. I am using using the following code:
> 
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> As Boolean)
> 
> Dim lastrow As Long
> lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
> Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
> Rows(lastrow + 1).SpecialCells(xlConstants).ClearContents
> 
> End Sub
> 
> All is well until I "Protect" the worksheet. I do this to limit tab
> order and because other cells above cell range B14 - R14 must not be
> erased or changed by mistake. If anyone is interested in looking at the
> relevant portion of this worksheet please let me know.
> 
> "Thank you" ... in advance for any suggestions you might have.
> 
> 
> -- 
> ZimBoy
> ------------------------------------------------------------------------
> ZimBoy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24273
> View this thread: http://www.excelforum.com/showthread.php?threadid=382737
> 
> 
0
Kassie (262)
6/28/2005 10:39:02 AM
Reply:

Similar Artilces:

CFileDialog -- auto completion issue
I'm using an extended CFileDialog (provides Win2k dialog support) and all is working fine, but I had one minor tweak question. If I have the files: c:\temp\test.xml c:\temp\test.txt And I specify that the dialog is only looking for .xml files, when a user types into the file name field "test", the auto-complete dropdown lists suggests both possibilities. I'd like for it only to suggest the .xml file since that's what the dialog's filter is set to. This works in Office 2000, but with any other app that uses the dialog, you'll get the multiple "incorrect&quo...

Auto Complete
I have a user that has the auto complete flag set but when he restarts his machine he has to start all over. Nothing is retained from the day before. I've looked everywhere I know to look. Any ideas? Steve You have? Search this group then. This problem has been posted countless times. It usually means a corrupt Outlook profile. -- Russ Valentine [MVP-Outlook] "GSP@DCS" <info@dcs.com> wrote in message news:2d836$45184a6c$d1a8fa82$5760@EDELTACOM.COM... >I have a user that has the auto complete flag set but when he restarts his >machine he has to start all ove...

Completely Stumped
Some kind souls help is requested to make my worksheet more user friendly: 1) When data is filled in across cell range B14 � R14 and I then press �enter�.... a new row is automatically inserted either above or below. 2) The new row would retain the formulas in the cells just copied, but none of the calculations or text. I am using using the following code: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim lastrow As Long lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault Rows...

Auto Complete Category
Hi All. I'm having an issue with Money Plus. When I download my statement, the information that come is: MEMO (i believe this should be the payee and it is going to the wrong place) date and amount. As the information is saved as MEMO and not PAyee, Money doesn't learn to auto complete thye categories according to previous transactions. Does anyone know how to solve this issue? In microsoft.public.money, Mozinha wrote: >Hi All. I'm having an issue with Money Plus. When I download my >statement, the information that come is: MEMO (i believe this should >be the payee and i...

Uninstall Mendeley completely
Dear Folks, I have accidentally installed Mendeley, which turned out to be a big mistake. I uninstalled it several times after closing all other programs, but now it still appears as "Add-Ins" in office 2007 (including word, excel, etc). It also interferes with Endnote x2. This is a huge problem for me now and please help. -CW -- restlessHK ------------------------------------------------------------------------ restlessHK's Profile: http://forums.techarena.in/members/162019.htm View this thread: http://forums.techarena.in/ms-office-support/1279777.htm ht...

RE: Understanding Project's Percent Complete vs. Percent Work Complete
Brian Kennemer wrote the subject-line article several years ago and was very informative/enlightening on how % Complete is computed, etc. However, in this article he showed a Microsoft Project view (Figure A) that had both "Work" and "Actual Work" rows in the detail lines for each resources on a specific task. Where can I find this view. I do need to enter actual hours worked per resource per day on their respective task thanks, David Hamil MaxVision 256-652-4322 David@MaxVision.com Perhaps what you seek is on the resource view or task usage view. Bot...

Auto Complete list has been deleted somehow anyone know how to fix??
I have hundreds of emails in auto complete and today when I tried to use it they were all gone. This happened to me about 3 years ago and I never was able to find out why. Can anyone help me please.. Submitted using http://www.outlookforums.com mike long wrote: > I have hundreds of emails in auto complete and today when I tried to ... <gatewayed post was truncated to illustrate lack of proper line wrap> The autocomplete cache is in the nickname file (.nk2). Do you still have a ..nk2 file? If it is gone or corrupted, restore it from your backups. http://support.mi...

Excel won't completely open
When I click on an Excel document it will start to open but stops at a grey screen. Only a few toolbar commands are selectable, all others are grey and not accesible. I need to go to file to open the correct document. This is frustating as I have several document in Excel and this is also confusing when I email it to someone else. Please help if you think you can. Hi try the following: goto 'Tools-Options-General " and uncheck "Ignore other Applications" Exit Excel and try again If this doesn't work try to re-register Excel 1. Close Excel first and 2. On the Windows...

2007 Structured Reference auto-completion not working completely?
In Excel 2007, when I: 1) Select a cell 2) Type "=" in the formula bar 3) Click a cell in a Table I thought Excel would add a Structured Reference to the formula. Instead, it just inserts an old-style, "A1" reference. The "Formula AutoComplete" and "Use table names in formulas" options are both turned on. When I click the cell in step 3, the TableTools tab appears so Excel agrees that the cell is in a Table. Isn't Excel supposed to insert a Structured Reference when you click a cell in a Table? Is there another setup/configuration switc...

Forms completion
Here is a question for you, we have a pretty simple database to gather comments and suggestions from users for improvement on things around our office, It is a form entry, a suggestion that was given is when users want to enter multiple suggestions they are having to re-enter the header section of the form (Basics of name and contact information) repeatedly, is there a code or work around where that will replicate for the user if they click the add new record after they enter the first entry? Are you storing this information in every row of the table? The way it should work is, when the...

inbox repair tool fails to complete
hi, I've been having problems with my newish laptop XP & Office 2003 ST edition. Tried to run inbox repair tool on the .pst. I get the message that says internal errors were found, and I selected the box for a backup file to be created, but the process hangs on the repair. I tried twice, and can verify the .bak file is there and the same size as the .pst, but the repair won't complete. Using the Task Manager indicates the process is not responding. Happened twice in a row. Any thoughts? Thanks, Carmen Greta Grip <misscarmengetit@hotnospammail.com> wrote: >...

Windows update is not completely successful Error Code: 0x64C
Cannot find a solution anywhere. No such error code in any of the help screens. It is now showing Error Code: 0x8007064C. Still cannot find this discussed anywhere. "John with RadioWorks" wrote: > Cannot find a solution anywhere. No such error code in any of the help screens. "John with RadioWorks" <JohnwithRadioWorks@discussions.microsoft.com> wrote in message news:016D3C6E-B2BE-49CC-8678-580B6E169903@microsoft.com... > It is now showing Error Code: 0x8007064C. Still cannot find this discussed > anywhere. > > "John with RadioWo...

100% complete without adjusting actual hours?
I’m attempting to “close out” project plans. Is there a way to mark all the tasks 100% complete without the Actual hours adjusting? Basically, I want to close the tasks based on the actual hours currently attached to them. I’m trying to avoid having to go to each individual task, recalculating, and saving… Please advise. Thanks MK "MK" wrote: > I’m attempting to “close out” project plans. Is there a way to mark all the > tasks 100% complete without the Actual hours adjusting? Try changing Remaining Work to 0. James Fraser ...

Auto Complete not completing
Hi. I have an Excel document at work containing some drop down menus. I like the Auto Complete feature that Excel uses and it has been working beautifully except for this one column in my worksheet. For some reason it has stopped auto completing and I am forced to either use the drop down menu or type the whole thing in. :S I have no spaces between rows, and I have made sure the Auto Complete feature is switched on in the Options menu. It is working on my other columns (regardless of whether they contain drop down menus or not). Any ideas? Thanks so much. Maybe you haven't type...

Way to look at % complete of predecessors 03-10-10
Hello I would like to find out what is the most efficient way to check the status of the predecessors of any tasks in the plan I have a plan with a large number of tasks and for instance task 540, has 10 predecessors each at different stages of completion scattered throughout the plan. Currently if I want to know how each predecessor is progressing I need to locate the predecessor manually, and I would be grateful for some advice on how to do this more efficiently Many thanks J Use a flag field (Flag1), flag the tasks you are interested in (540 and all of its predecess...

Money 2005 download not completing.
I just purchased Money2005 and downloaded from Microsoft. When trying to start the program the budget and reports sections will not finish downloading from the web. Anyone else have this problem? I am having the exact same problem. Think it may have something to do with Passport authentication. Notice I'm always in 'Offline' mode (File_Work Offline). "citeaux63" wrote: > I just purchased Money2005 and downloaded from Microsoft. When trying to > start the program the budget and reports sections will not finish downloading > from the web. Anyone else hav...

Report last activity completed, next activity(ies) not completed
Hi everyone! Thank you in advance for at least taking a look at this. CRM has been a great product to use so far but I've run into some difficulty building a report to give the account, last activity completed, and next (highest priority and date created) activity to be worked on. I have all the SQL structure and process figured out to sort the values but I can't for the life of me figure out how activities are related back to an account (or contact, lead, email recpt...ect.) so if anyone could point me in the direction of how to link an activity to an account in the SQL Repor...

OK, I'm stumped or stupid
Maybe it's because I've been working for 10 straight hours, but I just cannot figure out the best way to do this: I've got two columns. One has names and the second has titles. Example: A B Joe Blow Vice President John Smith President Big Daddy Office Worker Wayland Smithers Vice President Keep in mind, the real sheet has 42,000 records and hundreds of titles. I want to delete all rows (or at least clear all of them) if the title IS NOT Vice president. I want my spreadsheet to look like this: A B Jo...

viewing complete headers
Anyone know how to get Outlook to display a message's complete header Assuming that you are using Outlook 2000 or later, try right clicking on a message in your inbox and select options. The internet header is at the bottom of the dialog. "Debra" <anonymous@discussions.microsoft.com> wrote in message news:14d3e01c3f994$8f883410$a501280a@phx.gbl... > Anyone know how to get Outlook to display a message's > complete header ...

Outlook 2003 Auto Complete
I have recently upgraded my desktop computer and didn't archive my .NK2 file. Is there any way to manually rebuild the NK2? Is the old profile available? IF so, you can copy it over. IF not, the cache will rebuild each time you send to someone new.... "jdc" <jdc@discussions.microsoft.com> wrote in message news:9410115A-6DE0-4730-95CA-74C0DBE00029@microsoft.com... >I have recently upgraded my desktop computer and didn't archive my .NK2 >file. > > Is there any way to manually rebuild the NK2? jdc <jdc@discussions.microsoft.com> wrote: >...

Auto Complete
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How can I reset the data base of typed words and numbers in Excel? I would like to use the preference for Auto Complete, but I need to start over Sadly, there is no good way to reset the list globally, you have to dig the bad ones out, one by one. See the help topic "Change the name or contents of an AutoCorrect entry" Hope this helps On 24/03/10 2:47 AM, in article 59bb5c66.-1@webcrossing.JaKIaxP2ac0, "bandlc4@officeformac.com" <bandlc4@officeformac.com> wrote: > Ver...

Not enough system resources to display completely
I am using Office XP on win2000 prof. When i open an excel work book, it prompts "Not enough system resources to display completely". My system has 1GB ram and 2000MB virtual memory. My work book has some calculations & cell references from other workbooks also. I have gone through the article given by microsoft support "http://support.microsoft.com/?kbid=313275" with no luck. Please let me know, to fix this issue. Shanthi Hi Shanthi, Please note it is just my GUESS. It may occur because of the virus. But first of all, please display "Task manager",...

Source for automatic name completion
Dear Folks, Using Outlook 2003 and Windows XP. On my previous installation, Outlook 2000,I was able to specify the first source of data for automatic name completion in email. I can't seem to find that option in Outlook 2003, and the program seems to look first in my secondary data source. Any ideas? thanks Outlook 2000 never had autocompletion. It only had autoresolution, for which you could determine the order in which your folders were checked. Outlook 2002 and 2003 have autocompletion, which will only reference the cache created from messages you have sent, never your Contac...

% Complete Versus Physical % Complete
Hello, What is general perspective of using the Physical % Complete versus the % Complete field? My company uses the % Complete field for our earned value reporting. If the task is 50% complete, we mark it as 50%, 75% complete...we type in 75%. Another company, our customer, has instructed us to use the Physical % Complete field. They want us to use the "Update Project" feature and update all the tasks through the status date (currently our period end). Then they are going to use whatever MSProject calculates the % Complete to be for their reporting metrics....

Completely blank emails hang exchange POP3 download
Every single morning around 2 AM a blank email is sent into my Exchange 2003 account. The email has no subject, no body, no header. When I try to download emails from my account into Outlook or Outlook Express using POP3, an error 0x800CCC0F occurs and the download of email hangs. At that point I can no longer download or use my email account at all. To clear the message I have to log into the server using OWA, locate the blank email, and delete it. Upon doing so the POP3 download of mail resumes as normal. When I look through the server logs I can get the IP address of the computer se...