Concept only required

I tried MD but it tells me I've already posted the book in this thread. The earlier version doesn't have my latest work. Will try again.

Your textboxes are all individually named, so you can add across pages as you do within a page. Also, you nevere refer to EmploeeInduction within the form code, use Me.

Hi Ted,
I suggest you use linebreaks in your code as Bob showed earlier. It makes it much easier to follow what you're totalling
eg
[VBA]Private Sub txtgenpoints_Enter()
txtgenpoints.Text = CDbl(txtethics.Value) _
+ CDbl(txtreliability.Value) _
+ CDbl(txtcommunication.Value) _
+ CDbl(txtteamwork.Value) _
+ CDbl(txtohs.Value)
End Sub

[/VBA]

Consider it done Sire

Adding down each page of the multipage is working but trying to get the subtotals to add is becoming very confusing.

[vba]Private Sub txtReviewPoints_Click()
txtReviewPoints.Text = CDbl(txtGenPoints.Value)
+ CDbl(txtPrPoints.Value)
+ CDbl(txtHorPoints.Value)
+ CDbl(txtHosPoints.Value)
+ CDbl(txtIndPoints.Value)
+ CDbl(txtKillPoints.Value)
+ CDbl(txtAutPoints.Value)
End Sub
[/vba]
Follows the same principle being used to count the individual points on a single page within the multipage control. I've changed from a Click event to Change event but no totalling is occuring in the textbox txtReviewPoints on the Employee Details page of the multipage.

What have I done wrong?

The second part of the question for tonight is I wanted to apply a "A", "B","C","D","E" type grade to the total points for each subgroup and have it shown on the Employee Details page of the Multipage control. I tried to use the following but have a feeling that I should perhaps have used a Case Select code. For Example

[vba]Private Sub txtGeneralRating_Enter()
If txtGenPoints.Value > 32 Then
txtGeneralRating.Text = "A"
Else
If txtGenPoints.Value > 24 Then
txtGeneralRating.Text = "B"
Else
If txtGenPoints.Value >16 Then
txtGeneralRating = "C"
Else
If txtGenPoints.Value >8 Then
txtGeneralRating.Text = "D"
Else
If txtGenPoints.Value =< 8 Then
txtGeneralRating.Text = "E"
End If
End If
End If
End If
End IF
End Sub[/vba]
This sub Group General has a maximum points value of 40, where as all the other sub groups will have a maximum value of 50 points per group, with the breakdowns being 40, 30, 20, 10 and less than 10.

Could someone show me how I should have done this bit please?

Part 2 first
[VBA]Private Sub txtGeneralRating_Enter()
Select Case txtGenPoints.Value
Case Is > 32
txtGeneralRating.Text = "A"
Case Is > 24
txtGeneralRating.Text = "B"
Case Is > 16
txtGeneralRating.Text = "C"
Case Is > 8
txtGeneralRating.Text = "D"
Case Else
txtGeneralRating.Text = "E"
End Select
End Sub
[/VBA]

Ok. Thank you Malcolm. I'll adjust the code where necessary to fix this point.:friends:

Hi Ted,
Ignore my last post (but it does demonstrate the principles)
Firstly, you're missing the line breaks " _" in your code.
Second; Create two functions, one for each rating, Use the Change Event of the Total box to pass the total to the function, which should return A-E. Write this value to the Details page.
If you need help, let me know.

[VBA]Private Sub txtGenPoints_Change()
txtGeneralRating.Text = RateGen(txtGenPoints.Value)
End Sub

Function RateGen(Score As Double) As String
Select Case Score
Case Is > 32
RateGen = "A"
Case Is > 24
RateGen = "B"
Case Is > 16
RateGen = "C"
Case Is > 8
RateGen = "D"
Case Else
RateGen = "E"
End Select
End Function
[/VBA]

MD, I've just come from adjusting the code to your first example.... :motz2:

Okay I'll go back and have another crack at it. and thanks once again for your help.

Quote:

---

Originally Posted by mdmackillop

[vba]Private Sub txtGenPoints_Change()
txtGeneralRating.Text = RateGen(txtGenPoints.Value)
End Sub

Function RateGen(Score As Double) As String
Select Case Score
Case Is > 32
RateGen = "A"
Case Is > 24
RateGen = "B"
Case Is > 16
RateGen = "C"
Case Is > 8
RateGen = "D"
Case Else
RateGen = "E"
End Select
End Function
[/vba]

---

IN writing the two functions can i refer to one as RateGen1 and the other RateGen2?

Any other changes I need to make in doubling up the code?

Quote:

---

Originally Posted by Aussiebear

MD, I've just come from adjusting the code to your first example.... :motz2:

---

Sorry about that :mkay, but I think you'll find this way easier:yes

I'd probably go for RateOther as only one is General. That, and change the scores of course!

Righto..... I'll do that then I'm off to bed. It is after all 1:07am and I need lots of beauty sleep at my age.

