Setting common routine in a macro (taking out of userform)

[DblDocWhitaker]

Good morning. I have been building various userforms for auto-filling standard letters that we use in the office. Rather than typing the same series of commands in each userform (such as for identifying contact information for the designated person), I was hoping to build a macro and then call that macro in the userforms that need it.

My userform has a combobox that lists the persons in the office. The userform gathers that information as this:

Dim Sign As String
Sign = Me.cbSign.Value

I then created a class module (shown below my text). I call this module from my user form with:

Call ContactInfo

However, the information is not loading. For example, I tried troubleshooting by placing "msgbox Facsimile" in various places to see if I was getting the information loaded, and it gives me a blank message box. What am I missing?

******************************************
'For populating signature blocks and such

Public Function ContactInfo()
Dim SignLast As String
SignLast = Sign
Dim SignFirst As String
Dim AreaCode As String
Dim SignPhone As String
Dim SignEmail As String
Dim SignJob As String
Dim SignAdd1 As String
Dim SignAdd2 As String
Dim SignCity As String
Dim SignState As String
Dim SignZipCode As String
Dim Facsimile As String

With Worksheets("Attorneys").Range("LastName")
Set c = .Find(Sign, LookAt:=xlWhole)
If Not c Is Nothing Then
SignFirst = c.Offset(0, -1).Value
AreaCode = c.Offset(0, 2).Value
SignPhone = c.Offset(0, 3).Value
SignEmail = c.Offset(0, 4).Value
SignJob = c.Offset(0, 6).Value
SignAdd1 = c.Offset(0, 7).Value
SignAdd2 = c.Offset(0, 8).Value
SignCity = c.Offset(0, 9).Value
SignState = c.Offset(0, 10).Value
SignZipCode = c.Offset(0, 11).Value
Facsimile = c.Offset(0, 12).Value
End If
End With
End Function

 

[dmt32]

Hi,
your function is not returning anything which probably explains the blank msgbox prompts

If your idea is to have a common function that returns found values in a range that can be used by different userforms in your project then maybe, following update to your function will do what you want

Place in a STANDARD module

Public Function ContactInfo(ByVal Target As Range, ByVal Search As String) As Variant
    Dim FoundCell As Range
    Dim arr As Variant
  
    Set FoundCell = Target.Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
    If Not FoundCell Is Nothing Then
        ContactInfo = Application.Index(FoundCell.Offset(, -1).Resize(, 14).Value, _
                                  Index, Array(1, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14))
    Else
        ContactInfo = CVErr(10)
    End If

End Function

Function accepts two arguments -
Target = the range you are searching
Search = the value you are searching the range for

If search value found, function should return a variant array of all required values in found range which negates need for all the declared variables. If no match is found, an error is returned.

and to call it from your userform combobox

Private Sub ComboBox1_Change()
    Dim rngLastName As Range
    Dim arr         As Variant
    Dim i           As Integer
  
    Sign = Me.ComboBox1.Text
    Set rngLastName = Worksheets("Attorneys").Range("LastName")
    arr = ContactInfo(rngLastName, Sign)
  
    If Not IsError(arr) Then
    'record found do stuff
  
        For i = 1 To UBound(arr)
            MsgBox arr(i)
        Next
      
    Else
    'no match inform user
        MsgBox Sign & Chr(10) & "Record Not Found", 64, "Not Found"
      
    End If
End Sub

Solution untested & may need adjustment to meet specific project need but hopefully, will give some ideas for you to work with

Others here may have alternative suggestions

Dave