Enter Command

[Lil Pun]

I posted some code a little more than a month ago at this thread:

http://www.excelforum.com/showthread.php?t=536944

OK, this Excel program is working with 2-3 other programs as well. One program, an active X program, puts a 1 into cell A50. After it does this, I need "enter" some how pressed or activated when that value is placed into that cell. Is there anyway to acheive this?

What I mean is as soon as the 1 is placed into that cell the program then acts as if somebody has pressed enter without anybody actually having to press it.

[Mat P:son]

....Just a few wild guesses :-)

- Use the Worksheet_Change() even to listen for cell value changes
- Check wether or not the target param of the event is (includes?) cell A50
- If it's A50, invoke SendKeys and pass in {ENTER}

Don't know if this will work, but at least it might be a first stab...?

Cheers,
/MP

"Lil Pun" wrote:

>
> I posted some code a little more than a month ago at this thread:
>
> http://www.excelforum.com/showthread.php?t=536944
>
> OK, this Excel program is working with 2-3 other programs as well. One
> program, an active X program, puts a 1 into cell A50. After it does
> this, I need "enter" some how pressed or activated when that value is
> placed into that cell. Is there anyway to acheive this?
>
> What I mean is as soon as the 1 is placed into that cell the program
> then acts as if somebody has pressed enter without anybody actually
> having to press it.
>
>
> --
> Lil Pun
> ------------------------------------------------------------------------
> Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
> View this thread: http://www.excelforum.com/showthread...hreadid=549138
>
>

[Lil Pun]

How do I do the Send Keys though? I mean if I implement it into my code, what will the code look like?

Like I said, I only need Enter invoked if a 1 is placed into cell A50.

[Lil Pun]

Also, this is a section of code listed in the ThisWorkbook part of the Excel VBA code:

Private Sub Workbook_Open()
Range("A2").Select
avoidloop = True
Application.SendKeys "{F2}"
End Sub

I believe this code sends the F2 function to cell A2.

How would I do an "Enter" for th cell A50 but at the same time only doing it if a 1 were entered into that cell.

[Mat P:son]

Try the following (add it to the code module of sheet 1, if that's the sheet
you want to work with):

=========================================

Option Explicit

Private Const lTgtCol As Long = 1
Private Const lTgtRow As Long = 50

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = lTgtRow And Target.Column = lTgtCol Then
If Sheet1.Cells(lTgtRow, lTgtCol).Value = 1 Then
Sheet1.Cells(lTgtRow, lTgtCol).Select
Application.SendKeys "{ENTER}"
End If
End If
End Sub

=========================================

But why do you try to send enter? Is that to "confirm the edit", i.e., when
your ActiveX control is putting the character "1" into cell A50 do you need
to make your VBA code send an Enter keystroke to commit ithe change? If so,
the above attempt using Worksheet_Change() will obviously not work, because
the Change event will not fire before the change of the cell value has been
committed.

Cheers,
/MP

"Lil Pun" wrote:

>
> Also, this is a section of code listed in the ThisWorkbook part of the
> Excel VBA code:
>
> > Private Sub Workbook_Open()
> > Range("A2").Select
> > avoidloop = True
> > Application.SendKeys "{F2}"
> > End Sub
> >
>
> I believe this code sends the F2 function to cell A2.
>
> How would I do an "Enter" for th cell A50 but at the same time only
> doing it if a 1 were entered into that cell.
>
>
> --
> Lil Pun
> ------------------------------------------------------------------------
> Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
> View this thread: http://www.excelforum.com/showthread...hreadid=549138
>
>

[Lil Pun]

I need 'Enter' to be sent because it needs to be done automatically and not manually by keystroke. The program will place a 1 into the specified cell (A50) and then the program needs to act as if 'Enter' has been pushed so that the cursor then moves to cell C2, which is the way the program is setup right now but it doesn't have a function to 'Enter' when a 1 is present in the cell.

So will your suggestion not work? Thanks!

[Mat P:son]

"Lil Pun" wrote:

> I need 'Enter' to be sent because it needs to be done automatically and
> not manually by keystroke. The program will place a 1 into the
> specified cell (A50) and then the program needs to act as if 'Enter'
> has been pushed so that the cursor then moves to cell C2, which is the
> way the program is setup right now but it doesn't have a function to
> 'Enter' when a 1 is present in the cell.
>
> So will your suggestion not work? Thanks!

As I said: no, it will not. But by all means, try the suggestion before you
discard it -- stranger things have happened... :o)

Frankly, I see no viable way to achieve what you're asking for -- AFAIK,
Excel does not expose the events required to listen for e.g. cell entry, and
it doesn't send change events for each character entered in a cell (not for
worksheet cells, that is). Maybe you could listen for Selection Change
events, and see if cell A50 becomes selected, but then what? Hmmm, the future
doesn't look bright...

If you're owning the code base for the ActiveX control putting "1" in cell
A50, I suggest you go in and patch the control instead, and make sure it does
what it's intended to do. That would probably be the easiest way of solving
the problem, and you would avoid trying to jump through hoops in Excel (which
seems to be neither easy nor possible?)

Cheers,
/MP

> --
> Lil Pun
> ------------------------------------------------------------------------
> Lil Pun's Profile: http://www.excelforum.com/member.php...o&userid=33840
> View this thread: http://www.excelforum.com/showthread...hreadid=549138
>
>