PDA

View Full Version : Concept only required



Aussiebear
03-19-2007, 02:38 AM
At work, our negiotations for a pay increase have gone pear shaped. So the Company has proposed that it will implement a pay scale based on performance criteria. Now I can live with this, in fact i except the challange of reaching the top performance level.

My question therefore is this:

Given that there is approximately 30 criteria over several different areas, should I design a multi tabbed form in which each individual is evaluated, and this form then records to a flat file or is there a more effecient method of recording and reviewing the data on each employee?

I'm not looking for any specific code right now, but would appreciate any ideas that I could follow up as a project. Oh BTW.. I want to do this in Excel only, if you don't mind.

Ted

Bob Phillips
03-19-2007, 04:47 AM
Ted, I don't think there is enough detail yet to offer any sensible advice. A form might be good, but it might be overkill. A worksheet table might be all that is needed.

Aussiebear
03-19-2007, 06:33 AM
Yep, you are right. Let me think about it a bit more.

Ken Puls
03-19-2007, 02:41 PM
I'm just ping'ing in on this, as I'd like to see how it develops. While I agree with Bob on the level of detail right now, I can't envision how you'd attach a userform aspect. I see tables for some reason. :)

mdmackillop
03-19-2007, 03:07 PM
Here's a simple userform which could show skills. weightings and performance with some calculation to produce an end result. (Don't try and make sense of the figures). By adding in some adjustment e.g.
If Forename = "Ted" then Result = Result * 1.3, I'm sure a satisfactory solution can be achieved.

Ken Puls
03-19-2007, 03:36 PM
Fair enough, Malcolm, but I'm trying to envision 30 measures in the uf...

mdmackillop
03-19-2007, 03:49 PM
Multipage, with one page for each "area" maybe.

Ken Puls
03-19-2007, 03:53 PM
Maybe...

Personally, I'll reserve judgement on the userform. It would certainly give a more 'sexy' feel, but I wonder if it would lose some needed functionality too. (Of course, that could be a good thing as well.)

It will be interesting to see how Ted's ideas sort of pan out. Actually, I'm most interested in seeing how the measures actually break down. :)

mdmackillop
03-19-2007, 04:03 PM
Just speculating, but it may be that with different "areas", different people make the assessments, in which case the relevant information only is presented.

Another thing to be considered in the design, Ted, is whether this will form an ongoing record, with successive years to be saved alongside.

Ken Puls
03-19-2007, 04:19 PM
Okay, so based on that last point by Malcolm about multiple years (which is a great consideration), I start thinking database. Why do you want to keep it all in Excel, Ted? I'm just curious here, and I'm not saying that it can't be done in Excel either. As long as we're all musing on this, though... :)

Aussiebear
03-20-2007, 07:04 AM
At this stage work is talking about employee reviews every three months. I've seen a prototype which was done in Excel and it was simply rows and rows of data. I thought of a multipage type form as a method of breaking down the entry of information into relevent areas.

A Stockperson for example would be evaluated on the following main points.
Pen riding, Induction, Kill, Hospital, Horse husbandry and General presentation.

Pen riding gets broken down into the following; Observation of stock, selection and cutting of stock, Recognition of illnesses, and team work. Induction includes Tagging, injections, mouthing, computer skills, team work etc. KIll would include Drafting of cattle, Counting, Identification of lines of cattle, Horse work, team work, Communications, Computer skills, Paper work etc. I won't bore you with the rest of the breakdowns. Up to four supervisors will be evaluating each employee in each of the areas and and average score worked out between them and entered against each of the 16 employees currently working as stockpersons.

The concept is being considered in Excel as most people at work can work with Excel easily. However a database would have the advantage of being able to track the performance of an Employee over a significant period of time, but then so too would a Workbook with one sheet per employee I guess.

I'm open to all ideas at this stage.

Bob Phillips
03-20-2007, 08:24 AM
Personally, even if using Excel as a datastore, I would never add one person per sheet, it makes life too difficult. I would capture the data. either form or a customised worksheet, store all data in a flat file type structure, and provide a viewing facility that gets the data from the flat file. You could use SQL or more conventional techniques to retrieve it, then format as required.

