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.
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Take it with you when you go fishing!!!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
[VBA]Set tgt = .Cells(ActiveSheet.Column(2).Find(What:=cboEmployee.Value,Lookat:= xlWhole).Column, 1)
[/VBA]
It is acceptable?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Hi Ted,
Can you post your latest version to keep me up to date with any changes.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
as requested.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Add the following in the indicated position as a start for date entries. Note the change in the penultimate line.
[vba]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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
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
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
Okay that sounds like great advice.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
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.
Hmmmm........
Hmm..
H
m
m
m
.
.
.
Sorry can't yet picture what it is that you're telling me here.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
I thought about that (and even spinners), but it seems better to use textboxes and stop them inputting an invalid value.Originally Posted by geekgirlau
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.
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
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?
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
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
