Want to use sendkeys to replicate the Enter key

  • Here is a link to the post on Mr.Excel.

    Help with creating a board grid with VB
    I have a sheet that calculates how many boxes in one direction and how many boxes in the other direction. for example , I place the data in early columns and…

    Was able to create a graphic grid as requested but,

    On my sheet the cells D3/D5 are not changed directly , so i dont use the enter key. They are a result of a quick formula used to calculate those numbers resulted in D3/D5

    from other cells.

    When i place data in the cells listed below, the resulting numbers are placed in d3 and d5.

    My in put cells are A3/A5 - C3/C5
    I use in D3:


    and in D5:


    So I dont use the enter key for the numbers to change.

    and with the code i have the enter key is required

    so I would like to place some vb code to replicate enter key so the graphic will update.

    thanks in advance.

    • Best Answer

    Try this, to trigger your macro instead of event Worksheet_Change use the event Worksheet_Calculate, like this:

    In the ThisWorkbook module paste:

    Private Sub Workbook_Open()
        With Sheet1     '<- adjust sheet name as needed
            Prev1 = .Range("D3").Value
            Prev2 = .Range("D5").Value
        End With
    End Sub

    In a Standard module paste:

    Public Prev1   As Variant
    Public Prev2   As Variant

    In the Sheet's module paste:

    Private Sub Worksheet_Calculate()
        If Range("D3").Value <> Prev1 Or Range("D5").Value <> Prev2 Then Make_Board
    End Sub

    Save and Restart workbook to activate the public variables before use.

  • Ok, thanks for taking the time, appreciate.

    tried it and now nothing happens. Now graphic grid change

    even when i hit the enter key.

    see how the code is entered below.


    What i do is enter numbers in A3 and A5

    and with the some calculations it automatically changes the

    numbers in D3 and D5.

    The d3/d5 numbers are what build the graphic grid.

    Previously with the workbook change I would have to hit the enter key for the

    grid to change. But when i hit the enter key it deletes the forumla i have in that cell.

    so future calculations dont work.

    maybe I have to change the formula in d3 -


    to vba?


    Here is what i have so far.

    I created a module - by inserting a module and pasted.

    Public Prev1   As Variant
    Public Prev2   As Variant

    in my Sheet1 I have this.

    then , in my workbook I have this.

    Private Sub Workbook_Open()
        With Sheet1     '<- adjust sheet name as needed
            Prev1 = .Range("D3").Value
            Prev2 = .Range("D5").Value
        End With
    End Sub
  • You got Sheet1 module wrong. I said don't use Worksheet_Change, instead use Worksheet_Calculate as indicate at the end of my post. When D3 or D5 change it will call your Make_Board macro.

  • Well, that works beautifully 😁😁😁😁

    Weird thing is happening. When i put a number in A3 and if i hit enter and dont use the mouse to move down to A5 it turns off number-lock.

    so , when i hit the enter key the cursor tabs down to A5 and I am not able to enter a number.

    if , with numberlocks on, i place a number in a3 then use the mouse to place the cursor in a5 I am able to enter a number.

    The enter , after the number entry, turns off Numlock.

    Any thoughts as to why?

    Edited 2 times, last by SkateRockandRoll: disregard. found an extra line of code not needed. the application send keys was not needed ----------------- End With End If End With Application.ScreenUpdating = True Application.SendKeys "{enter}" End Sub ------------------- ().

  • Well, it was a problem with 64bit versions and a couple of years ago I found a workaround ... that's why I went back to a 32bit version.

    Maybe if I look throught my snippets it could come up but since you say you "found an extra line of code not needed" I would be very happy to know which one :/.

    Ps. I think it was to add a Sendkeys "{NUMLOCK}", True before the end of the macro or just after the last offending SendKey.

    Edited once, last by rollis13 ().

  • See the application send keys at the bottom. I placed this in there earlier trying a workaround with the send keys. this will also, dont know why, cancelling out the numlocks when i hit enter after putttin a number in. As soon as i took out this line the numlocks stayed on.

    this was not part of the original code i received from Mr.Excel.

    the page works great and as expected.

    thanks for all of your help.

  • Yes, ok. From the beginning I wondered what it was doing there but since I've never considered what the macro really did ... :whistling:.

    Thanks for the positive feedback:thumbup:, glad having been of some help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!