Consulting

Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast
Results 61 to 80 of 108

Thread: Concept only required

  1. #61
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Yes, VBA always works in US dates. What I tend to do is to declare a date variable and load that.

  2. #62
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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.
    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. #63
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Hmmm..... yet another wall to bust through.


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

  4. #64
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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".
    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

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

  6. #66
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    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 "Access" 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.

  7. #67
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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")?
    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

  8. #68
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Of that ilk, yes.

  9. #69
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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)
    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

  10. #70
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    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.
    Last edited by mdmackillop; 04-02-2007 at 01:57 PM. Reason: Set Tgt row amended
    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'

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

  12. #72
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    [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]
    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'

  13. #73
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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?
    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

  14. #74
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    Sorry, was away creating mass confusion and didn't see your post.
    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

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

  16. #76
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    MD, you've got me running back and forth, I don't know whether I'm Arthur or Martha..

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

  17. #77
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Ted, I wouldn't rewrite what you have that's working, just a pointer for the future.
    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'

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

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

  20. #80
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,232
    Location
    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.
    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

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