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.
+4
A:
"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
Atmocreations
2009-07-29 15:49:22
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
2009-07-29 16:04:41
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.
Atmocreations
2009-07-29 16:28:44
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.
2009-07-29 17:46:34
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
Atmocreations
2009-07-29 20:58:15