Shortening code

[dpaton05]

I have 3 signature buttons that call a procedure each to insert the relevant signature. The 3 called subs are almost identical with the only difference being the signature image to use. I want to just have one sub and send data from the caller subs to specify which signature needs to be inserted. I know it is using arguments but I not sure how to do this. Could someone help me please?

3 caller subs

Private Sub cmdGB_Click()
Quoting.Unprotect password:=ToUnlock
    Call cmdNoSig
    cmdGarrettSig
'Quoting.Protect password:=ToUnlock
End Sub

Private Sub cmdLS_Click()
Quoting.Unprotect password:=ToUnlock
    Call cmdNoSig
    Call cmdLynSig
'Quoting.Protect password:=ToUnlock
End Sub

Private Sub cmdTS_Click()
Quoting.Unprotect password:=ToUnlock
    Call cmdNoSig
    Call cmdTraceySig
'Quoting.Protect password:=ToUnlock
End Sub

3 called subs

Function LastRow()
'Dim LastRow As Long
    With Sheets("CSS_quote_sheet")
        LastRow = .Range("A:H").Find(What:="*", _
            After:=.Range("A1"), _
            Lookat:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
    End With
End Function

Sub cmdGSig()
Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgG").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

Sub cmdTSig()
Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgT").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

Sub cmdLSig()
Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes("ImgL").Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

 

[ManiacB]

Here are the three new button subs and the single sig code. Let me know how it works.

Private Sub cmdGB_Click()
Dim ImgG As String
Quoting.Unprotect Password:=ToUnlock
    Call cmdNoSig
    cmdSig Sig:=ImgG
'Quoting.Protect password:=ToUnlock
End Sub

Private Sub cmdLS_Click()
Dim ImgL As String
Quoting.Unprotect Password:=ToUnlock
    Call cmdNoSig
    cmdSig Sig:=ImgL
'Quoting.Protect password:=ToUnlock
End Sub

Private Sub cmdTS_Click()
Dim ImgT As String
Quoting.Unprotect Password:=ToUnlock
    Call cmdNoSig
    cmdSig Sig:=ImgT
'Quoting.Protect password:=ToUnlock
End Sub

Sub cmdSig(Sig As String)
Dim a As Double, aa As Double, aaa As Double
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Shapes(Sig).Duplicate.Name = "Signature"
        .Shapes("Signature").Cut
    End With
        Sheets("CSS_quote_sheet").Cells(43, 1).PasteSpecial
        Sheets("CSS_quote_sheet").Shapes(Selection.Name).Name = "Signature"
        a = Sheets("CSS_quote_sheet").Cells(LastRow, 1).Top + 140
        aa = Sheets("CSS_quote_sheet").Shapes("Signature").Height
        aaa = Rows(Sheets("CSS_quote_sheet").HPageBreaks(1).Location.Row).Top + 1
    With Sheets("CSS_quote_sheet").Shapes("Signature")
        .Left = ActiveSheet.Range("A1").Left
        .Top = IIf(a + aa > aaa, aaa, a)
        .Placement = 1
    End With
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

This made excel crash.

 

[RoryA]

The ImgG and related variables should be literal string values - i.e. "ImgG" - and not variables.

 

[dpaton05]

Thanks Rory, do you mean that the name of a variable can't be the same as it's value?

 

[RoryA]

No, it can be. The problem here is that the variable is never given a value, and it seems like overkill to me to create a variable solely to pass it as an argument, when you can just use the necessary value directly:

cmdSig "ImgT"

for example.

 

[dpaton05]

I thought I would have to be able to pass something to distinguish which signature image to use.

Depending on which of the 3 buttons are pressed would pass a different, identifier to the signature sub, well that is what I thought I could do. Do you have any ideas on how I could tackle this?

 

[RoryA]

Yes, doing what I just posted. You just need to amend the code that ManiacB posted to pass the values as strings, rather than empty variables.

 

[ManiacB]

Thanks RoryA!