standard output message

[Koert3]

Hello,

I want to make a macro that makes a standard output message with some variables when I press a button. I put an excel sheet in the attachments to explain what I mean. I want the macro to copy the message so it is on my clipboard and I can paste it in an other application.

I want to make a macro where I can make this message:
"
Hello, we have a new job:
[JOB(col.B)]
🗓 [DATE(col.A)] starts [begin(col.D)] until [end(col.E)]

🦐 There are [counted people(col.G:colI)]/[people needed(col.F)] slots available.

Let us know if you want to work!
"

So for example, the message for row 6 should look like this:
"
Hello, we have a new job:
Ticketscan 2 Peacock
🗓 04-03 starts 12:30 until 20:00

🦐 There are 1/2 slots available.

Let us know if you want to work!
"


What is the easiest way to realise this? I have some experience in VBA but that is mostly recording macro and editing it or copying it from a youtube video.

P.S. I don't know if it makes any difference but I use mac.

Every bit of help is much appreciated!

[bebo021999]

I'd suggest not to use button, but double click in any cells in range G:I
For example, double click on G6 (or H6 or I6), a text string will be generated in cell, i.e, K6, then copy.
Move to other application then paste.
This code is put in worksheet module: (Right click tab' name, viewcode then copy/paste)

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lr&, r&, avail&, msg As String
Cancel = True
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(Target, Range("G2:I" & lr)) Is Nothing Or Target.Count > 1 Then Exit Sub
r = Target.Row
avail = Cells(r, "F") - WorksheetFunction.CountA(Range(Cells(r, "G"), Cells(r, "I")))
Select Case avail
    Case 0
          MsgBox "Full!"
          Exit Sub
    Case Else
        msg = "Hello, we have a new job:" & vbLf & Cells(r, "B") & vbLf & Format(Cells(r, "A"), "dd-mm") & _
        " starts " & Format(Cells(r, "D"), "hh:mm") & " until " & Format(Cells(r, "E"), "hh:mm") & vbLf & vbLf & _
        "There are " & avail & "/" & Cells(r, "F") & " slots available."
End Select
Cells(r, "K").Value = msg ' use column K to generate message. Adjust to any other column if K is not available.
Cells(r, "K").Copy
End Sub 

[kvsrinivasamurthy]

here is formula solution. It wil be real time. If you edit any row (adding name in column G:I) automatically message will be up dated. No need of running macro.
In K2 copied down.

Please Login or Register  to view this content.

[Koert3]

Thank you both very much. I used the macro from the first reaction and added the Putinclipboard function of vba. So it is easy to use for everyone.