I have recently started a new job. It is a management position and the company doesn't have much in the way of an I.T department. As such, I have been trying to create my own management tools. My colleagues are also scared of Access and do not want any involvement in anything I create through that. The reason I feel this is relevant to mention is because the most obvious solution to my current problem is 'just use Access' which I may end up having to do if it turns out I am out of my depth here.
One of the tools I am working on at the moment is a skills matrix/succession plan. On one worksheet I have a list of tasks that are required as per an employees job description and whether or not they require training.
I would like all user interaction to take place on a 'search' worksheet so that the 'back end' data is not manipulated at the source manually by anyone but me should the need arise. After running a search, a recorded macro copies, pastes and formats a set of Index Match formulas from another sheet to present the results. In this case, a list of employee's where training complete is 'No'.
From the results of this list, I would like the user to be able to select the cell they wish to update, and click a button to update the source cell from a 'No' to a 'Yes'. The below was my guess as to how to achieve this. I know nothing about VBA.
Sub updateyes()
SendKeys "(^[)"
Worksheets("Sheet1").Activate
ActiveCell.Value = "Yes"
Sheets("Search & Update").Select
Range("A6").Select
End Sub
The sendkey on it's own succeeds in moving to the source cell and selecting it. As a whole, the above code merely updates whatever cell was last selected manually on the 'back end' worksheet.
I cannot make the code reference a cell in particular as it depends on which cell is currently being selected by the user.
What i want to achieve in summary is this:
User selects cell to update, clicks button. Macro travels to source cell that is selected, updates from no to yes, returns to the 'search' worksheet.
Easy, no? From the responses I've already had, it seems I may be out of my league. Many thanks in advance.