Trigger macro with keypress event??

[CarlosAntenna]

I have an application where data is being collected from a tester and fed to
a PC through the serial port. The application that sits between the serial
port and Excel is called WinWedge. It feeds test data into Excel and then
advances to the next row. The problem is that it assumes the active cell is
in the correct location to start with. I have written a macro to position
the cursor and I tied it to the workbook open event and the worksheet
activate event, but if someone moves the active cell to a different location
after Excel is up, things get all out of place. Is there any way I can have
Excel listen for the "down arrow" character and activate the macro at that
time? Down arrow is the last character WinWedge sends before it starts the
next record.

Here is my macro:

Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Offset(1, 0).Select

Thanks,

-- Carlos --

[Tom Ogilvy]

Use the selectionchange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
set rng = Cells(rows.count,1).End(xlup)(2)
if Target.Address <> rng.Address then
application.EnableEvents = False
rng.Select
application.EnableEvents = True
End if
End Sub

right click on the Sheet Tab, select view code and paste in code like the
above.

--
Regards,
Tom Ogilvy





"CarlosAntenna" <[email protected]> wrote in message
news:[email protected]...
> I have an application where data is being collected from a tester and fed
to
> a PC through the serial port. The application that sits between the
serial
> port and Excel is called WinWedge. It feeds test data into Excel and
then
> advances to the next row. The problem is that it assumes the active cell
is
> in the correct location to start with. I have written a macro to position
> the cursor and I tied it to the workbook open event and the worksheet
> activate event, but if someone moves the active cell to a different
location
> after Excel is up, things get all out of place. Is there any way I can
have
> Excel listen for the "down arrow" character and activate the macro at that
> time? Down arrow is the last character WinWedge sends before it starts
the
> next record.
>
> Here is my macro:
>
> Application.Goto Reference:="R65536C1"
> Selection.End(xlUp).Offset(1, 0).Select
>
> Thanks,
>
> -- Carlos --
>
>

[CarlosAntenna]

Thanks Tom,

I thought of tying it to the selection change event, but the problem with
that is, there are 15 columns of data being recorded, and it starts a new
row each time the column changes. Can it be restricted to look only for a
row change?

-- Carlos --

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Use the selectionchange event.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> set rng = Cells(rows.count,1).End(xlup)(2)
> if Target.Address <> rng.Address then
> application.EnableEvents = False
> rng.Select
> application.EnableEvents = True
> End if
> End Sub
>
> right click on the Sheet Tab, select view code and paste in code like the
> above.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
>
> "CarlosAntenna" <[email protected]> wrote in message
> news:[email protected]...
> > I have an application where data is being collected from a tester and
fed
> to
> > a PC through the serial port. The application that sits between the
> serial
> > port and Excel is called WinWedge. It feeds test data into Excel and
> then
> > advances to the next row. The problem is that it assumes the active
cell
> is
> > in the correct location to start with. I have written a macro to
position
> > the cursor and I tied it to the workbook open event and the worksheet
> > activate event, but if someone moves the active cell to a different
> location
> > after Excel is up, things get all out of place. Is there any way I can
> have
> > Excel listen for the "down arrow" character and activate the macro at
that
> > time? Down arrow is the last character WinWedge sends before it starts
> the
> > next record.
> >
> > Here is my macro:
> >
> > Application.Goto Reference:="R65536C1"
> > Selection.End(xlUp).Offset(1, 0).Select
> >
> > Thanks,
> >
> > -- Carlos --
> >
> >
>
>

[Jim Thomlinson]

The target is the cell that was changed. Using the intersect function you can
find out if the change occurted in a specific range. Something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
MsgBox "The change was in column B"
End If
End Sub
--
HTH...

Jim Thomlinson


"CarlosAntenna" wrote:

> Thanks Tom,
>
> I thought of tying it to the selection change event, but the problem with
> that is, there are 15 columns of data being recorded, and it starts a new
> row each time the column changes. Can it be restricted to look only for a
> row change?
>
> -- Carlos --
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > Use the selectionchange event.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > set rng = Cells(rows.count,1).End(xlup)(2)
> > if Target.Address <> rng.Address then
> > application.EnableEvents = False
> > rng.Select
> > application.EnableEvents = True
> > End if
> > End Sub
> >
> > right click on the Sheet Tab, select view code and paste in code like the
> > above.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> >
> > "CarlosAntenna" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have an application where data is being collected from a tester and
> fed
> > to
> > > a PC through the serial port. The application that sits between the
> > serial
> > > port and Excel is called WinWedge. It feeds test data into Excel and
> > then
> > > advances to the next row. The problem is that it assumes the active
> cell
> > is
> > > in the correct location to start with. I have written a macro to
> position
> > > the cursor and I tied it to the workbook open event and the worksheet
> > > activate event, but if someone moves the active cell to a different
> > location
> > > after Excel is up, things get all out of place. Is there any way I can
> > have
> > > Excel listen for the "down arrow" character and activate the macro at
> that
> > > time? Down arrow is the last character WinWedge sends before it starts
> > the
> > > next record.
> > >
> > > Here is my macro:
> > >
> > > Application.Goto Reference:="R65536C1"
> > > Selection.End(xlUp).Offset(1, 0).Select
> > >
> > > Thanks,
> > >
> > > -- Carlos --
> > >
> > >
> >
> >
>
>
>

[Tom Ogilvy]

Well, you code sure didn't look like it address entries in 15 columns.
Nonetheless:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rw As Long, col As Long
Dim rng As Range
If Application.CountA(Me.UsedRange) > 0 Then
rw = Cells(Rows.Count, 1).End(xlUp).Row
col = Cells(rw, 16).End(xlToLeft).Column
If col = 15 Then
rw = rw + 1: col = 1
Else
col = col + 1
End If
Debug.Print Target.Address, rw, col
Set rng = Me.Cells(rw, col)
If Target.Address <> rng.Address Then
Application.EnableEvents = False
rng.Select
Application.EnableEvents = True
End If
End If
End Sub

This assume it writes across 15 columns 1 cell at a time, then comes down to
the next row, first column and starts again.

Initial cell is A1

--
Regards,
Tom Ogilvy





"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Use the selectionchange event.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> set rng = Cells(rows.count,1).End(xlup)(2)
> if Target.Address <> rng.Address then
> application.EnableEvents = False
> rng.Select
> application.EnableEvents = True
> End if
> End Sub
>
> right click on the Sheet Tab, select view code and paste in code like the
> above.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
>
> "CarlosAntenna" <[email protected]> wrote in message
> news:[email protected]...
> > I have an application where data is being collected from a tester and
fed
> to
> > a PC through the serial port. The application that sits between the
> serial
> > port and Excel is called WinWedge. It feeds test data into Excel and
> then
> > advances to the next row. The problem is that it assumes the active
cell
> is
> > in the correct location to start with. I have written a macro to
position
> > the cursor and I tied it to the workbook open event and the worksheet
> > activate event, but if someone moves the active cell to a different
> location
> > after Excel is up, things get all out of place. Is there any way I can
> have
> > Excel listen for the "down arrow" character and activate the macro at
that
> > time? Down arrow is the last character WinWedge sends before it starts
> the
> > next record.
> >
> > Here is my macro:
> >
> > Application.Goto Reference:="R65536C1"
> > Selection.End(xlUp).Offset(1, 0).Select
> >
> > Thanks,
> >
> > -- Carlos --
> >
> >
>
>