Excel VBA - Update cell with existing value + user form input
<p>Background: My team would like to use a shared Excel workbook to track time spent on TFS work items.  We figure this can be done easily using VBA, but we're new to VBA and can't quite find the combination of what works.  </p>

<p>After linking to TFS, we have a table which may look something like this:</p>

<pre><code>ID |Title | Description | Category | State             | Completed Work
===========================================================================
140 Test 1  VBA is fun    Testing    Programming         0
141 Test 2  VBA is great  Testing    Programming         0
142 Test 3  Testing VBA   Testing    Programming         0
143 Test 4  Need help     Testing    Programming         0
144 Test 5  Questions     Testing    Programming         0
</code></pre>

<p>We want to update the Completed Work field based on entries from a pop-up form.  (Or, better yet, have the form directly on the worksheet.)  </p>

<p>The form part is easy.  It will have only two text boxes: Ticket ID and Time Spent.  As well as an Update control button.  When both text boxes are entered and Update is clicked, it should add Time Spent to Completed Work field for the Ticket ID supplied.  What we can't get right is the code to make that happen.  I expect it will be some combination of If / Then / Else and loops.</p>

<p>Specifically we need help with the following...</p>

<p>1) Once Ticket ID and Time Spent are entered, and "Update" button is clicked, this is the desired behavior:</p>

<ul>
<li>Search worksheet to find the ID column (location of ID column may change)</li>
<li>Search this column for Ticket ID supplied by user</li>
<li>If a match is found, search adjacent cells to find the Completed Work column (location of Completed Work column may also change). Else trigger error message and close</li>
<li>Get existing value in Completed Work column, and add value from Time Spent text box</li>
<li>Update value in Completed Work column with the new total</li>
</ul>

<p>The following is very much simplified and doesn't work anyway.  But to give an idea:</p>

<pre><code>Private Sub UpdateCompletedWork_Click()

   Dim ExistingCompletedWork AS Integer

   If Sheet1.Range("A1:G100").Value = IDTextBox.Value
   Then ExistingCompletedWork.Value = Sheet1.Range([Completed Work field in same row])

   [Completed Work field] = ExistingCompletedWork.Value + TimeSpentTextBox.Value 

   Else MsgBox("ID Not Found")

End Sub 
</code></pre>

<p>How do we find a location of a cell based off the column name AND value of a text box on a user form? </p>

<p>Once that's found, how can we look through cells on the same row to get the value of another cell, based on its column name?  </p>

<p>2) If a user has an ID highlighted (active cell), we want to automatically populate the Ticket ID text box in the form.  How can we accomplish this?  I figured it would be as easy as setting it to active cell value on initialization: </p>

<pre><code>Private Sub UserForm_Initialize()

    IDTextBox.Value = ActiveCell.Value

End Sub
</code></pre>

<p>But that fails with Run-time Error 424: Object Required.</p>