VB Coding Problems

[Briggsey]

This is a plea to all people with VB experience out there! This is the situation i find myself in. I have been given an assignment which requires me to create a golf application and as part of it i am required to create a user form where the user can input their scores on each hole. What I want to do on that form is add each of the scores from the 18 holes up and have the total be displayed in a text box. I thought I had coded it properly but all that is coming up in the text box are the numbers which have been inputted. Can someone tell me what is wrong with the code below?

Private Sub lblcalculate_Click()
'is supposed to calculate the total scores of each hole
Scoresheet.txtfinalscore.Text = txthole1.Text + txthole2.Text + txthole3.Text + txthole4.Text + txthole5.Text + txthole6.Text + txthole7.Text + txthole8.Text + txthole9.Text + txthole10.Text + txthole11.Text + txthole12.Text + txthole13.Text + txthole14.Text + txthole15.Text + txthole16.Text + txthole17.Text + txthole18.Text
End Sub

Scoresheet.txtfinalscore is the text box where the total is supposed to appear
The individual txthole[].text are where the scores are being taken from

 

[embi]

txtbox.text is a string. Adding strings together concentates them, for eaxmple "Hello"+"Mary"="HelloMary". What you need to do is convert those strings to numbers. Luckily, there is a handy little function for this called Val().

The code you are looking for:

Scoresheet.txtfinalscore.Text = Val(txthole1.Text)+Val(txthole2.Text)+Val(txthole3.Text)+Val(txthole4.Text)+Val(txthole5.Text)+Val(txthole6.Text)+Val(txthole7.Text)+Val(txthole8.Text)+Val(txthole9.Text)+Val(txthole10.Text)+Val(txthole11.Text)+Val(txthole12.Text)+Val(txthole13.Text)+Val(txthole14.Text)+Val(txthole15.Text)+Val(txthole16.Text)+Val(txthole17.Text)+Val(txthole18.Text)

Hope that helps - let me know how it goes.
I'm sure theres a much quicker way of doing it but i'm too tired.

 

[Briggsey]

Thats spot on mate just what I was looking for! Cheers I owe you one big time!

Okay the next problem is that from that score which has just been calculated, I want that to display a '+' or '-' score from it as in terms with real golf scorecards.

Ive done an if statement for it, and its quite long, I may be going wrong in the fact that i am trying to get one button to do two things at once, but ill put the code up anyway and see if you can spot a problem with it.

Private Sub lblcalculate_Click()
'is supposed to calculate the total scores of each hole
Scoresheet.txtfinalscore.Text = Val(txthole1.Text) + Val(txthole2.Text) + Val(txthole3.Text) + Val(txthole4.Text) + Val(txthole5.Text) + Val(txthole6.Text) + Val(txthole7.Text) + Val(txthole8.Text) + Val(txthole9.Text) + Val(txthole10.Text) + Val(txthole11.Text) + Val(txthole12.Text) + Val(txthole13.Text) + Val(txthole14.Text) + Val(txthole15.Text) + Val(txthole16.Text) + Val(txthole17.Text) + Val(txthole18.Text)

