title: Can Excel Vba code be updated via an Excel Add In for multiple users
body: <p>I have an Excel workbook that contains lots of VBA code. The VBA Code consists of many Sub routines, Functions and User Forms. Over 200+ employees will be using this Workbook.</p>
  
  <p>Currently my VBA code lives inside the distributed Excel Workbook. The problem I fear I will be faced with is updating each Workbooks VBA code if any update is ever needed.</p>
  
  <p>Would it be best to write all my VBA code as part of an Add In, upload a new version of the Add In to a site and have employees download from there? If so, would I encounter any limitations or restrictions? Is such feature even possible? Is VB.Net a better solution?</p>
  
  <p>I have created an XLAM file from my original Workbook File. The original Workbook file containa all my Sub Routines, Functions, and UserForms. I am encountering an error when calling the UserForm directly, even though I referenced the XLAM file that contains UserForm1.  </p>
  
  <p>The following scenarios are being ran from the distributed WorkBook copy. The WorkBook is referencing the XLAM file. </p>
  
  <p><strong>Scenario1: Calling a UserForm from a Sub assigned to a shape</strong>
  The following Sub returns a <code>Runtime Error 424 Object Required</code></p>
  
  <pre><code>Sub RectangleRoundedCorners1_Click()
  UserForm1.Show 'highlights this line on the error, XLAM reference houses UserForm1
  End Sub
  </code></pre>
  
  <p><strong>Scenario2: Calling a Sub Procedure from a shape that calls the UserForm</strong> 
  This method doesn't return an error, why? Can we not reference UserForm Objects from a referenced Add In? </p>
  
  <pre><code>Sub RectangleRoundedCorners1_Click()
  showUserForm
  End Sub
  
  Sub showUserForm()
  UserForm1.Show
  End Sub
  </code></pre>
  
  <p><strong>Scenario 3: Using UserForms to input values into Worksheet Cells</strong></p>
  
  <p><em>Would I have to refrence the <code>ActiveWorkbook</code> in each of my UserForms?</em></p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Set wb = ActiveWorkbook
  Set ws = wb.Sheets("clientmenu")
      forceLogOut
      'clear filter so that we dont 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 &lt;&gt; "" 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 &lt;&gt; "" 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" &amp; Rows.Count).End(xlUp).Row + 1
  
          If Me.priority_ = vbNullString Then
              ws.Range("A" &amp; lastrow).Interior.Color = vbWhite
              ws.Range("A" &amp; lastrow).Font.Color = RGB(0, 0, 0)
  
          ElseIf Me.priority_ = "None" Then
              ws.Range("A" &amp; lastrow).Interior.Color = vbWhite
              ws.Range("A" &amp; lastrow).Font.Color = RGB(0, 0, 0)
              ws.Range("B" &amp; lastrow).value = vbNullString
  
          ElseIf Me.priority_ = "High" Then
              '.Cells(x, 1).Interior.Color = RGB(0, 176, 80)
              ws.Range("A" &amp; lastrow).Font.Color = RGB(0, 176, 80)
              ws.Range("B" &amp; lastrow).value = addnewClient.priority_.Text
          ElseIf Me.priority_ = "Medium" Then
              '.Cells(x, 1).Interior.Color = RGB(255, 207, 55)
              ws.Range("A" &amp; lastrow).Font.Color = RGB(255, 207, 55)
              ws.Range("B" &amp; lastrow).value = addnewClient.priority_.Text
          ElseIf Me.priority_ = "Low" Then
              '.Cells(x, 1).Interior.Color = RGB(241, 59, 59)
              ws.Range("A" &amp; lastrow).Font.Color = RGB(241, 59, 59)
              ws.Range("B" &amp; 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 &lt;&gt; vbNullString Then
  
  ws.Range("L" &amp; lastrow).value = Format(Now(), "mm/dd/yyyy")
  
          ws.Range("A" &amp; lastrow).value = addnewClient.client.Text
          ws.Range("A" &amp; lastrow).Font.Name = "Arial"
          ws.Range("A" &amp; lastrow).Font.Size = 18
          ws.Range("A" &amp; lastrow).Font.Bold = True
  
  
  
          ws.Range("B" &amp; lastrow).Font.Name = "Arial"
          ws.Range("B" &amp; lastrow).Font.Size = 14
          ws.Range("B" &amp; lastrow).HorizontalAlignment = xlCenter
  
          ws.Range("C" &amp; lastrow).value = addnewClient.priority.Text
  
          ws.Range("C" &amp; lastrow).Font.Name = "Arial"
          ws.Range("C" &amp; lastrow).Font.Size = 14
          ws.Range("C" &amp; lastrow).HorizontalAlignment = xlCenter
  
          ws.Range("E" &amp; lastrow).value = addnewClient.contact.value
          ws.Range("E" &amp; lastrow).Font.Name = "Arial"
          ws.Range("E" &amp; lastrow).Font.Size = 14
          ws.Range("E" &amp; lastrow).HorizontalAlignment = xlCenter
  
  
          ws.Range("G" &amp; lastrow).value = addnewClient.result.Text
          ws.Range("G" &amp; lastrow).Font.Name = "Arial"
          ws.Range("G" &amp; lastrow).Font.Size = 14
          ws.Range("G" &amp; lastrow).HorizontalAlignment = xlCenter
  
  
          ws.Range("I" &amp; lastrow).value = addnewClient.segmentType.Text
          ws.Range("I" &amp; lastrow).Font.Name = "Arial"
          ws.Range("I" &amp; lastrow).Font.Size = 14
          ws.Range("I" &amp; lastrow).HorizontalAlignment = xlCenter
  
          ws.Range("K" &amp; lastrow).value = addnewClient.notes.Text
  
          If Me.contact = vbNullString Then
          ElseIf Me.contact &lt;&gt; vbNullString Then
              ws.Range("J" &amp; lastrow) = Sheet3.Range("J" &amp; lastrow).value + 1
              ws.Range("J" &amp; lastrow).Font.Name = "Arial"
              ws.Range("J" &amp; lastrow).Font.Size = 14
              ws.Range("J" &amp; lastrow).Font.Bold = True
              ws.Range("J" &amp; lastrow).HorizontalAlignment = xlCenter
  
          End If
          End If
  
      End With
  
  
  
      'With Sheet3
      'Sheet3.Range("A" &amp; lastrow &amp; ":K" &amp; lastrow).Interior.Color = vbWhite
      Application.GoTo Range("A" &amp; lastrow), True
      'End With
  
      wb.Sheets(2).Range("C4") = Format(Now, "mm/dd/yyyy")
      Unload Me
  
  End Sub
  </code></pre>