Freeze one cell after data is entered

[johan4b]

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

[mana]

please post the code in sheet module.

[johan4b]

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

[mana]

Would you attach the excel file?

[johan4b]

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

[mana]

you can use the event procedures in sheet module.

Option Explicit

Private Sub Worksheet_Activate()


    Application.Calculation = xlCalculationManual
    
End Sub


Private Sub Worksheet_Deactivate()


    Application.Calculation = xlCalculationAutomatic
    
End Sub

[johan4b]

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

[mana]

When do you want to calculate the B3 formula?

[johan4b]

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

[mana]

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

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

[johan4b]

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

[mana]

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

[johan4b]

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

[snb]

we don't like crossposting:

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

[mana]

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


>we don't like crossposting:

Me neither!

[johan4b]

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

[mana]

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