Thread: Triggering value change in vba output on sheet two when input comes from sheet one

Hi,

I have a bit of code in sheet two of my document that requires user input to produce output. However I have the document set up in a vway that requires the actual input to be placed in a vell in sheet one, and the cell in sheet two is just "=cell in sheet one".

This causes the input cell in sheet two to change but the output cell does not produce the new answer.

Anyone know why?

Thanks in advance!

When the value changes on Ark2 it is changing as the result of a 'Calculate' not a 'Change', so you would need to use the Worksheet_Calculate event of Ark2, and adapt your code to fit it, as per below. This goes into the Ark2 sheet code:

Code:

Private Sub Worksheet_Calculate()
Const IN_CELL As String = "E1"
Const OUT_CELL As String = "E2"

With Me ' the sheet that called the 'Calculate' event
    .AutoFilterMode = False ' Remove any old filter
    .Cells(1, 1).AutoFilter field:=1, Criteria1:=.Range(IN_CELL).Value ' filter on the INPUT cell
    Application.EnableEvents = False
    ' Set the output cell to the minimum price
    .Range(OUT_CELL).Value = WorksheetFunction.Min(.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible))
    Application.EnableEvents = True
    .AutoFilterMode = False ' Remove the filter again
End With
End Sub

Incidently, E1 on the Ark2 sheet could have this forumla:

Code:

=CONCATENATE(Ark1!E13," ",Ark1!F13)

Then you wouldn't need the 'helper' cell in column G of Ark1 (and therefore wouldn't need to hide that column )