CODEWITHSUNDEEP
excelvbauserform
Guillaume
Guillaume

Reputation: 25

Click button, insert text from that button

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

Upvotes: 1

Views: 1259

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

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.

Upvotes: 1

Related Questions