Programmatically press enter with Excel VBA

Question

I am trying to programmatic press the enter key once a value has been set to specific cell:

Private Sub TextBox1_Change()
    If TextBox1.Text = "ALC Test" Then
        Range("$F$2").Value = "17"
        ActiveWorkbook.RefreshAll
    End If
    If TextBox1.Text = "ALC Prod" Then
        Range("$F$2").Value = "54"
        ActiveWorkbook.RefreshAll
    End If
    If TextBox1.Text = "" Then
        Range("$F$2").Value = ""
        ActiveWorkbook.RefreshAll
    End If
End Sub

So that I can trigger in excel the refresh of my table But that does not to seam to work correctly

Your question is actually not pressing the enter key, but you want excel to recalculate (refresh) your table after a value is updated using vba. Is that correct? — davejal
– davejal, Commented Jan 18, 2017 at 13:34

davejal · Accepted Answer · 2017-01-18 13:57:44Z

2

Using sendkeys is correct.

If you just want to recalculate (refresh) your table data, you could do the following:

Private Sub TextBox1_Change()

If TextBox1.Text = "ALC Test" Then
        Range("$F$2").Value = "17"
        Range("$F$2").Select
        Application.SendKeys("~")

In your situation, you need to select the cell (that holds the parameters) before the enter command is send to excel.

edited Jan 18, 2017 at 13:57

answered Jan 18, 2017 at 13:17

davejal

6,19110 gold badges45 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Niko.K Over a year ago

Thanks allot for your answer yes I did that but somehow the excel refresh is not getting triggered to update my tabel

davejal Over a year ago

I edited my answer, depending on what you actually want to achieve there are more options

Niko.K Over a year ago

I am sorry if that was not clear from original question: I type in my text box for example "ALC Test" then in the cell F2 the number 17 appears. The cell F2 has been set as a parameter field on witch my table is updating the part of updating the field is not working , i think that is not getting triggered since there is no enter input when I do it like that manually it works fine

Niko.K Over a year ago

Thanks for you effort ! but I am sorry still nothing happens .. update my question code

davejal Over a year ago

could your provide some more details? How is the table refreshed when using the enter key? Using a filter or macro ?

|

Pierre · Accepted Answer · 2017-01-18 13:26:00Z

0

What you do is: change the value, and then press "enter" , which has no effect.

Use

calculate

or

activesheet.calculate

edited to make it clear:

Private Sub TextBox1_Change()
If TextBox1.Text = "ALC Test" Then
    Range("$F$2").Value = "17"
    calculate

edited Jan 18, 2017 at 13:26

answered Jan 18, 2017 at 13:10

Pierre

1,0467 silver badges21 bronze badges

3 Comments

Niko.K Over a year ago

Hey thanks a lot for your answer can you give me a bit more context to where I hace to set that ? or instead of what ?

Pierre Over a year ago

Just replace your sendkey line with one of those :)

Niko.K Over a year ago

like that Application.Calculate ("~")

Collectives™ on Stack Overflow

Programmatically press enter with Excel VBA

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related