Macro Control

[Bidwin]

I'm trying to learn VB using MSoft on-line help as a reference. I know, not the best in the world for folks who don't know VB. I'm running a binary search on a large file, and when I find a match I move several cells from a hidden sheet to an Excel spreadsheet-based form. The following statement kicks off a macro: "Private Sub Worksheet_Change(ByVal target As Excel.Range)". But when I move data from one sheet to another the macro runs with every move, and the search fails. I'd appreciate anyone telling me how to either move the data without running the macro each time data is moved, or how to condition running the macro when only one cell is changed. I could add a command button but I didn't want to create a another step for the users.
Thanks,
Bidwin

 

[Al Chara]

You can add an if statement that will check for a certain cell.

Something like:

Sub Worksheet_Change(ByVal target As Excel.Range)
If target.address = "$A$1" or target.address = "$B$1" then
msgbox "Cell " & target.address & " just changed"
end if
end sub

In the previous example, the code will only run if Cells A1 or B1 are changed.

 

[Bidwin]

Al,
I've overcome my prior problem of executing the macro only when a specific cell is changed by doing just what you suggest. I've got two sheets: a search/display sheet from which the macro is run, and a data file, which is searched and, if a match is found, from which data is moved to fields on the search/display sheet. But every time data is moved to a display field that causes the macro to run because the worksheet has changed. And, of course, the search fails. Is there any way to move data without the worksheet thinking it's been changed? If not I'll just have to put a command button on the screen which will execute the macro instead of the worksheet change doing that.
Thanks for your reply,
Bidwin

 

[Ivan F Moala]

On 2002-04-23 19:01, Bidwin wrote:
Al,
I've overcome my prior problem of executing the macro only when a specific cell is changed by doing just what you suggest. I've got two sheets: a search/display sheet from which the macro is run, and a data file, which is searched and, if a match is found, from which data is moved to fields on the search/display sheet. But every time data is moved to a display field that causes the macro to run because the worksheet has changed. And, of course, the search fails. Is there any way to move data without the worksheet thinking it's been changed? If not I'll just have to put a command button on the screen which will execute the macro instead of the worksheet change doing that.
Thanks for your reply,
Bidwin

Hi Bidwin
Use the Application.Enableevents and set this
to False before any changes are made to the sheet THEN set it back to TRUE when you have
finished. eg

<pre/>
Sub Worksheet_Change(ByVal target As Excel.Range)
Application.enableevents=false

do yuor changes

application.enableevents = true



End Sub