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

[heidenman]

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!

[PetrH]

Hallo,
does the recalculation (F9) help? The original file would be helpful to understand you. Can you attach it?
Regards
Petr

[heidenman]

Sheet 2 E2 changes on the basis of what is given in E1. However only when input is given manually it seems, and this is my problem.

I need Sheet 2 E1 to pick up input from sheet 1 F13 and then E2 to change.

This in turn gives the ouptut back to a calculation in sheet 1 F14.

Thanks!

[Phil_V]

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:

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:

=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 )

[heidenman]

Works perfectly...

I am bowing in awe and respect.