Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro Control

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Sub Worksheet_Change(ByVal target As Excel.Range)
    Application.enableevents=false

    do yuor changes

    application.enableevents = true



    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •