0
\$\begingroup\$

I have created an XLAM file called "MyCustomAddIn" from an XLSB file called "Call Tracker" which contains all my Subroutines, Functions and UserForms. I have referenced "MyCustomAddIn" inside of "Call Tracker" workbook.

Below are a few scenarios when calling Subs, Functions or UserForms from "MyCustomAddIn" reference. All macros are being called from my WorkBook, "Call Tracker" My ultimate goal is to not have to reference the ActiveWorkbook for each Sub, Function, and UserForm.

I have noticed that without referencing "Call Tracker" as the ActiveWorkbook, the user form values will go to a sheet inside "MyCustomAddIn" XLAM file, same goes for UDF, it gets the values from a sheet inside of "MyCustomAddIn"

Is there a better way to create and deploy an Add-In that contains all my VBA Code without containing any sheets as an XLAM file does?

Scenario1: Calling a UserForm from a Sub assigned to shape within "Call Tracker" The following Sub returns a Runtime Error 424 Object Required

Sub RectangleRoundedCorners1_Click()
UserForm1.Show 'highlights this line on the error, XLAM reference houses UserForm1
End Sub

Scenario2: Calling a Sub Procedure from a shape that calls the UserForm This method doesn't return an error, why? Can we not reference UserForm Objects from a referenced Add-In?

'assign the sub to a shape in "Call Tracker" Workbook
Sub RectangleRoundedCorners1_Click()
showUserForm
End Sub

'call the user form
Sub showUserForm()
UserForm1.Show
End Sub

Scenario 3: Using UserForms to input values into Worksheet Cells

Would I have to reference the ActiveWorkbook in each of my UserForms?

Private Sub CommandButton1_Click()
Set wb = ActiveWorkbook
Set ws = wb.Sheets("clientmenu")
    forceLogOut
    'clear filter so that we don't mix new customers up

    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
        With ws.Shapes("priorities")
            .Fill.ForeColor.RGB = RGB(64, 64, 64)
        End With
    End If


    If contact.value <> "" And result.value = vbNullString Then
        MsgBox "Please enter a result"
        result.BorderColor = vbRed
        result.BackColor = vbYellow
        result.DropDown
        Exit Sub

    ElseIf contact.value = vbNullString And result.value <> "" Then
        MsgBox "Please enter a date"
        contact.BorderColor = vbRed
        contact.BackColor = vbYellow
        Exit Sub

    Else: With ws
            callDate
            callResult
        End With
    End If




    With ws
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row + 1

        If Me.priority_ = vbNullString Then
            ws.Range("A" & lastrow).Interior.Color = vbWhite
            ws.Range("A" & lastrow).Font.Color = RGB(0, 0, 0)

        ElseIf Me.priority_ = "None" Then
            ws.Range("A" & lastrow).Interior.Color = vbWhite
            ws.Range("A" & lastrow).Font.Color = RGB(0, 0, 0)
            ws.Range("B" & lastrow).value = vbNullString

        ElseIf Me.priority_ = "High" Then
            '.Cells(x, 1).Interior.Color = RGB(0, 176, 80)
            ws.Range("A" & lastrow).Font.Color = RGB(0, 176, 80)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        ElseIf Me.priority_ = "Medium" Then
            '.Cells(x, 1).Interior.Color = RGB(255, 207, 55)
            ws.Range("A" & lastrow).Font.Color = RGB(255, 207, 55)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        ElseIf Me.priority_ = "Low" Then
            '.Cells(x, 1).Interior.Color = RGB(241, 59, 59)
            ws.Range("A" & lastrow).Font.Color = RGB(241, 59, 59)
            ws.Range("B" & lastrow).value = addnewClient.priority_.Text
        End If

If Me.client = vbNullString Then
MsgBox "Must enter Clients name in order to proceed"
Exit Sub
ElseIf Me.client <> vbNullString Then