and.. I didn't follow the second part of your suggestion in post 48. Could you leave me something to look at when I come back on line in the morning please?

Goodnight.:sleep2:

Malcolm has kindly tidied up my rather pathetic effort at coding. :friends: thank you for that.

I had origonally allowed for the form to load on opening of the workbook, but MD has placed a button on the page which does the same thing. If I use MD's button to load the form the combobox to select the name of the employee, and it works fine. If I come from the VBE environment and use the run button the combobox picks up the wrong data.

I've tried looking at the code but can't find out why it does this. Can someone give me some advice on how this can be overcome?

Secondly, I was looking at activating the calendar control so that when you click on the Review date textbox, up pops the calendar and allows the user to select the date, then once selected the value is written to the textbox.

I've searched for code examples and have foound the following

[vba]Private sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub[/vba]
Further down in the article it suggested that I needed to initialize the form with the following code

[vba]Private Sub Userform_Initialize()
IfIsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub
[/vba]
Where it talks about ActiveCell.Value, can I replace "ActiveCell.Value" with the name of the textbox as in "txtReviewDate.Value", so that it ties the calendar to the textbox?

I've left the calendar showing on the form so as to indicate where I hoped it would pop up when triggerred.

Ted,

I couldn't reproduce the button, VBE problem, both showed the same.

On the calendat, of course you can

[vba]

Private Sub Calendar1_Click()
Me.txtReviewDate.Text = Calendar1.Value
End Sub

Private Sub txtReviewDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(txtReviewDate.Text) Then
Calendar1.Value = DateValue(txtReviewDate.Text)
Else
Calendar1.Value = Date
End If
End Sub
[/vba]

Bob, Does the Calendar then close after a date is selected?

No, you have to make it visible/non-visible AFAIR.

Today's Date shows as 4/1/07 rather than 1/4/07. In the properties window the format is shown as 1/4/07. Do I have to reformat the date layout in the code within the sub to get this right?

Yes, VBA always works in US dates. What I tend to do is to declare a date variable and load that.

Hi Ted,
I didn't delete your open form code, I just added a button for use during the design process.
With regard to date; do you need the actual day date? It occurs to me that appraisals might be over a couple of days, and will only take place periodically, so Month/Year might be sufficient.
Before you progress too much further with detailed coding, you need to decide about writing/reading data from the spreadsheet, how you want it to appear, and how this is to be stored for successive evaluations. How does it cater for new employees etc.

Hmmm..... yet another wall to bust through. :banghead:


I had intended for the data to be stored in a flat file format as laid out on Sheet 2. Button on Form for "new" employees data to be entered.

Do I take it that because you have asked this question that you forsee some problems? I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an "Access" project.

Quote:

---

Originally Posted by mdmackillop

Hi Ted,
With regard to date; do you need the actual day date? It occurs to me that appraisals might be over a couple of days, and will only take place periodically, so Month/Year might be sufficient.

---

From what I've seen of the appraisals being conducted at work, they simply get a couple of the mid level management together and nut it out over a couple of hours. It often leads to the conclusion that if you happen to PO someone because of some incidental issue, you could go into an evaluation with several knives in your back. What you might call "Shot Duck Syndrome".

Quote:

---

Originally Posted by Aussiebear

Do I take it that because you have asked this question that you forsee some problems? I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an "Access" project.

---

Hi Ted,
No serious problems. I was thinking more about possible layouts subject to the form use. If you are only writing the data to store it, it might all be done in single rows. For presentation, printing, another format may be more suitable. I was really making the point that you should be clear about your needs before tackling the next stage.
Re Access, it could be done there, but a bit of a sledgehammer approach for this amount of data.

Quote:

---

Originally Posted by Aussiebear

I haven't thought to much about this side of it because it re- inforces the point that this is becoming very quickly an &quot;Access&quot; project.

---

Ted,

for what it is worth, IMO the only reason for an Access project over an Exceproject is because of a personal preference for Access (the obverse could be argued for Excel although I would argue not ).

This is using the application as the platform, which is not the same as the datastore. Here it makes little difference in concepts IMO, just depending upon volumes (and here I would prefer SQL Server over Access any day). It isn't that diificult to develop within Excel, and if well written, transfer of the data to Access or SQL server later is not that onerous.

The company would in my opinion, simply write their own version in Progress anyway. So all of this is just a learning tool for myself (and anybody else who might be trying to follow the build).

Can't say that I've actually had any experience with SQL Server, although I've probably used things with it and simply not recognised the process as such.


So getting back to the matters at hand.... Date formatting is simply a case of Format (DDate, "ddmmyy")?

Of that ilk, yes.

There's an old adage that people use when building a homebuilt aircraft. "90% built with 90% to go", and it seems it can easily apply to coding as well.

I've added two msgboxes, one to indicate if an employee has not been selected, and the second when there are currently no points to allocate.

