Shortening code

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Here are the three new button subs and the single sig code. Let me know how it works.

VBA Code:
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The ImgG and related variables should be literal string values - i.e. "ImgG" - and not variables.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

Code:
cmdSig "ImgT"

for example.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top