Hello my expert friends!
I have a worksheet where I have a column for status and a column for progress (Status = column I & In Progress = column J). What I do is, change In Progress to Yes and when finished, status gets a value added to it. What I would like to do is when a value gets added to status, the In Progress field in that row gets cleared.
The spreadsheet is new every day so the amount of rows will be different. Is there code that will allow me to work this out?
Ken
Re: Clear Cell based on change in another cell
There's a couple of ways, all involve VBA code - but you don't give enough information to explain the "spreadsheet is new every day" comment.
for example, if you get sent a workbook every day, you could build a 'bootstrap' workbook to locate and load that workbook. The bootstrapper will then monitor changes to the other workbook and update the column as necessary. It can also be set to unload when the other workbook is unloaded.
You could add code (essentially the same as the above) to your Personal workbook but this introduces overhead for all workbooks in Excel (Resources used while loaded etc)...
You could import the data from the new workbook to a template containing the code...
I have no idea which, if any of those suggestions might be suitable. You need to explain.
Re: Clear Cell based on change in another cell
Hi cytop...thank you for replying and I appologize for not being more clear.
Every morning the spreadsheet (template if you will) is populated with patients that need to be called, so everyday the amount of rows are different but the two columns I and J remain constant with what populates. Column I is a status column with a dropdown driven off of this code:
Public Sub CreateList()
With ActiveCell
.Validation.Delete
.Validation.Add xlValidateList, , , "=" & "CallStatus"
End With
End SubWHat I would like to do is be able to change the In Progress column (J) be Yes when work is being completed on that particular patient. When that task is complete, I will change the status in column I to something representing the work done ect. What I would like to happen is when a cell in column I is changed, I would like the corresponding cell in column J in the same row to be cleared. There should only be 1 cell with an in progress of Yes at a time.
Hope that makes more sense.
Re: Clear Cell based on change in another cell
OK, that's simpler than what was suggested in your original post. Perhaps something like this in the code module of the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Excel.Range
'// It will error if no cells in Col I changed
On Error GoTo Catch
'// Stop any changes made here firing the Event again
Application.EnableEvents = False
'// For each changed cell in Col I
For Each c In Intersect(Range("I:I"), Target)
'// If it now contains something then
If c.Value <> vbNullString Then
'// Blank cell in Col J, same row
c.Offset(, 1).Value = vbNullString
End If
Next
Catch:
'// Make sure event handling is turned on again
'// Critical. Excel stops responding to everything
'// if not reset.
Application.EnableEvents = True
End SubThis does not cover the "should only be 1 cell with an in progress of Yes at a time". It blanks the contents of a cell in Col J without checking exactly what has been entered in the column.
Re: Clear Cell based on change in another cell
That works perfectly! Thank you again for all of your help!
I do have one other question, is there a site where I can go to learn vba programming better? I would like to understand the uses for the different code statements and such, the more I can learn, the less I need to be a pest to you folks!
If you have any ideas, I would love to know.
Re: Clear Cell based on change in another cell
Sorry but I really don't know. That sort of site is not something I've used so I can't really suggest.
Don’t have an account yet? Register yourself now and be a part of our community!