Aussiebear
03-20-2007, 12:28 PM
Fair enough. Does this mean I've got out of usng Access?

Bob Phillips
03-20-2007, 01:25 PM
Sounds like it to me.

Aussiebear
03-21-2007, 12:30 AM
Wooo Hooo!!!! :thumb

Aussiebear
03-22-2007, 05:20 PM
MD, Further to your suggestion of a multipage, can a multipage form be written to on each page and have just a single save command button, say on the main page?

I'm in the process of contsructing a multipage form and at this stage have just a simple Previous and Next command buttons for use once I've added data to each page.

Ken Puls
03-22-2007, 09:44 PM
Sure, Ted. Just put the button within the first page and it will be applicable to that page only. To use the buttons over and over again, place them on the form outside the multipage control.

:)

mdmackillop
03-23-2007, 01:24 AM
Hi Ted,
You can put contols on or off the pages

Aussiebear
03-23-2007, 03:29 AM
OKay I've got the basics "nearly right" as to the multipage form but am not having any success with the ability to move between pages. I've added Next and Previous buttons to each of the pages, in the belief that I could move between pages after the required data has been entered but this is not happening.

Could someone please suggest what I've done wrong, so I can correct the code.

mdmackillop
03-23-2007, 04:32 AM
Hi Ted,
Check your button names. eg Next on General is cboNext, I think it should be cbogenNext. Also your command for that name is missing _Click

JimmyTheHand
03-23-2007, 04:45 AM
Hi Ted :hi:

The code is almost OK, but page indices start with zero, so to display Page1 you need to set Multipage.Value to zero. Decrease the values by one E.g.
Private Sub btngenPrevious_Click()
'instad of Employeeinduction.MultiPage1.Value = 1
Employeeinduction.MultiPage1.Value = 0
End Sub (I made all necessary changes and attached the modified workbook.)

Also, you may want to consider a pair of buttons off the multipage that would take care of all page turnings. This, too, is included in the modified workbook.

Regards,

Jimmy

Bob Phillips
03-23-2007, 04:54 AM
Ted,

When you have Next/Previous buttons like this, they really should be off page and applicable across the board.

Like this

Aussiebear
03-23-2007, 05:17 AM
Well that's a bit dissappointing..... I wasn't even close.

Thanks Bob, I should have re read your previous post in the light of day when the brain is not so muddled.

Aussiebear
03-23-2007, 05:23 AM
Jimmy, what you have offered is great except..... In this thread, could you only supply either a direction for me to take or an example line of code. I need to learn to code rather than borrow a working section.

I do apprecieate your help, but I need to do the hard yards by myself where ever possible, even if it means I'm going round in circles for a few hours. (its what us bears do best)

Aussiebear
03-23-2007, 05:28 AM
Righto, next question. If I want to add the values of some textboxes, is it just a case of;

Textbox5.Text = Textbox1.value+Textbox2.Value+Textbox3.Value+Textbox4.Value

OBP
03-23-2007, 05:42 AM
Ted, if you need to go down the Access route then I will gladly help you put it together, or put it together for you.

Aussiebear
03-23-2007, 05:46 AM
uhoh...someone mentioned the "A" word.....:hide:

Bob Phillips
03-23-2007, 05:47 AM
uhoh...someone mentioned the "A" word.....:hide:

Ignore him Ted, I think he is lost (spiritually and group) :)

Bob Phillips
03-23-2007, 05:49 AM
Righto, next question. If I want to add the values of some textboxes, is it just a case of;

Textbox5.Text = Textbox1.value+Textbox2.Value+Textbox3.Value+Textbox4.Value

Best to cast it



Textbox5.Text = CDbl(Textbox1.value) + _
CDbl(Textbox2.Value) + _
CDbl(Textbox3.Value) + _
CDbl(Textbox4.Value)

Aussiebear
03-23-2007, 06:04 AM
"cdble" is a form of declaring the variant as a double?

Bob Phillips
03-23-2007, 06:30 AM
"cdble" is a form of declaring the variant as a double?

