Command buttons to input data in specific cells

[Callum Paterson]

I have created an Excel form that contains command buttons. Each of the buttons represents a number. When the button is pressed I would like the number to appear in a specific cell.

See form below...

For example if the project duration was 2 months between March and April, I would select the button labeled 0.83, and the cell in my worksheet would be populated with the value of 0.83.

I would also like the form to close as the value is selected.

And help on the coding for this would be much appreciated.

Thanks

 

[RayFrye]

My suggestion is to not use buttons but use the value in the cell once triggered to send the value in the selected cell to the cell of your choice.
When any cell is selected the value of that cell is, in my example, placed in Cell B2.
This will be flexible such that as value change all you need to do is change the value of the cell and everything works.
Plus the code is simple and straight forward.

'This code is in the WorkSheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("B2").Value = Target.Value
End Sub

 

[Micron]

0.83 is the button caption or is it also the button name? If you need to use buttons and 0.83 is the caption, then
Sheets("yourSheetName").Range("A1") = Me.YourButtonNameHere.Caption
Substitute your range address, sheet name and button name.

 

[NoSparks]

@RayFrye the op's picture shows a user form.

The way I understand this the form already exists and has about 50 buttons, 'specific cell' being the same for all buttons.

If this is the case, I'd put a temporary label on the form and have it run this code to produce a text file with the button_click code required for each button,
then copy and paste that into the form module (for the buttons requiring it). Saves writing the procedure 50 times.
Change the 'specific' cell and folder path as required.

Private Sub TmpLabel_Click()
  Dim ctrl As MSForms.Control
  Dim str As String
  Dim fso As Object
  Dim Fileout As Object
  
    Set fso = CreateObject("Scripting.FileSystemObject")

    Set Fileout = fso.CreateTextFile("D:\Junk\vba.txt", True, True)
    
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CommandButton" Then
            str = "Private Sub " & ctrl.Name & "_click()" & vbLf & _
                  "  Sheet1.Cells(1,1).Value = " & ctrl.Caption & vbLf & _
                  "  Unload Me" & vbLf & _
                  "End Sub" & vbLf & vbLf
            Fileout.Write str
            str = ""
        End If
    Next ctrl
    
    Fileout.Close
    
End Sub

Another option would be a class module as DanteAmor suggested in this thread.