I have failed to correctly format the date in the txtReviewdate textbox, and am unable to get the data in the form to write to the sheet. It clears the form correctly but doesn't write the data to any sheet let alone the "Employee Data" Sheet.

I think I shall leave the Calendar visible on the Employee details page as this doesn't overcrowd the page.

Could someone have a look please so I can wrap this thread up.

Desperate Aussiebear ( and Frustrated)

Part 1:
You forgot to set Tgt to anything.
[vba]Application.ScreenUpdating = False
On Error Resume Next
'ActiveSheet.Sheet2.Select
LRow = Sheets("Employee Data").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Tgt = Sheets(2).Cells(LRow, 1)
' Write data to sheet 2
[/vba] When it comes to clearing the form, your code works, but you must really like typing! Look at the PageTotal function for a methodology you might use for that.

Part 2:
[VBA]Private Sub Calendar1_Click()
Me.txtReviewDate = Format(Calendar1.Value, "dd/mm/yy")
End Sub
[/VBA]

and

[VBA]Tgt.Offset(, 1) = DateValue(Me.txtReviewDate.Text)
[/VBA]

[VBA] 'Clears Data from the Multipage control form once written to the sheet
Dim tb As Control
For Each tb In Me.Controls
If tb.Type = "MSForms.TextBox.1" Then
tb.Text = ""
End If
Next
[/VBA]

Will do..as soon as I finish standing in the corner.

As regards the methodology for writing data. You wrote the following

[VBA]' Get Total for a referred Page
Function PageTotal(Pg As Control)
Dim Crlt As Control, Tot As Long
'Look at each control on Pg.Contol
For Each Control in Pg.Control
'If Control Name Starts with txt then add it to the Total
If Left(Crlt.Name, 3) = "Txt" Then
'If Blank, Use 0 As value( prevents error)
If Ctrl = "" Then
Tot = Tot + 0
Else
Tot = Tot + Clng(Ctrl)
End If
End If
Next
'Pass value to the Function
PageTotal = Tot
End Function
[/VBA]

I'm therefore assuming I can write a function called
[VBA]Function WriteClearData (Pg As Control)[/VBA]

I need only the value of the cbo and txtreviewDate values from Multipage.page0 and then all the values of the controls which start with txt on all other pages. So i have a multiple select condition.

[VBA]'If PgNo.Value = "0" and left(Ctrl.Name , 3) = "cbo" then
LRow = Sheets("Employee Data").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set Tgt = Sheets(2).Cells(LRow, 1)
Tgt.Offset(, 0) = cboEmployee.text
[/VBA]

Then I have to go back and find the txtReviewDate.Value and prepare to add it too.

If Left(Ctrl.Name, 13) = "TxtReviewDate" Then... and so on

By the time I've done all this, I'm going to finish up with as much typing as I have with the origonal mess, won't I?

Sorry, was away creating mass confusion and didn't see your post.

Just for the future, if you store your textbox names in an array, it makes them more amenable to manipulation. You can write the data to the spreadsheet and also get data from the spreadsheet with a simple loop.
[VBA]Dim arr(5)
arr(0) = "cboEmployee"
arr(1) = "txtReviewDate"
arr(2) = "txtEthics"
arr(3) = "txtReliability"
arr(4) = "txtGenComms"
arr(5) = "txtGenTeamWork"

Tgt.Offset(, 0) = Me.Controls(arr(0)).Text
Tgt.Offset(, 1) = DateValue(Me.Controls(arr(1)).Text)
For i = 2 To 5
Tgt.Offset(, i) = Me.Controls(arr(i)).Text
Next
[/VBA]

or
[VBA] For i = 0 To 5
Me.Controls(arr(i)).Text = Tgt.Offset(, i)
Next
[/VBA]

MD, you've got me running back and forth, I don't know whether I'm Arthur or Martha..:bug:

I'll fix each component when i get back. Off fishing with youngest daughter and she's eventually arisen from her room. She's going to be a handful tomorrow when we need to be on the water by 5am.

Thanks for your help.

Hi Ted, I wouldn't rewrite what you have that's working, just a pointer for the future.

Quote:

---

Originally Posted by Aussiebear

Off fishing with youngest daughter

---

That's a much better thing to do than mess around with this VBA stuff. Hope you catch a big one!

So how did the fishing go?

Here's a possible spreadsheet layout for storage of data which can be written to and retrieved by the userform based on name and date. It also demonstrates changes in scores or whatever in a potentially useful display.
Let us know how you get on coding for this. Happy to assist as required.
Regards
Malcolm

On the water at the first crack of light, lines in, full of expectation, ...

Things looked good, fresh bait, new tackle, incoming tide, others yet to turn up


and we waited......



Shifted location after an hour......



Still nothing.....



Shifted again....... And again..... and again....



Eventually went back to the main bridge and fished under there to get onto some yellow tailed Dart.