No, CDbl casts a value to a double, required as a textbox is a string.

Aussiebear
03-23-2007, 06:34 AM
If I write it as;

Private Sub txtgenpoints_Click()
Txtgenpoints= CDbl(txtethics.Value) + CDbl(txtreliability.Value) + CDbl(txtcommunication.Value) + CDbl(txtohs.Value)
End Sub


Nothing happens when the focus is shifted to textbox called txtgenpoints. If you change textboxes by using the TAB key is this the same as using the mouse and actually creating a click event?

Bob Phillips
03-23-2007, 06:38 AM
If I write it as;

Private Sub txtgenpoints_Click()
Txtgenpoints= CDbl(txtethics.Value) + CDbl(txtreliability.Value) + CDbl(txtcommunication.Value) + CDbl(txtohs.Value)
End Sub

Nothing happens when the focus is shifted to textbox called txtgenpoints. If you change textboxes by using the TAB key is this the same as using the mouse and actually creating a click event?

I would use the Change event.

Tab will trigger the Enter event, not Click.

Aussiebear
03-23-2007, 06:39 AM
I see my mistake. Someone take me outside and flog me with a heavy limb.


10 minutes later.... I no longer see my mistake and its still not working.

Bob Phillips
03-23-2007, 06:44 AM
I see my mistake. Someone take me outside and flog me with a heavy limb.

Sorry that is not included in the tuition fees, you will have to pay extra for such services.

Aussiebear
03-23-2007, 06:48 AM
Might be cheaper if I just take up drniking full time...:beerchug:

moa
03-23-2007, 07:07 AM
Could just use the Enter event.

Aussiebear
03-23-2007, 03:21 PM
We have a winner.... (be back in 5)

Aussiebear
03-23-2007, 04:26 PM
Okay can I add the total points values from each page of the multipage in the same format as you do for each individual page or do you need to identify each page?

mdmackillop
03-23-2007, 04:39 PM
Hi Ted,
Can you post your book? Guidance offered only - no solutions!

Aussiebear
03-23-2007, 05:15 PM
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.

Bob Phillips
03-23-2007, 05:29 PM
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.

mdmackillop
03-23-2007, 05:42 PM
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
Private Sub txtgenpoints_Enter()
txtgenpoints.Text = CDbl(txtethics.Value) _
+ CDbl(txtreliability.Value) _
+ CDbl(txtcommunication.Value) _
+ CDbl(txtteamwork.Value) _
+ CDbl(txtohs.Value)
End Sub

Aussiebear
03-23-2007, 07:23 PM
Consider it done Sire

Aussiebear
03-31-2007, 06:29 AM
Adding down each page of the multipage is working but trying to get the subtotals to add is becoming very confusing.

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

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

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
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?

mdmackillop
03-31-2007, 07:02 AM
Part 2 first
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

Aussiebear
03-31-2007, 07:15 AM
Ok. Thank you Malcolm. I'll adjust the code where necessary to fix this point.:friends:

mdmackillop
03-31-2007, 07:22 AM
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.

mdmackillop
03-31-2007, 07:28 AM
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

Aussiebear
03-31-2007, 07:46 AM
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.

Aussiebear
03-31-2007, 07:57 AM
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


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?

mdmackillop
03-31-2007, 07:58 AM
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

mdmackillop
03-31-2007, 08:01 AM
I'd probably go for RateOther as only one is General. That, and change the scores of course!

Aussiebear
03-31-2007, 08:09 AM
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?

mdmackillop
03-31-2007, 08:22 AM
Goodnight.:sleep2:

Aussiebear
04-01-2007, 01:00 AM
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

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

Private Sub Userform_Initialize()
IfIsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

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.

Bob Phillips
04-01-2007, 04:13 AM
Ted,

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

On the calendat, of course you can



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

Aussiebear
04-01-2007, 04:23 AM
Bob, Does the Calendar then close after a date is selected?

Bob Phillips
04-01-2007, 04:26 AM
No, you have to make it visible/non-visible AFAIR.

Aussiebear
04-01-2007, 04:41 AM
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?

