Run VBA-code, when cell A9 is filled.

[KlausW]

Hi
I am using an indirect formula to transfer data from different sheets, these will be dynamic, to the "Bestilling" sheet. When cell A9 is filled, a VBA code must run.
How to do it, anyone who can help?
Any help will be appreciated.
Best regards
Klaus W

 

[Herakles]

You can run code from the Worksheet Change event.

This example will ony run code when the cell is not empty.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$9" Then
        If Len(Target.Value) > 0 Then
            MsgBox "Run code here"
        End If
    End If
        
End Sub

 

[Joe4]

You can run code from the Worksheet Change event.

This example will ony run code when the cell is not empty.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Address = "$A$9" Then
        If Len(Target.Value) > 0 Then
            MsgBox "Run code here"
        End If
    End If
       
End Sub

If cell A9 is being filled by a formula, then using a "Worksheet_Change" event procedure on cell A9 will NOT work, as "Worksheet_Change" procedures are only triggering when the value in cell A9 is manually changed. It cannot detect changes brought about by a formula.

There is a "Worksheet_Calculate" event, which is fired anytime any cell on the sheet is re-calculated. However, the big drawback is that unlike "Worksheet_Change", it CANNOT detect which cell was re-calculated on the sheet, only that some cell somewhere on the sheet was re-calculated. So this event will fire any time any re-calculation on the sheet occurs.

Depending on the exact formula in cell A9, there may be another way around this. If one of the cells being referenced in the formula in cell A9 is being manually entered, and that is what is causing the change in A9 to happen, you can write a "Worksheet_Change" event procedure on those underlying cell values which are being manually updated.

 

[Herakles]

Without knowing how the transfer of data from different sheets is to occur it is difficult to offer a solution.

How is cell A9 to be filled 'dynamically' from another sheet if that is what you are doing?

 

[KlausW]

Hi, yes the data is being transferred from another sheet and it is dynamic. I get the sheet name from a dropdown menu. So when I scroll down to the name of the sheet, the data is transferred to cell A9, and the VBA code must run. The VBA code that runs here is for setting frames around the text. Hope it makes sense. KW

 

[Herakles]

Can you please submit the data transfer code using code tags.

 

[KlausW]

It's is a formula. I do not know where to put it. But here it is.

=IFERROR(IF(OR($M$1="";$A$3="");"";INDIRECT("'"&$M$1&"'!"&
ADDRESS(Row(3:3);COLUMN(A:A))));"")

KW

 

[Herakles]

What is in M1?

Where do you put this formula?

What does it return?

When you say 'setting frames around the text' do you mean a border and where is this text?

ADDRESS(Row(3:3);COLUMN(A:A)) needs to be ADDRESS(ROW(3:3),COLUMN(A:A)) with a comma between the row and column reference.

Row(3:3) will always be 3 and COLUMN(A:A) will always be 1.

 

[KlausW]

What is in M1?

Where do you put this formula?

What does it return?

When you say 'setting frames around the text' do you mean a border and where is this text?

ADDRESS(Row(3:3);COLUMN(A:A)) needs to be ADDRESS(ROW(3:3),COLUMN(A:A)) with a comma between the row and column reference.

Row(3:3) will always be 3 and COLUMN(A:A) will always be 1.

In M1 is the sheets name.

The formula is put in cell a9 and down to a100.

It returns a number from the sheets, whose name is in cell M1.

Yes, I mean a border, sorry.

I English U use comma between, but in Denmark we use semicolon.

Hope that will help.

KW