# Can Excel Vba code be updated via an Excel Add In for multiple users

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
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
result.BorderColor = vbRed
result.BackColor = vbYellow
result.DropDown
Exit Sub

ElseIf contact.value = vbNullString And result.value <> "" Then
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)
ElseIf Me.priority_ = "Medium" Then
'.Cells(x, 1).Interior.Color = RGB(255, 207, 55)
ws.Range("A" & lastrow).Font.Color = RGB(255, 207, 55)
ElseIf Me.priority_ = "Low" Then
'.Cells(x, 1).Interior.Color = RGB(241, 59, 59)
ws.Range("A" & lastrow).Font.Color = RGB(241, 59, 59)
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).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).Font.Name = "Arial"
ws.Range("C" & lastrow).Font.Size = 14
ws.Range("C" & lastrow).HorizontalAlignment = xlCenter

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

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

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

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")

• 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? – user179210 Aug 2 '19 at 6:18
• @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 – Jose Aug 2 '19 at 6:23
• 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. – user179210 Aug 2 '19 at 6:26
• @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` – Jose Aug 2 '19 at 6:34