Jan 11 2022 07:04 PM
Hello All,
Thank you in advance for any help you can provide me to point me in the right direction.
I have Hires (Cell D13) and Submissions (Cell D7), and I need to divide hires from submissions. The third cell (D16) is the Conversion Rate from the other two cells. I have searched google to solve this problem but only come up with deleting cells or splitting cells. Because this Excel DB is for the company I work for I need to have it Automated in VBA so nobody accidentally deletes a formula. I would like to have the conversion rate populate as the user enters the numbers into those two fields however, it can be done by button as well.
Again, thank you for any assistance you can provide me. You are much appreciated for what you do to help others.
Jan 11 2022 07:28 PM
Jan 11 2022 07:35 PM
Jan 11 2022 07:56 PM
@TimothySmith Don't know why you would need to give them the PW to unlock the sheet. You set the cells they need to 'touch' as unlocked and then all the formula cells get locked. If they have a problem they will have to have you fix it (which would be the same if there is a problem with the VBA).
If you still insist on VBA then add a _change routine onto the VBA sheet you want to automatically update. So in VBA (Alt-F11) you need to double click the sheet icon to open the macro page:
Then paste something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
.Range("D16").Value2 = .Range("D7") / .Range("D13")
End With
End Sub
you could get fancy and check what was updated and only make the change if one of the input cell changed and you could also do warnings or something if they try to enter a value into D16 ... But based on your request this is the 'answer' even though I still think locking the sheet is a much better solution. Just right click on cell D7, select Format Cells, go to the Protection tab and unclick 'Locked'. Repeat with D13 and any other cells/ranges you want them to actually enter data into. Then lock the sheet and you can even select options if you want them to be able to select those locked cell (but not change) or not even select, etc...
Either way, good luck.
Jan 11 2022 08:08 PM