Click button, insert text from that button

Question

I'm just starting with VBA and i'm stuck on a userform.

I am looking for a method that will allow me to click a command button and the text that is on that button will then appear in the last blank cell in a certain column. (for exemple in A2)

Private Sub CommandButton1_Click()
    If MsgBox("Please confirm your choice ?", vbYesNo, "Confirmation") = vbYes Then
        Worksheets("Sheet1").Select
        ligne = Sheets("Sheet1").Range("A456541").End(xlUp).Row + 1
    End If
    
    Worksheets("Sheet1").Activate
    Range("A2").Select Range("A2").Value = "Multiproject" & vbNewLine & vbNewLine
End Sub

After pen comment, my code looks like this :

Private Sub Multiproject_Click()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim NextFreeCell As Range ' find next free cell in column A
Set NextFreeCell = ws.Cells(ws.Rows.Count, "A").End(xlUp)

If MsgBox("Please confirm your choice?", vbYesNo, "Confirmation") = vbYes Then
    NextFreeCell.Value = "Multiproject" & vbNewLine & vbNewLine
End If

Unload FrmCustomMsgbo

End Sub [1]: https://i.sstatic.net/4rGKP.png

Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary that you show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question. — Pᴇʜ
– Pᴇʜ, Commented May 3, 2022 at 8:58
Hello Pen, thank you for your advices :) For now my code is " Private Sub CommandButton1_Click() If MsgBox("Please confirm your choice ?", vbYesNo, "Confirmation") = vbYes Then Worksheets("Sheet1").Select ligne = Sheets("Sheet1").Range("A456541").End(xlUp).Row + 1 End If Worksheets("Sheet1").Activate Range("A2").Select Range("A2").Value = "Multiproject" & vbNewLine & vbNewLine End Sub" I would like my code not to appear on A2 but on the first blank cell on the column A — Guillaume
– Guillaume, Commented May 3, 2022 at 9:02
Please edit your question to add the code there formatted as code block. Code in coments gets useless due to missign formating. — Pᴇʜ
– Pᴇʜ, Commented May 3, 2022 at 9:04

Pᴇʜ · Accepted Answer · 2022-05-03 12:39:02Z

1

Just find the next free cell and write the value there:

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim NextFreeCell As Range ' find next free cell in column A
    Set NextFreeCell = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(RowOffset:=1)

    If MsgBox("Please confirm your choice?", vbYesNo, "Confirmation") = vbYes Then
        NextFreeCell.Value = "Multiproject" & vbNewLine & vbNewLine
    End If
End Sub

You might benefit from reading How to avoid using Select in Excel VBA.

And I highly recommend to give your command button a useful name CommandButton1 is not useful at all.

edited May 3, 2022 at 12:39

answered May 3, 2022 at 9:09

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

4 Comments

Guillaume Over a year ago

Thanks for the help and your advices. I changed my command buttons. Unfortunately, when I run the code it doesn't give me any result. The word "multiproject" does not appear in my excel cell.

Pᴇʜ Over a year ago

@GuillaumeChomienne Does the code run at all? Does the message box "Please confirm your choice?" appear?

Guillaume Over a year ago

The message box appears with the message "pls confirm your choice". Then or it does nothing if the previous line was already "multiproject" , or it replace the last NON EMPTY cell...

Pᴇʜ Over a year ago

@GuillaumeChomienne Ahh, my bad. See my edit. I forgot .Offset(RowOffset:=1). Now it should work.

Collectives™ on Stack Overflow

Click button, insert text from that button

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related