PDA

View Full Version : sheetchange problem



tintin
01-04-2007, 11:33 AM
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.


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

lucas
01-04-2007, 11:58 AM
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
you also need to add an s to this line in the Sub headtossprob

head = 0

to

heads = 0

Bob Phillips
01-04-2007, 02:22 PM
you also need to add an s to this line in the Sub headtossprob

head = 0
to

heads = 0


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

lucas
01-04-2007, 03:39 PM
That's how I found it too...

tintin
01-04-2007, 05:07 PM
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.

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

Bob Phillips
01-04-2007, 05:43 PM
Your code seems somewhat awry. The workbook_SheetChange event goes in the Thisworkbook code module, and the signature is



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


You probably want worksheet_change



Private Sub Worksheet_Change(ByVal Target As Range)


which goes in the sheet code module.

tintin
01-04-2007, 06:45 PM
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
01-04-2007, 08:44 PM
Thats because you used a specific cell.....target cell(D5) that must change before anything happens....

tintin
01-05-2007, 07:56 AM
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
01-05-2007, 08:40 AM
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.