The best way to do what you want to do is as follows:
- Enter all of the formulas you want on the sheet
- Highlight the entire sheet (click the little square with the triangle at far upper left of the sheet
- Right-click anywhere on the sheet, choose "Format Cells" and click on the Protection tab
- Make sure that "Locked" is checked, indicating that *all* cells are locked when the sheet is protected; click OK
- Now select all the cells that you will want to enter data in (hint: you can select multiple cells by holding down the CTRL key while clicking on them)
- Right-click again, Format Cells, and this time *clear* the "Locked" checkbox; click OK. Your selected cells will now remain unlocked
- Now click on the "Review" tab on the main menu (ribbon)
- Click on "Protect Sheet" and enter a password if you want; *uncheck* the box for "Select Locked Cells" and click OK. The sheet is now protected
- Now click in your first unlocked cell, make an entry and hit enter; the cursor will jump to the next unlocked cell.
You can use this technique to allow entry into just a few cells on a sheet. In order not to drive yourself crazy later, make sure that it's clear by formatting that these are the cells that are for user entry, or you'll drive folks crazy trying to enter data!
This technique is especially useful for ensuring that critical formulae are not overwritten when someone is entering data in nearby cells.
I was afraid I was going to have to do it that way. I'm well aware of it but I wasn't sure if it could be done another way. Almost the same way Access controls it's tab orders.
As soon as I am done creating the SS I will end up locking it just so people don't enter data into the wrong fields or remove any formulas.
Does anyone know how can I paste the copied information into the next available row of a table? I have a formatted table so at the end of each column a tab will start a new row and I want to paste the value only into the new row. The information is already formatted to fit perfectly so how can I paste this info? (see bold line for error)
Sub Move_2_NoList()
'
' Move_2_NoList Macro
'
'
Range("AA15:AJ15").Select
Selection.Copy
ActiveWindow.ScrollColumn = 1
Sheets("No List").Select
Range("L37").Select 'this is the line where it pastes when I made the macro but I want to paste into a new row' Selection.ListObject.ListRows.Add AlwaysInsert:=False
Range("B38").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Home").Select
Range("C2:J2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C6:D6").Select
Selection.ClearContents
Range("C8:D8").Select
Selection.ClearContents
End Sub
There are probably a dozen or more different ways of accomplishing what you want, some more elegant than others. Since you are using VBA, you have a lot of choices. I'll give you a pretty inelegant solution that I know works.
- Create a cell somewhere that tracks the number of entries in the table using one of the COUNT functions
- Prior to pasting, read the value from that cell and calculate which row you need to paste in (deal with header rows, etc.)
- Construct a string with the proper range information. For instance, if a formula in cell A1 counts 38 non-blank values including a header row, you want to insert at row 39 so add one. I'll also assume you want to insert at column L, so your code might look like this (I've added line numbers so I can reference them, you don't need them of course):
In the above code, the variable NextRow should be defined as an Integer type, and the value read from cell A1 is the result of the calculation of the number of non-blank cells plus any offsets you need. The variable RangeString should be dimensioned as a String type, and you use this to construct the cell range to select.
This method is pretty down & dirty. There are probably other more elegant methods, but this gets the job done.