'total par of course is 72 so
'72=E, 71=-1, 70=-2, 69=-3, 68=-4, 67=-5, 66=-6, 65=-7, 64=-8, 63=-9, 62=-10, 61=-11
'73=+1, 74=+2, 75=+3, 76=+4, 77=+5, 78=+6, 79=+7, 80=+8, 81=+9, 82=+10, 83=+11
If txtfinalscore.Text = 72 Then
txtoverall.Text = E
ElseIf txtfinalscore.Text = 71 Then
txtoverall.Text = -1
ElseIf txtfinalscore.Text = 70 Then
txtoverall.Text = -2
ElseIf txtfinalscore.Text = 69 Then
txtoverall.Text = -3
ElseIf txtfinalscore.Text = 68 Then
txtoverall.Text = -4
ElseIf txtfinalscore.Text = 67 Then
txtoverall.Text = -5
ElseIf txtfinalscore.Text = 66 Then
txtoverall.Text = -6
ElseIf txtfinalscore.Text = 65 Then
txtoverall.Text = -7
ElseIf txtfinalscore.Text = 64 Then
txtoverall.Text = -8
ElseIf txtfinalscore.Text = 63 Then
txtoverall.Text = -9
ElseIf txtfinalscore.Text = 62 Then
txtoverall.Text = -10
ElseIf txtfinalscore.Text = 61 Then
txtoverall.Text = -11
ElseIf txtfinalscore.Text = 73 Then
txtoverall.Text = "+" & 1
ElseIf txtfinalscore.Text = 74 Then
txtoverall.Text = "+" & 2
ElseIf txtfinalscore.Text = 75 Then
txtoverall.Text = "+" & 3
ElseIf txtfinalscore.Text = 76 Then
txtoverall.Text = "+" & 4
ElseIf txtfinalscore.Text = 77 Then
txtoverall.Text = "+" & 5
ElseIf txtfinalscore.Text = 78 Then
txtoverall.Text = "+" & 6
ElseIf txtfinalscore.Text = 79 Then
txtoverall.Text = "+" & 7
ElseIf txtfinalscore.Text = 80 Then
txtoverall.Text = "+" & 8
ElseIf txtfinalscore.Text = 81 Then
txtoverall.Text = "+" & 9
ElseIf txtfinalscore.Text = 82 Then
txtoverall.Text = "+" & 10
ElseIf txtfinalscore.Text = 83 Then
txtoverall.Text = "+" & 11
End If

End Sub

 

[embi]

Whoa.

First store the par in a variable so its easier to edit:
Dim par as Integer = 72
Now you simply calculate the difference between the score and the par.
textoverall.text=txtfinalscore.text-par
That method won't give you a + or - though, so:
textoverall.text=iif(txtfinalscore.text-par>0,"+"&val(txtfinalscore.text)-par,"-"&val(txtfinalscore.text)-par)

Dim par as Integer = 72
textoverall.text=iif(txtfinalscore.text-par>0,"+"&val(txtfinalscore.text)-par,"-"&val(txtfinalscore.text)-par)

 

[barmyarmy]

Can't disagree with any of that. I'd also use case rather than if in that situation or use for and next to populate the whole card,

 

[embi]

I was gonna try and work it out using for, but i was too tired to work it out. looking back, i'm impressed with what i managed (i did have cowburner nagging me for a game of mk)

 

[Briggsey]

Excellent stuff embi, remember though for when you read this, i need a statement to make it so that when the total score matches the total par, it displays 'E'

 

[embi]

Oops, forgot about that.

textoverall.text=iif(txtfinalscore.text=0,"E",iif(txtfinalscore.text-par>0,"+"&val(txtfinalscore.text)-par,"-"&val(txtfinalscore.text)-par))

That should do the trick, i just nested another iif in. Hope you dont have any more problems, its starting to look a little messy...

 

[Briggsey]

Thanks mate thats brilliant.

Does anyone know anything about how to save or store worksheets somewhere and being able to load them again from within the same workbook?

 

[das_vicky]

there are many ways, depends on your needs, u can text file, database, INI as storafe or registry. Can yuo specify what are your needs?

 

[Briggsey]

there are many ways, depends on your needs, u can text file, database, INI as storafe or registry. Can yuo specify what are your needs?

Im not quite sure I understand what your saying? :help

Basically, this golf spreadsheet requires me to be able to save a worksheet called "Leaderboard" under the name of a date and then from another worksheet called "Matches" i need to be able to look up that date from a drop down list and select it which in turn will show the leaderboard from that date.

Its very complicated especially when we havent even been told how to do it, me and my mates on the course dont think its first year material at all.

 

[embi]

Do you mean hide the worksheets?

 

[Briggsey]

Do you mean hide the worksheets?

Basically yes, the worksheet needs to be saved somewhere within that workbook but hidden, and from there it needs to be called up and shown when the user selects its date from the drop down list.

A replica of the original worksheet then needs to take its place on the original worksheet, so basically it would be like making mulitple copies of the same worksheet but only showing one which would be blank until the date is put onto it.

 

[embi]

Ok, that method has beaten me already. That would need some hard thinking. The easiest(and probably best) thing to do would be to think of some other way of storing and retrieving this data. What does the brief say you have to do?

 

[Briggsey]

exactly what ive said.

The user needs to be able to look at past leaderboards which should be saved within the workbook and be easily viewable for the user by looking through a list of dates.