Is it possible to.....

[ukmxer]

Use A1 cell as data entry (max whole number of 999) e.g type in a number, wait for the enter key and move the contents of A1 to A2 then enter a new number into A1 then as the enter key is pressed move A2 to A3 and A1 to A2 then repeat the process until cell A40 holds the first number you entered.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(1, 0).Insert shift:=xlDown
.Offset(1, 0).Value = .Value
.Offset(40, 0).Delete shift:=xlUp
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[ukmxer]

WOW that was quick. Doing exactly what I wanted, Thank you.

One question. When I enter my second number I have to arrow up to cell 1 is there a line I can add to the code that will make the cursor go up automatically.

[paulked]

Just add

Range("a1").Select


after the EndIf statement.

Regards

Paul Ked

[ukmxer]

Thank you XLD and Paul Ked.

Used to use basic 20 year ago, I have spent all weekend trying to learn how to do it in VB.net. Made no progress whatsoever. You have both cheered me up soooooo much thanks again.

I am just finding all this dotnet stuff so hard to get my head around. I am 45 now but it cant be because I am getting on I will persevere and see if I can move a bit further now you have helped me.

[Bob Phillips]

This is VBA not VB.Net!

[ukmxer]

LOL. OK guys I am stuck already.

Sorry about this BUUUUUUTTTTTT: the number I enter to A1 takes its font properties to A2 and then makes the text too large in that and the other cells.
I have A1 set to 200 for visual purposes but I am setting other cells to 28 for less impact than the data entry cell.

Is it possible to leave the font properties behind and use the destination cell properties.

Thanks

[Bob Phillips]

Just add

Range("a1").Select


after the EndIf statement.

Regards

Paul Ked

Odd, I don't get that, but anyway don't do it that way , use



target.Select


After the End If

[ukmxer]

XLD I did NOT know the difference between VB.net and VBA. That one comment you made has set me off in what I hope is the right direction. It makes more sense for me to learn VBA as I am trying to work with spreadsheet data. Thanks again

[Bob Phillips]

I know why you get that behaviour and I don't.

You must have it set that the cursor moves down after Enter (Tools>Options>Edit, Move checkbox). I have disabled that.

[Bob Phillips]

LOL. OK guys I am stuck already.

Sorry about this BUUUUUUTTTTTT: the number I enter to A1 takes its font properties to A2 and then makes the text too large in that and the other cells.
I have A1 set to 200 for visual purposes but I am setting other cells to 28 for less impact than the data entry cell.

Is it possible to leave the font properties behind and use the destination cell properties.

Thanks



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Offset(1, 0).Resize(38).Copy .Offset(2, 0)
.Copy
.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End If
Target.Select

ws_exit:
Application.EnableEvents = True
End Sub

[ukmxer]

XLD , AGAIN you have made it do exactly what I want. THANK YOU........

I will now start with those few commands that you have used and try to begin learning VBA. At the moment those commands are so alien to me.

So wish me luck I am going in.


:rotlaugh: :rotlaugh: :rotlaugh:

[ukmxer]

If I use INSERT, userform from the menu in the VBA editor I can create a gui which just has the Input box(textbox1) and underneath that I have four smaller boxes displayed from left to right. These would display the contents of A2 to A5.
I do not want the rows and columns displayed as in the standard spreadsheet, the above display would be better for this purpose and is less cluttered.

Can this userform be added to the code above.

[Bob Phillips]

Private Sub TextBox1_AfterUpdate()
TextBox5.Text = TextBox4.Text
TextBox4.Text = TextBox3.Text
TextBox3.Text = TextBox2.Text
TextBox2.Text = TextBox1.Text
End Sub

Private Sub TextBox2_Enter()
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
End Sub

Private Sub TextBox3_Enter()
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
End Sub

Private Sub TextBox4_Enter()
With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
End Sub

[ukmxer]

I am unable to see the userform I still only see the ss . How do I show my userform and the code you offered is that added to the userform viewcode window or the sheet1 viewcode window.

thx

[Bob Phillips]

It is added to the userform, and you have to show the form

Userform1.Show