Adding Macros to Buttons

[Chrispydennis2]

Hello, I’m fairly new to the excel game and I’m trying to build a schedule that is a little more user friendly for our meetings than what we are currently using.

This might be simple but I am struggling to get what I want.

I am wanting to add a button (Job 1) then I am wanting to click on the cell according to day and crew member. When I click the button I am wanting that job to duplicate to that cell.

For reference, I would want to click (job 1) than I would like to click any cell on the schedule E5- I 11 and that job name would appear in that cell. I’m sorry for the bad picture and the lazy excel creation, I just wanted to submit some form of visual. Thank you

 

[GlennUK]

I don't know about anyone else, but on my computer that picture is of such low quality I can't see what is what. Use a snipping tool or screen-grab tool instead of a photo.

 

[kanadaaa]

Try this in the sheet's module:

Private Sub CommandButton1_Click()
    Dim r As Range
    Sheets(1).Activate
    On Error GoTo errhandler
    Set r = Application.InputBox("Select a cell to fill in with the job name.", "Fill", Type:=8)
    On Error GoTo 0
    If Intersect(r, Range("E5:I11")).Count = 1 Then
        r.Value = CommandButton1.Caption
    Else
        MsgBox "Selection out of range", vbExclamation, "Error"
    End If
Exit Sub
errhandler: 'Do nothing if selection is canceled
End Sub

Private Sub CommandButton2_Click()
    Dim r As Range
    Sheets(1).Activate
    On Error GoTo errhandler
    Set r = Application.InputBox("Select a cell to fill in with the job name.", "Fill", Type:=8)
    On Error GoTo 0
    If Intersect(r, Range("E5:I11")).Count = 1 Then
        r.Value = CommandButton2.Caption
    Else
        MsgBox "Selection out of range", vbExclamation, "Error"
    End If
Exit Sub
errhandler: 'Do nothing if selection is canceled
End Sub

Private Sub CommandButton3_Click()
    Dim r As Range
    Sheets(1).Activate
    On Error GoTo errhandler
    Set r = Application.InputBox("Select a cell to fill in with the job name.", "Fill", Type:=8)
    On Error GoTo 0
    If Intersect(r, Range("E5:I11")).Count = 1 Then
        r.Value = CommandButton3.Caption
    Else
        MsgBox "Selection out of range", vbExclamation, "Error"
    End If
Exit Sub
errhandler: 'Do nothing if selection is canceled
End Sub

 

[Chrispydennis2]