Excel 2007 help - DPChallenge Forums

Canon RF 24-70mm f/2.8 L IS

06/02/2010 06:57:27 PM · #1

Does anyone know how I can ignore a cell when hitting the enter key???

I enter a data into cell D2 and when I hit enter I want it to shift to C4 automatically. Enter again would shift it to cell C6.

Anyone know how I can do this or can someone make a macro for me that will allow it to do this?

kirbic

Canon EOS R5

06/02/2010 08:35:35 PM · #2

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.

Message edited by author 2010-06-02 20:38:24.

Nikon AF Nikkor 50mm f/1.8D

06/03/2010 12:04:03 AM · #3

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.

Thanks Fritz

frisca

Nikon D70

06/04/2010 03:41:28 PM · #4

DD -- this is OT, but I find your signature *really* tacky and offensive.

06/07/2010 11:35:56 PM · #5

Originally posted by frisca:

DD -- this is OT, but I find your signature *really* tacky and offensive.

I'm Sorry

Message edited by author 2010-06-07 23:45:58.

06/07/2010 11:39:52 PM · #6

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

Message edited by author 2010-06-07 23:53:16.

Canon RF 24-70mm f/2.8 L IS

06/08/2010 05:58:19 PM · #7

bump

kirbic

Canon EOS R5

06/08/2010 06:17:25 PM · #8

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):

10 NextRow = cells(1,1).value + 1
20 RangeString = "L" & NextRow
30 Range(RangeString).Select
40 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

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.

06/09/2010 09:48:59 AM · #9

Thanks again buddy