Bob Phillips
04-01-2007, 05:02 AM
Yes, VBA always works in US dates. What I tend to do is to declare a date variable and load that.

mdmackillop
04-01-2007, 09:43 AM
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.

Aussiebear
04-01-2007, 01:43 PM
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.

Aussiebear
04-01-2007, 01:49 PM
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".

mdmackillop
04-01-2007, 02:28 PM
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.

Bob Phillips
04-01-2007, 02:56 PM
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.

Aussiebear
04-01-2007, 03:20 PM
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")?

Bob Phillips
04-01-2007, 03:24 PM
Of that ilk, yes.

Aussiebear
04-02-2007, 01:27 PM
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)

mdmackillop
04-02-2007, 01:48 PM
Part 1:
You forgot to set Tgt to anything.
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
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.

mdmackillop
04-02-2007, 02:07 PM
Part 2:
Private Sub Calendar1_Click()
Me.txtReviewDate = Format(Calendar1.Value, "dd/mm/yy")
End Sub


and

Tgt.Offset(, 1) = DateValue(Me.txtReviewDate.Text)

mdmackillop
04-02-2007, 02:39 PM
'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

Aussiebear
04-02-2007, 03:04 PM
Will do..as soon as I finish standing in the corner.

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

' 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


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

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.

'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


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?

Aussiebear
04-02-2007, 03:06 PM
Sorry, was away creating mass confusion and didn't see your post.

mdmackillop
04-02-2007, 03:09 PM
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.
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


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

Aussiebear
04-02-2007, 03:11 PM
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.

mdmackillop
04-02-2007, 03:13 PM
Hi Ted, I wouldn't rewrite what you have that's working, just a pointer for the future.

mdmackillop
04-02-2007, 03:16 PM
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!

mdmackillop
04-05-2007, 03:26 PM
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

Aussiebear
04-05-2007, 05:00 PM
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.

Aussiebear
04-05-2007, 05:42 PM
MD, I've got hold of a book called "Special Edition - Using Microsoft Office Excel 2003" by Patrick Blattner from Que Publishing. I think I'll spend Easter having a quick read of the sections dealing with Setting up a list or database in Excel.

mdmackillop
04-06-2007, 12:23 AM
Take it with you when you go fishing!!!

Aussiebear
04-06-2007, 02:09 PM
The book has plenty of flash methods ( the examples are really dressed up) but it unfortunately doesn't get me out of the problem of selecting the correct section if I follow your layout MD. I've simply changed the tgt.offsets to reflect the data now needs to be recorded down the column rather than across the row.

I have found an example of what I believe finds the correct section by looking at Column 2 for the value of the cboemployee selection.

Set tgt = .Cells(ActiveSheet.Column(2).Find(What:=cboEmployee.Value,Lookat:= xlWhole).Column, 1)


It is acceptable?

mdmackillop
04-06-2007, 02:29 PM
Hi Ted,
Can you post your latest version to keep me up to date with any changes.

Aussiebear
04-06-2007, 03:10 PM
as requested.

mdmackillop
04-06-2007, 05:09 PM
Add the following in the indicated position as a start for date entries. Note the change in the penultimate line.
With Sheets("Review Data")

' Write data to correct section based on employee name selected from cboEmpName

'Locate row containing employee name
Rw = .Columns(2).Find(What:=Me.cboEmployee.Value).Row
'Locate matching date from next row (Note Match type)
Col = Application.WorksheetFunction.Match(CDbl(CDate(Me.txtReviewDate.Text)), .Rows(Rw + 1), 1)
'Cater for no date
If Col = 0 Then Col = 2
'Set target cell for data entry
Set Tgt = .Cells(Rw, Col)
'Offset target for new date
If Tgt <> CDbl(CDate(Me.txtReviewDate.Text)) Then
Set Tgt = Tgt.Offset(, 1)
End If
End With
' The objective here is insert the word "Review" plus a number value in the cell immedeatly to the right of the name _
' selected in the cboemployee. Then all data required from the multipage control is entered below this

