0

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.

1
  • 2
    Can you please provide more detail what the problem is you are trying to solve with the code? It seems that using sendkeys would not be a good solution (it usually isn't) so if we know your entire problem, we may be able to help provide you with a better overall solution.
    – Eric F
    Oct 30, 2015 at 15:35

1 Answer 1

4

Your code is relying on very frail Select and Activate, on top of implicitly referencing the active workbook and active sheet.

Worksheets("Sheet1").Activate '~> implicitly refers to ActiveWorkbook
ActiveCell.Value = "Yes" '~> implicitly refers to ActiveSheet
Sheets("Search & Update").Select '~> implicitly refers to ActiveWorkbook
Range("A6").Select '~> implicitly refers to ActiveSheet

This will be your friend:

Dim sheetOne As Worksheet
Set sheetOne = Worksheets("Sheet1")

Dim searchSheet As Worksheet
Set searchSheet = Worksheets("Search & Update")

Now, instead of selecting and activating things, use these explicit references.

Instead of this:

ActiveCell.Value = "Yes"

Do this:

sheetOne.Range("A6").Value = "Yes"

(if that's what your code indends to be doing - it's not very clear what it's supposed to be doing)

2
  • That is somewhat where I was going to try and recommend to him as well.. Still very unclear as to what OP is trying to achieve but referencing solid calls instead of using send keys, selects, and activates is a much better approach.
    – Eric F
    Oct 30, 2015 at 18:41
  • Your answer has been useful as a tutorial on vba declarations. However, as detailed in my now edited question, I cannot reference a specific cell as it is reliant on what the source cell is of the cell the user has selected. Any further advice?
    – user515914
    Nov 3, 2015 at 15:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .