Consulting

Page 5 of 6 FirstFirst ... 3 4 5 6 LastLast
Results 81 to 100 of 108

Thread: Concept only required

  1. #81
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  2. #82
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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'

  3. #83
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  4. #84
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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'

  5. #85
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    as requested.
    Remember To Do the Following....
    Use 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

  6. #86
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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'

  7. #87
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  8. #88
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  9. #89
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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.

  10. #90
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    Okay that sounds like great advice.
    Remember To Do the Following....
    Use 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

  11. #91
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  12. #92
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  13. #93
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  14. #94
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  15. #95
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by geekgirlau
    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.

  16. #96
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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.

  17. #97
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  18. #98
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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.

  19. #99
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    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?

  20. #100
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •