1

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.stack.imgur.com/4rGKP.png

Guillaume
  • 25
  • 6
  • 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 [ask] might help you to improve your question. – Pᴇʜ May 03 '22 at 08: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 May 03 '22 at 09:02
  • Please [edit] your question to add the code there formatted as code block. Code in coments gets useless due to missign formating. – Pᴇʜ May 03 '22 at 09:04

1 Answers1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 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. – Guillaume May 03 '22 at 10:27
  • @GuillaumeChomienne Does the code run at all? Does the message box *"Please confirm your choice?"* appear? – Pᴇʜ May 03 '22 at 12:03
  • 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... – Guillaume May 03 '22 at 12:25
  • @GuillaumeChomienne Ahh, my bad. See my edit. I forgot `.Offset(RowOffset:=1)`. Now it should work. – Pᴇʜ May 03 '22 at 12:39