PDA

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

mana
10-03-2016, 03:58 AM
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

mana
10-03-2016, 04:26 AM
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

mana
10-03-2016, 05:08 AM
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

mana
10-03-2016, 05:28 AM
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.

mana
10-03-2016, 05:49 AM
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

mana
10-03-2016, 06:47 AM
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

snb
10-03-2016, 06:51 AM
we don't like crossposting:

http://www.helpmij.nl/forum/showthread.php/901441-cel-bevriezen-na-invoering-waarde

mana
10-03-2016, 06:58 AM
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

mana
10-05-2016, 04:52 AM
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