On Click Command Button Macro

I'm trying to create a macro for a command button that when clicked, will get the job number from that row and look for a file for that job. If it does not exist I want it to copy from a template and save with a new name, otherwise just open the file.

However, I cannot seem to work out how to get hold of the information for the command button that calls the macro. This is what I have so far:

Public Function ShapeExists(OnSheet As Object, Name As String) As Boolean

    On Error GoTo ErrShapeExists
    If Not OnSheet.Shapes(Name) Is Nothing Then
        ShapeExists = True
    End If
    Exit Function

End Function

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim buttonName As String
buttonName = (Target.Row - 1)
    If Not ShapeExists(ActiveSheet, buttonName) Then
      If Range("O" & Target.Row).Value = "" And Target.Column <= 14 And Target.Row > 1 Then
            ActiveSheet.Buttons.Add(910.5, Range("O" & Target.Row).Top, 80, 20).Select
            Selection.Name = buttonName
            Selection.OnAction = "Sheet1.JobButton"
            Selection.Characters.Text = "Open Job"
      End If
    End If
End Sub

Private Sub JobButton()
Dim newText As String

If Range("N" & Selection.TopLeftCell.Row).Value <> "" Then
    newText = "Job " & Range("N" & Selection.TopLeftCell.Row).Value
    Dim checkFilename As String
    Dim check As String
    check = "N" & Selection.TopLeftCell.Row
    checkFilename = newText & ".xlsm"
    If Dir(checkFilename) <> "" Then
    Workbooks.Open (newText)
    Dim SrcBook As Workbook
    Set SrcBook = ThisWorkbook
    Dim NewBook As Workbook
    NewBook = Workbooks.Open("Job Template.xlsm")
    SrcBook.Worksheets(1).Range("D" & Selection.TopLeftCell.Row).Copy
        With NewBook
            .Title = newText
            .Subject = newText
            .SaveAs Filename:=newText
        End With
    End If
MsgBox ("Job Should always have a number."), , "NO JOB NUMBER"

End If
End Sub

As you can see I am currently trying ActiveSheet.Shapes(Application.Caller).Select, this is causing a "Run-time error '13': Type mismatch".

Any help would be much appreciated, thank you!


Right-click the button --> View Code --> put your JobButton code here

Need Your Help

Determine Azure Web Role's instance IP address

c# .net azure azure-cloud-services

The IP address I am looking for is not the VIP, nor the private IP address that is given to the role instance, but the public IP address that you can assign to each instance using this configuratio...

This MQL query does not return complete results

freebase mql

I have run the following query in the Freebase MQL query editor:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.