sheetchange problem

[tintin]

Hi all,
Am pretty new to VBA programming so am teaching myself by writing different types of programs. My first major program is to calculate the proportion of heads that shows up in n (any integer) toss of a coin. I tried doing this by asking the user to enter the amount of tosses of the coin their desire in one cell, which is then passed to the macro to calculate, and return to another cell in the same sheet, the percentage of times heads shows up. The only problem, is that the macro is not calculating and outputting the numbers automatically as I enter them into the cell even though am using sheetchange.


[VBA]Private Sub workbook_sheetchange(ByVal Target As Range)

If Target.Worksheets("Coin Toss").Range("D5") Then
Call headtossprob
End If

End Sub

Sub headtossprob()
Dim ntoss, heads, i As Variant
Dim prob, hproportion As Double
ntoss = Worksheets("Coin Toss").Range("D5")
head = 0
Randomize
For i = 0 To ntoss
prob = Rnd
If prob > 0.5 Then
heads = heads + 1
End If
Next i
hproportion = heads / ntoss
Worksheets("Coin Toss").Range("D8") = hproportion
End Sub
[/VBA]

[lucas]

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D5" '<== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call headtossprob
End With
End If
End Sub[/vba]
you also need to add an s to this line in the Sub headtossprob
[vba]
head = 0
[/vba]
to
[vba]
heads = 0
[/vba]

[Bob Phillips]

you also need to add an s to this line in the Sub headtossprob
[vba]
head = 0
[/vba] to
[vba]
heads = 0
[/vba]

If you had Option Explicit at the start of the module, you would have been warned about that.

[lucas]

That's how I found it too...

[tintin]

thanks guys
i tried your recomendations but its still not doing what it want it to when i enter a number into the ntoss cell.

[VBA]Option Explicit
Private Sub workbook_sheetchange(ByVal Target As Range)
Const WS_RANGE As String = "D5"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call headtossprob
End With
End If
End Sub


Sub headtossprob()
Dim ntoss, heads, i As Variant
Dim prob, hproportion As Double
ntoss = Worksheets("Coin Toss").Range("D5")
heads = 0
Randomize
For i = 0 To ntoss
prob = Rnd
If prob > 0.5 Then
heads = heads + 1
End If
Next i
hproportion = heads / ntoss
Worksheets("Coin Toss").Range("D8") = hproportion
End Sub
[/VBA]

[Bob Phillips]

Your code seems somewhat awry. The workbook_SheetChange event goes in the Thisworkbook code module, and the signature is

[vba]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
[/vba]

You probably want worksheet_change

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
[/vba]

which goes in the sheet code module.

[tintin]

ok i think i got it but there's one little quirk. When i type in a number in the cell and press enter i get back a proportion, but when i press enter again i still get the same proportion.

[lucas]

Thats because you used a specific cell.....target cell(D5) that must change before anything happens....

[tintin]

Thanks guys, problem solved. Eventhough the the macro worked I rewrote it as a function. Maybe i should of done that in the first place, but by writing the macro i learned about some new things which is my main goal. thanks to everyone who responded to the post and the webmasters for keeping up this site.

[lucas]

Hi tintin,
If your happy with it please mark your thread solved using the thread tools at the top of the page. You can still post here after that if you have more questions on this subject at a later time.