Freeze one cell after data is entered

Hi guys

I hope you can help me with this. I searched the internet but can't find what i looking for.

I have an Excelfile where excel does an automatically calculation. But what i want is that when Excel entered an value in cell B3, this cell freeze so when i dubble click a random cell the value in b3 doesn't change.


I hope you can help me with this

thank you

Johan

please post the code in sheet module.

Hi Mana thanks for the fast response

Here is the code i used for the automatically calculation and i want when the value in B3 is entered this cell freezes:
Private Sub CommandButton4_Click()


For intCount = 15 To 150
Range("B2") = intCount
If Range("B4") = "Akkoord; voldoende steken" Then
unload Me
Exit Sub

End If
Next intCount

End Sub

thanks

Johan

Would you attach the excel file?

Hi Mana

i dont know what you exacly mean with post the code in sheet module.?

The question is when i press f9 it still changes the value i want to lock this cellafter data entry. and when i push button then it calculates again. But nog when i click random cell or press f9. I hope you can help me with this

Thanks

Johan

you can use the event procedures in sheet module.

Code:

---

Option Explicit

Private Sub Worksheet_Activate()


    Application.Calculation = xlCalculationManual
   
End Sub


Private Sub Worksheet_Deactivate()


    Application.Calculation = xlCalculationAutomatic
   
End Sub

---

Hi Mana thanks for the response. In this file if you dubble click a random cell or press f9 the value in B3 changes i want to freeze this when i press f9 or dubble click random cell thanks a lot

When do you want to calculate the B3 formula?

When i click on button than it calculates. I cant send this file because it is to big.

1)clear the B3 formula
2)then try this macro

Code:

---

Option Explicit

Private Sub CommandButton4_Click()
    Dim intCount As Long
   
    For intCount = 15 To 150
        Range("B2") = intCount
        Range("B3") = Evaluate("RANDBETWEEN(1,SUM(Sheet2!A1:A6)/Sheet1!B2)")
        If Range("B4") = "Akkoord; voldoende steken" Then
            Unload Me
            Exit Sub
        End If
    Next
   
End Sub

---

Hi Mana

Thank you for your help

But is it possible to just freeze a cell from vba so. Like if value is entered than stop refreshing until calculate button is clicked again ??

thanks Johan

B3 is not formula cell.
B3 value changes when only commandbutton4 is clicked.

Yes it's a formula cell and it changes the value when i click the button. Because the button is not directly llinked with cell B3

we don't like crossposting:

http://www.helpmij.nl/forum/showthre...voering-waarde

No, B3 is not formula cell.
Because,
1)clear the B3 formula
2)then try this macro


>we don't like crossposting:

Me neither!

Hi Mana

Thanks your very helpful

I tried your code. First i removed value in b3 then i pasted your code but when i click the button i get as result #value

Can you help me thanks

Johan

Code:

---

Option Explicit

Private Sub CommandButton4_Click()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim intCount As Long
    Dim s As Long, n As Long
   
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    s = WorksheetFunction.Sum(ws2.Range("A1:A6"))

    Randomize

    For intCount = 15 To 150
        ws1.Range("B2").Value = intCount
        n = Int(s / intCount)
        ws1.Range("B3").Value = Int(n * Rnd + 1)
        If ws1.Range("B4").Value = "Akkoord; voldoende steken" Then
            Unload Me
            Exit Sub
        End If
    Next
   
End Sub

---