'Note DateValue in the next line
Tgt.Offset(1, 0) = DateValue(Me.txtReviewDate)
Tgt.Offset(2, 0) = Me.txtEthics.Text

geekgirlau
04-10-2007, 01:05 AM
I had a slow day today, so I thought I might have a play with your model.

The concept I was thinking of is to build the user form dynamically. What if you started with a list of questions, and had the macro create the pages on the fly? This would make it a lot more flexible and easy to maintain if you had to add or change questions.

Anyway, have a look at what I've put together if you're interested - I'm happy to go through this with you, or feel free to toss it on the "investigate later" pile if it doesn't suit.

Aussiebear
04-10-2007, 01:20 AM
I've got to admit it's got that database feel about it. Where does it go from here Geek? How does a marco create pages on the fly?

I'm not saying you are on a wiinner, but I'm certainly interested in looking at what you want to do with this.

Ted

Bob Phillips
04-10-2007, 01:43 AM
It is a good approach insofar as it allows you to maintain the criteria via a worksheet rather than the form, which is always good. You need to remember that controls created on the fly have no associated events (they can be created, but it is painful), so you cannot handle any input etc. For instance, you cannot stop a user inputting letters. This may or may not be a problem, in this case you have a natural checkpoint, the Calculate button, where you would have to check each textbox on the page.

One thing I would do is not display the evaluation factors alongside the input boxes, but rather use them as tooltips on the inputs, it is much slicker IMO, and saves space, so you could reduce the size of that enormous form.

Aussiebear
04-10-2007, 02:21 AM
Okay that sounds like great advice.

