Excel enter formula into cell

Question

Function bd()

    Sheets("sheet1").Range("A1").value=1

End Function

This is my function. Why is it that when i enter =bd() into any cell in sheet1, the data in A1 does not change to 1? I do not want to use the button to change the value.

Answer 1

Why it does not work:

If you're calling your function from an Excel formula, your function becomes a User-Defined-Function (=UDF).

UDF have to follow special rules - the main one being that it cannot by any means change anything in Excel - it shall only return a value! Your function clearly violates this rules.

(For reference: the other main restriction is that it shall also not access any data outside the parameters that were passed to it when calling the function. Thus, you cannot use MyUDF=Range("A1").Value - but only something like MyUDF=rngParam1.Value*2!

Alternative approach:

If you want to change the worksheet but don't want to use a button, you need to think of some kind of trigger event. Look at the possible Worksheet and workbook events - you'll find a list of events here - and detailed instruction how to use them here.

For instance, you could use the Activate of Sheet1. For this place this code in the Sheet1 code module:

Private Sub Worksheet_Activate()
    Range("A1").Value = 1
End Sub

Now every time that sheet1 gets activated, A1 will be reset!

Answer 2

Try This

Copy this code on your 'Thisworkbook' on vba

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Range("A1").Value = "bd()" Then
Sheets("sheet1").Range("A1").Value = 1
End If
End Sub

---

Now if you enter 'bd()' (without Quotes) on cell A1 on Any Sheet and press enter the cell A1 of Sheet one will Change to 1

OR

Copy this code on your 'Thisworkbook' on vba

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("sheet1").Range("A1").Value = "bd()" Then
Sheets("sheet1").Range("A1").Value = 1
End If
End Sub

---

Now if you enter 'bd()' (without Quotes) on cell A1 on Sheet one and press enter the cell A1 of Sheet one will Auto Change to 1

Hope this works