Want to use sendkeys to replicate the Enter key

SkateRockandRoll · Jan 27th 2023

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

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…

www.mrexcel.com

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:

=ROUNDDOWN(SUM(C3/A3),0)

and in D5:

=ROUNDDOWN(SUM(C5/A5),0)

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.

Code

Sub Make_Board()
  Dim Rws As Long, Cols As Long
  
  Const maxSize As Long = 50 '<- tThis would be max. no. or rows and max. no. of columns
  Const TopLeftCell As String = "H3"  '<-Top left cell of the board
  
  Rws = Range("D3").Value
    Application.SendKeys "{return}"

  Cols = Range("D5").Value
    Application.SendKeys "{enter}"

  Application.ScreenUpdating = False
  With Range(TopLeftCell).Resize(maxSize, maxSize)
    .Interior.Color = xlNone
    .Borders.LineStyle = xlNone
    If Rws * Cols > 0 Then
      With .Resize(Rws, Cols)
        .Interior.Color = RGB(200, 200, 200)
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
      End With
    End If
  End With
  Application.ScreenUpdating = True
   Application.SendKeys "{enter}"
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3, D5")) Is Nothing Then Make_Board

End Sub

Display More

rollis13 · Jan 27th 2023

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

In the ThisWorkbook module paste:

Code

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:

Code

Public Prev1   As Variant
Public Prev2   As Variant

In the Sheet's module paste:

Code

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.

SkateRockandRoll · Jan 27th 2023

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 -

=ROUNDDOWN(SUM(C3/A3),0)

to vba?

-------------------------------------------------------------

Here is what i have so far.

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

Code

Public Prev1   As Variant
Public Prev2   As Variant

in my Sheet1 I have this.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3, D5")) Is Nothing Then Make_Board

End Sub


Sub Make_Board()
  Dim Rws As Long, Cols As Long
  
  Const maxSize As Long = 50 '<- tThis would be max. no. or rows and max. no. of columns
  Const TopLeftCell As String = "H3"  '<-Top left cell of the board
  
  Rws = Range("D3").Value
  

  Cols = Range("D5").Value
   

  Application.ScreenUpdating = False
  With Range(TopLeftCell).Resize(maxSize, maxSize)
    .Interior.Color = xlNone
    .Borders.LineStyle = xlNone
    If Rws * Cols > 0 Then
      With .Resize(Rws, Cols)
        .Interior.Color = RGB(200, 200, 200)
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
      End With
    End If
  End With
  Application.ScreenUpdating = True
   Application.SendKeys "{enter}"
End Sub

Display More

then , in my workbook I have this.

Code

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

rollis13 · Jan 27th 2023

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.

SkateRockandRoll · Jan 27th 2023

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.

Code

Any thoughts as to why?

SkateRockandRoll · Jan 27th 2023

disregard.

found an extra line of code not needed.

rollis13 · Jan 27th 2023

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.

SkateRockandRoll · Jan 28th 2023

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.

Code

  Application.ScreenUpdating = False
  With Range(TopLeftCell).Resize(maxSize, maxSize)
    .Interior.Color = xlNone
    .Borders.LineStyle = xlNone
    If Rws * Cols > 0 Then
      With .Resize(Rws, Cols)
        .Interior.Color = RGB(200, 200, 200)
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThick
        End With
      End With
    End If
  End With
  Application.ScreenUpdating = True
   Application.SendKeys "{enter}"
End Sub

Display More

rollis13 · Jan 28th 2023

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

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

Want to use sendkeys to replicate the Enter key

SkateRockandRoll Jan 27th 2023

Participate now!

Share

Similar Threads

Refresh Page When Date Changes

Index Match with proration of upper and lower values.

Ascending and Ascending & FilterBy and SortBy Buttons

Use cell values to create hyperlinks using VBA Worksheet_Change Event

Input Data box open based on cells value condition unload in different cells

Auto Summing a Column with the hash key from Data in Rows

Tags