geekgirlau
04-10-2007, 05:18 PM
The concept is that you have a page (I've called it "Blank") that contains the controls you want on each of the group sheets. For each group, you then copy this page, rename the controls and set the captions for your labels as required.

You'll notice there's a new "Calculate" button so the event is no longer attached to the totals text box.

geekgirlau
04-10-2007, 11:29 PM
Hmmm ...

Another thought - combo boxes instead of text boxes to enter the score. In the Questions table you could list a minimum and maximum score for each question, which means you don't have to check each text box for valid entries.

Aussiebear
04-11-2007, 12:13 AM
Hmmmm........

Hmm..

H
m
m
m
.
.
.


Sorry can't yet picture what it is that you're telling me here.

geekgirlau
04-11-2007, 12:31 AM
Try this. I've replaced the text boxes with combo boxes, linked to a range that has values from 0 to 10. The combo boxes are set to limit acceptable values to those in the list, so if you type anything other than a value from 0 to 10 you get an error message.

I've also incorporated xld's suggestion to use the criteria description as the controltip text rather than have it displayed in a label.

The primary change to what you had developed previously is contained in the procedure BuildQuestions - it's commented, but I'm happy to go over it in more detail.

Bob Phillips
04-11-2007, 01:14 AM
Hmmm ...

Another thought - combo boxes instead of text boxes to enter the score. In the Questions table you could list a minimum and maximum score for each question, which means you don't have to check each text box for valid entries.

I thought about that (and even spinners), but it seems better to use textboxes and stop them inputting an invalid value.

geekgirlau
04-11-2007, 10:37 PM
The only problem with that is as you pointed out earlier, attaching an event dynamically to a control on a user form is a pain, not to mention the fact that the text box does not give any indication to the user what they're supposed to enter - the range of acceptable numeric values, or even the fact that it's expecting values.

Aussiebear
04-13-2007, 02:36 AM
Okay I've spent some time looking at this new direction, and have decided that this is not the way I wish to go. There are indeed some advantages with the layout that GG has offerred but for people with my experience in Excel, I'd be happier with a structured interface between the data stored and the user level.

I would like to take this opportunity to thank GG for her thoughts and the concept offered.

Bob Phillips
04-13-2007, 05:31 AM
What is the difficulty Ted. GG laid it all out for you, code and all,and you have to admit it is rather neat, eminently flexible, and more maintainable.

geekgirlau
04-15-2007, 01:42 AM
No worries, keep it in the pile of useful stuff to be investigated later.

One other thought I had is that it would be nice to view previous data via the form - have you thought about allowing the user to enter or select an employee and review date, and retrieve the values from that review?

Aussiebear
04-15-2007, 05:28 AM
Thank you Geek, I had wanted to explore the possibility of reviewing an individual's evaluation history but had intended to do so using another form.

At this stage I'm trying to construct a training course and notes for an OHS Committee representatives role and responcibilities lecture which I need to deliver this Wednesday afternoon. I would have liked the opportunity to spend some time on this issue but right now I'm distracted.

BTW I've added a number of pages and copied over the controls. Yet to rename the controls. Problem has arisen that the form EmployeeEvaluation will not load. Checked for spelling error and could not find one.

Will re-look at the issue later this week.

Ted

tccmdr
05-24-2007, 02:44 PM
Aussiebear,

I hope you don't mind me butting in, however, I'd be really interested in GG's ideas on viewing employee history: pray2:

geekgirlau
05-24-2007, 07:01 PM
Okay, a couple of thoughts spring to mind ...

Each time you record the results of an employee review, it would be helpful to have an additional range that just lists the employee name and the review date. You can then have a new page on the user form that has a combo box for the employee name (from the reviews recorded) and a list box showing all reviews recorded for that employee, which is populated each time up select an employee name.
Calling up the review basically requires that you reverse the process of recording the results in the first place. You would have to loop through the data in the Emp History sheet to grab all records matching the selected Employee and review date, and stick the score into the relevant combo box.
I would also recommend that all the controls be locked if displaying a previous review, so that the data can't be changed.Hope this is enough to get you thinking!

tccmdr
05-24-2007, 07:24 PM
GG,

Basic user here:doh:

I understand the setup, controls and processes but, unlike aussiebear, my trouble is with the coding:help

geekgirlau
05-28-2007, 07:08 PM
I've had a bit of a play with this, and I'm now leaning towards using a sheet to call up and print the results of the evaluation.

What I would suggest is that you have a protected sheet where the only cells the user can change are the name of the Employee and the review date (both of which would need to be combo boxes). You could then display each section, sub-section and question, and use a lookup to capture the score given for each question.

The History sheet might require a key reference field that can be used with VLookup - maybe concatenating the employee name, date and question number.

By the way bear, this would be WAAAAAAAYYYYY easier in Access IMHO ...

Aussiebear
05-29-2007, 01:00 AM
I understand the setup, controls and processes but, unlike aussiebear, my trouble is with the coding:help

That's not a mirror you're looking at mate, that me tearing my hair out because of the code as well.

I swear GG has done this just to torment me.:devil2:

Aussiebear
05-29-2007, 01:18 AM
By the way bear, this would be WAAAAAAAYYYYY easier in Access IMHO ...

:fright: Just when I thought it was safe to go in the water, you go and mention the "A" word....
I've spent months trying to dodge using Access and up till now I've sort of got away with it. :mkay (Say GG, you're not related to the PM are you?)

geekgirlau
05-29-2007, 05:12 PM
Come to the dark side Aussiebear :devil2:

You've probably noticed that my approach to this is very database-like anyway. Whilst you can do amazing database type stuff in Excel, when you're creating controls on the fly it can start to get a little hairy.

In terms of recalling the history, I think the simplest approach will be via a sheet, which also solves the potential issue whereby there is no easy way to print the results at this point. It needs a lookup from the History sheet by Employee name and date.

However bear, your mission (should you choose to accept it, and after the Excel version is working to your satisfaction) is to have a go at developing this in the dreaded "A". This is an excellent example to cut your teeth on - very few tables and forms, and you know the end result that you are working towards. There are lots of us here to give you pointers along your journey of discovery, and I think you might actually enjoy it!

Go on - is you a mouse or a bear? :boxer2:

Aussiebear
05-30-2007, 01:22 AM
Geez.... I've been challenged!!! :jawdown:


I've a small confession to make GG, I went to Access 2007 last night and its all sort of changed. Where does one make a start?

I mean its like being in a new town, so you think I'll just drop into the local bar for a couple whilst I pick up a few pionters as to where to go. Well after quite a few you step out outside and you have trouble rememebering which way it is to the car.