Activating a cell ready for user to enter data into

[Harribone]

Hi,

I have some code to create a new row with formulas etc. and then the first cell the user needs to enter data into is selected.
However when testing and I type in some data it doesn't go into the selected cell.

Range("C" & NewRow).Select
Range("C" & NewRow).Activate

Neither of the above lines make that cell the active cell for entering data into....

Hopefully what I'm trying to do makes sense and hopefully there is an easy solution!

Thanks in advance
Harribone

[LukeGilfoyle]

Couldn't you just record a macro of what you want it to do?

[Harribone]

Hi Luke,

Unfortunately recording a macro won't give me the answer. The complete code for what I am doing is this:

Sub mcrNewItem()

Application.ScreenUpdating = False

Dim NewRow As Long

With ActiveSheet
    NewRow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
End With

Rows(NewRow & ":" & NewRow).Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B" & NewRow & ":BL" & NewRow).Select
Selection.FillDown
Range("B12:BL13").Select
Selection.Copy
Range("B14:BL" & NewRow).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Range("C" & NewRow).Value = ""
Range("I" & NewRow).Value = ""
Range("J" & NewRow).Value = ""
Range("K" & NewRow).Value = ""
Range("L" & NewRow).Value = ""
Range("BC" & NewRow).Value = ""
Range("BG" & NewRow).Value = ""

With Range("B" & NewRow)
 .Value = Date
 .NumberFormat = "dd/mm/yyyy"
End With

Application.ScreenUpdating = True

Range("C" & NewRow).Select
Range("C" & NewRow).Activate

End Sub

Everything is fine and does its job except for the last bit where I try to select a specific cell so the user doesn't have to scroll down to it etc. Even though the cell is selected on screen Excel doesn't seem to think it is the active cell and the user then has to click on that cell again before Excel realises that is where you want to type in data (in my case an Item Code).

I would go down a userform route to allow the user to enter data for the new row but at this stage it's not really a necessity and this odd 'bug' is driving me nuts so I'm trying to figure out what I've done wrong in case I need this again for something.

[LukeGilfoyle]

So surely

Range("A2").Select

Should work?

You wouldn't need to select a whole row to enter data in one cell?

[Harribone]

So surely

Range("A2").Select

Should work?

You wouldn't need to select a whole row to enter data in one cell?

Just so you know 'NewRow' is the row number in my code so it is selecting just the one cell after the new row is inserted. I have to insert a new row because I have a footer too.

[Harribone]

Hi all,

I've figured out what is going on.

Basically the code is fine. When I type something the screen renders the data in the previous cell for some reason but as soon as you press enter it shows in the correct cell!

Maybe something to do with the 'screenupdating' or Excel generally or just my computer. Either way it turns out everything was fine...

Sorry for wasting everyone's time!

Regards
Harribone

[LukeGilfoyle]

Well done to you, taught me a lesson also

[LukeGilfoyle]

Hi,

I have some code to create a new row with formulas etc. and then the first cell the user needs to enter data into is selected.
However when testing and I type in some data it doesn't go into the selected cell.

Range("C" & NewRow).Select
Range("C" & NewRow).Activate

Neither of the above lines make that cell the active cell for entering data into....

Hopefully what I'm trying to do makes sense and hopefully there is an easy solution!

Thanks in advance
Harribone

Create a macro and enter this code:

Sub Macro1()
'
'
'

'
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
End Sub


This will create a new row and automatically select the first cell in the new row for data to be entered.



Range("A2").Select <--should work