ws.Range("L" & lastrow).value = Format(Now(), "mm/dd/yyyy")

        ws.Range("A" & lastrow).value = addnewClient.client.Text
        ws.Range("A" & lastrow).Font.Name = "Arial"
        ws.Range("A" & lastrow).Font.Size = 18
        ws.Range("A" & lastrow).Font.Bold = True



        ws.Range("B" & lastrow).Font.Name = "Arial"
        ws.Range("B" & lastrow).Font.Size = 14
        ws.Range("B" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("C" & lastrow).value = addnewClient.priority.Text

        ws.Range("C" & lastrow).Font.Name = "Arial"
        ws.Range("C" & lastrow).Font.Size = 14
        ws.Range("C" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("E" & lastrow).value = addnewClient.contact.value
        ws.Range("E" & lastrow).Font.Name = "Arial"
        ws.Range("E" & lastrow).Font.Size = 14
        ws.Range("E" & lastrow).HorizontalAlignment = xlCenter


        ws.Range("G" & lastrow).value = addnewClient.result.Text
        ws.Range("G" & lastrow).Font.Name = "Arial"
        ws.Range("G" & lastrow).Font.Size = 14
        ws.Range("G" & lastrow).HorizontalAlignment = xlCenter


        ws.Range("I" & lastrow).value = addnewClient.segmentType.Text
        ws.Range("I" & lastrow).Font.Name = "Arial"
        ws.Range("I" & lastrow).Font.Size = 14
        ws.Range("I" & lastrow).HorizontalAlignment = xlCenter

        ws.Range("K" & lastrow).value = addnewClient.notes.Text

        If Me.contact = vbNullString Then
        ElseIf Me.contact <> vbNullString Then
            ws.Range("J" & lastrow) = Sheet3.Range("J" & lastrow).value + 1
            ws.Range("J" & lastrow).Font.Name = "Arial"
            ws.Range("J" & lastrow).Font.Size = 14
            ws.Range("J" & lastrow).Font.Bold = True
            ws.Range("J" & lastrow).HorizontalAlignment = xlCenter

        End If
        End If

    End With



    'With Sheet3
    'Sheet3.Range("A" & lastrow & ":K" & lastrow).Interior.Color = vbWhite
    Application.GoTo Range("A" & lastrow), True
    'End With

    wb.Sheets(2).Range("C4") = Format(Now, "mm/dd/yyyy")
    Unload Me

End Sub
\$\endgroup\$
  • 1
    \$\begingroup\$ Not sure I understand. If you want to reference the workbook that has the running code, it's ThisWorkbook instead of ActiveWorkbook. But you may also declare a variable of class Worksheet, as you do here with ws (I think it's safer: as long as you know the name, you don't have to be in the worksheet for the macro to work). If you want to distribute your code, you could export as a .bas file, but I would not recommend it unless it's going to be used by people who know VBA fairly well (they would have to import the basic code into a workbook to run it). What's wrong with an XLAM? \$\endgroup\$ – Jean-Claude Arbaut Aug 2 at 6:18
  • \$\begingroup\$ @Jean Claude Arbaut basically my code thinks it's still running in the XLAM file when calling it from another workbook. I would like to avoid having to write 'Set WB = ActiveWorkbook` for every subroutine, function, and userform \$\endgroup\$ – Jose Aug 2 at 6:23
  • 1
    \$\begingroup\$ Well, it is still running from the XLAM, since it's where it is stored. It's up to you to make sure the macro acts on the correct sheet: if the macro has to act on ActiveWorkbook, you have to tell it to, one way or another. A single set wb = ActiveWorkkbook is not really a problem, even if it's in every function. \$\endgroup\$ – Jean-Claude Arbaut Aug 2 at 6:26
  • \$\begingroup\$ @jean Claude Arbaut I'm starting to follow. I need to rewrite the code inside the XLAM to reflect ActiveWorkbook, without doing that all the code will run through ThisWorkbook \$\endgroup\$ – Jose Aug 2 at 6:34
  • \$\begingroup\$ That's right. . \$\endgroup\$ – Jean-Claude Arbaut Aug 2 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.