View Full Version : Freeze one cell after data is entered
johan4b
10-03-2016, 03:14 AM
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.
johan4b
10-03-2016, 04:01 AM
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?
johan4b
10-03-2016, 04:29 AM
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.
Option Explicit
Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub
Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub
johan4b
10-03-2016, 05:18 AM
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?
johan4b
10-03-2016, 05:31 AM
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
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
10-03-2016, 06:20 AM
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.
johan4b
10-03-2016, 06:49 AM
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/showthread.php/901441-cel-bevriezen-na-invoering-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!
johan4b
10-05-2016, 03:18 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.