Active.cell

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Cells(1, 1) = Target.Value

End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,880
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:


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

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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:
Code:
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
 

Forum statistics

Threads
1,081,530
Messages
5,359,353
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top