0

I am new to programming in Excel. I have done very little Visual Basic. What I would like to do is check a column on one sheet in Excel and compare all the values to a column in a different sheet in Excel. Now the problem is, is it possible that when i click on one of the cells it is "linked" to the other and takes me to the matching cell. I would like to have this implemented into the spread sheet and not be a "macro". If anyone can help it would be much appreciated.

Improve this question
3
  • If you don't want to implement this as a macro, why tag the question VBA? Commented Jul 29, 2009 at 15:40
  • Then apparently i have misunderstood. Can excel only be programmed to macros? As opposed to listening for an event? Commented Jul 29, 2009 at 15:44
  • excel has cells which may contain formulas. these are evaluated in a specific order. well and VBA can extend this behaviour in three ways: a) you create your own formula which returns a certain value, or b) you handle events that occur if e.g. a cell's content is modified or the selection has switched to another cell, or c) you create your own addin. this requires programming knowledge as well, cannot tell you much about the last option. Commented Sep 7, 2009 at 19:41

1 Answer 1

Reset to default
4

"macro" and "implementation in spread sheet" is nearly the same. the macro is, depending on how you do it, stored in the spreadsheet-file (.xls)

you can get the content of a cell by reading 

Range("A1").Value // Any cell-reference is valid here.

If you want to read an entire column, you need to use some kind of loop.

the linking you're talking about could be done with the event Worksheet.SelectionChange. e.g. following skeleton:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

you may now fill what should happen when the selection has changed. With Target.Column and Target.Row you may read the row and do the appropriate tests.

You can switch the View on a special Sheet using the Worksheet.Activate-Method which brings on top the worksheet you call the method on.

hope this clarifies a bit... if you need details, i will do some research...

regards

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

If i am correct a macro needs to be linked to a "ctrl + 'character'" or has to be ran manually? I would like excel to "listen" almost like a java action listener for an event to take place. so when i click on a specified cell it will automatically put me on the next sheet with the same value selected. As for the stubbed version of the function i will look into it. Thank you very much
the Worksheet_SelectionChange-Event fires when you change the selection on the current worksheet. you can optionally lay a macro onto a custom toolbar-icon and/or shortcut. however, the user must have either his macro-security set low enough OR accept to execute your macro if he wants to be asked for it.
alright, i actually am going to end up making a macro out of it because i am thinking if a row of cells is constantly being clicked on and then it is constantly being switched to the next sheet that is a lot of back and forth that has to be done. Thank you for your help.
if this helped you enough, you may accept this answer in order to close the question. otherwise you may come back and ask further... regards

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.