Running Macros on event

  • Excel 2003

    I have three macros:



    However I keep getting a circular reference error --

    Any help would be greatly appreciated...

    While using a scan tool once a scan code is recorded into a cell in column A I would like to have the three macros executed

  • Re: Running Macros on event


    Hi


    I'm presuming that the scan tool will trigger a worksheet change event. If so then try something like

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Column = 1 And Target.Cells.Count = 1 Then
        Cells(Target.Row, 2).FormulaR1C1 = "=IF(RC[-1] > 1,CONCATENATE(""*"",RC[-1],""*""),"""")"
        Cells(Target.Row, 3).FormulaR1C1 = "=IF(RC[-2] > 1, R[2]C[2],"""")"
        Cells(Target.Row, 4).FormulaR1C1 = "=IF(RC[-3] > 1, COUNTIF(C[-3],RC[-3]),"""")"
      End If
    End Sub



    Tony

  • Re: Running Macros on event


    Thanks Tony....


    That is correct - and thanks for the help!!!


    I am kind of running into the same problem though
    When the second IF statment is exceuted the value from where it is getting the information to copy is incrementing by 1.


    Code
    Cells(Target.Row, 3).FormulaR1C1 = "=IF(RC[-2] > 1, "THIS LOCATION MUST REMAIN CONSTANT or at LEAST GET THE VALUE FROM THE CELL ABOVE","""")"


    IF you see what I mean is that value is incrementing along with the other two cells which, for the other two is just fine - My Location name will be located in Cells A1:H1 which are merged hence, this is where I would like the second if statement to retrieve it's information from...


    Thanks again for all the help.... It must be that second statement which is giving Excel as myself, grief
    : D

  • Re: Running Macros on event


    Hi


    Can you put up a small sample file which shows a multiple instances of the formulas that you want to put into columns B,C and D. Not really sure what you want to increment / stay static.



    Tony

  • Re: Running Macros on event


    I need to protect this worksheet so that the folks using the scan tool don't accidentally (or on purpose even) delete the code. When I protect the worksheet allowing users to edit column A and the rest locked and hidden the worksheet code cannot execute.....


    Is there a way around this?

  • Re: Running Macros on event


    Hi


    You can unlock/unhide the sheet, make your changes, then relock/hide the sheet all in the code. Is that what you want to do? If you turn off the screenupdating, the user does not know what is happening behind the scenes.



    Tony

  • Re: Running Macros on event


    Well to get through the first problem regarding the Location information incrementing and missing the value placed in the cell, I labeled the row and referenced that by referencing to that column -1 row (I am doing this this way so no one has a heart attack about the code posting)


    To solve the problem of the "copy" of the original worksheet from becoming "unprotected" I used the ActiveSheet Protect UserInterfaceOnly in the macro used to open and copy the original..... that worked just ducky...


    I tried all the rest and unfortunately the "code" posted works fine on a static workbook but since most workbooks are dynamic the line added at the end of the macro works just great!!!!!


    Thanks though : D

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!