Active.cell

[MartinL]

Is there a way making A1 always equal the contents of the last cells entry.

I found this whilst looking....

http://www.mrexcel.com/forum/showthread.php?t=22149

but it puts the active cells contents in A1.

In my case the active cell is empty and when I press enter the active cell has changed to the next cell down....Hmmmm...

So basically what Im trying to do is:

Enter xyz in cell d1 and when I press enter xyz appears in D1 and A1
When I then enter ABC in D2 and press enter again ABC appears in D2 and A1. whilst D1 still contains xyz.

Does that make sense???

Why am I doing this? because I need to validate the contents of a complex cell entry and the standard Validation doesnt work for what I need to do.

see: http://www.mrexcel.com/forum/showthread.php?t=416482


Martin

 

[crimson_b1ade]

Private Sub Worksheet_Change(ByVal Target As Range)

Cells(1, 1) = Target.Value

End Sub

 

[Scott Huish]

That code will continually call itself:

I have also made an adjustment so that it will always pull the top left cells value of the changed range:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1") = Target(1, 1)
Application.EnableEvents = True
End Sub

 

[MartinL]

Hi Guys

I fear my suggested requirements did not work, although your answers were excellent in every way.

So I am having to give you fuller information of my needs:

Anyway here are two days and two items
the idea is that if someone enters the value as in cell G18 validation takes place.
The same validation takes place for anything that is entered in any packplan cell. In the case of this sample that is; cells B9:B14 & B17:B22 & G9:G14 & G17:G22.

This will continue for 7 days and many items.

Only if something is entered in these cells should the validation take place.

TestRange = B9:B14, B17:B22, G9:G14, G17:G22

OK here is the test sheet

Sheet1

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td colspan="4" style="font-weight: bold; font-size: 9pt; text-align: center;">MONDAY</td><td style="background-color: rgb(0, 255, 0); font-weight: bold; font-size: 9pt;">
</td><td colspan="4" style="font-weight: bold; font-size: 9pt; text-align: center;">TUESDAY</td><td style="background-color: rgb(255, 0, 255); font-weight: bold; font-size: 9pt;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="background-color: rgb(255, 204, 153); font-weight: bold; font-size: 9pt; text-align: center;">Stock</td><td style="background-color: rgb(255, 255, 153); font-weight: bold; font-size: 9pt; text-align: center;">Pack Plan</td><td style="background-color: rgb(204, 255, 204); font-weight: bold; font-size: 9pt; text-align: center;">Arrivals</td><td colspan="2" style="background-color: rgb(204, 255, 255); font-weight: bold; font-size: 9pt; text-align: center;">Orders</td><td style="background-color: rgb(255, 204, 153); font-weight: bold; font-size: 9pt; text-align: center;">Stock</td><td style="background-color: rgb(255, 255, 153); font-weight: bold; font-size: 9pt; text-align: center;">Pack Plan</td><td style="background-color: rgb(204, 255, 204); font-weight: bold; font-size: 9pt; text-align: center;">Arrivals</td><td colspan="2" style="background-color: rgb(204, 255, 255); font-weight: bold; font-size: 9pt; text-align: center;">Orders</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(255, 204, 153); font-size: 8pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt; text-align: center;">07-Sep</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 8pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt; text-align: center;">08-Sep</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="background-color: rgb(255, 204, 153); font-size: 8pt;">
</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 8pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td rowspan="5" style="background-color: rgb(192, 192, 192); color: rgb(0, 0, 255); font-size: 7pt; text-align: center;">ITEM 1</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt; text-align: center;">1PUV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold; text-align: right;">220</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(192, 192, 192); color: rgb(255, 255, 255); font-size: 7pt; text-align: center;">-221</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt; text-align: center;">21WLS</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-size: 7pt;">18WLS (BA)</td><td style="background-color: rgb(204, 255, 255); font-weight: bold; text-align: right;">350</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt; text-align: center;">-221</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt; text-align: center;">4GUV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt; text-align: center;">3PLV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">221</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt; text-align: center;">1GLV</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">0</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="background-color: rgb(0, 255, 0); font-size: 7pt; text-align: center;">1</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td>
</td><td style="color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="color: rgb(255, 0, 0); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td>
</td><td style="color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="font-size: 7pt;">
</td><td style="color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="color: rgb(255, 0, 0); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td rowspan="6" style="background-color: rgb(192, 192, 192); color: rgb(0, 0, 255); font-size: 7pt; text-align: center;">ITEM2</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 8pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-weight: bold; font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-size: 7pt;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt; text-align: center;">1PUV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold; text-align: right;">220</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(192, 192, 192); color: rgb(255, 255, 255); font-size: 7pt; text-align: center;">-221</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt; text-align: center;">18WLS(BA)</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-size: 7pt;">18WLS (BA)</td><td style="background-color: rgb(204, 255, 255); font-weight: bold; text-align: right;">350</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(204, 255, 255);">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt; text-align: center;">-221</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt; text-align: center;">4GUV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt;">
</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td><td style="background-color: rgb(255, 255, 153); font-size: 7pt; text-align: center;">3PLV</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">221</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="background-color: rgb(255, 204, 153); font-size: 7pt; text-align: center;">1GLV</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">0</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td><td style="background-color: rgb(0, 255, 0); font-size: 7pt; text-align: center;">1</td><td style="background-color: rgb(255, 255, 153); color: rgb(255, 0, 0); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 204); font-size: 7pt;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(0, 0, 255); font-weight: bold;">
</td><td style="background-color: rgb(204, 255, 255); color: rgb(255, 0, 0); font-weight: bold;">
</td></tr></tbody></table>
I said that if we could put the result of a cell entry into cell A1 I had some code that would check for a valid entry.

Unfortunately, the suggeted code puts evy piece of data in cell A1 and not just if the Active cell WAS in the "TestRange"

I am probably doing this ArseAboutFace so I would welcome a completely different approach as cell Validation does not work.

So far the validation for PackPlan is:

Private Sub Worksheet_Change(ByVal target As Range)
Dim rx As Object
 
If Not Intersect(target, Range("A:A")) Is Nothing And target <> "" Then
    Set rx = CreateObject("VBScript.RegExp")
    With rx
        .Pattern = "^([1-9]\d{0,2}|1000)[A-Za-z]+$"
        If Not .test(target.Value) Then
            MsgBox "Invalid Input"
            Application.EnableEvents = False
            target.ClearContents
            target.Select
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

Martin