Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    UserForm / ComboBox (Win 98 XL2002)

    The code below is attached to the [FINISH] button on a userform and writes data to a database. There are two entries where the column in the database is determined by a choice made in a userform combobox. The combobox has 7 options. If there is already an entry in one of the 7 possible spaces, then the NEW entry should REPLACE the original. But this code will not REPLACE a previous entry , it simply adds another one.

    How can I force the code to clear a previous entry before posting another to the database?


    Private Sub ComFinish_Click()

    Application.ScreenUpdating = False
    Dim EditRow As Integer
    EditRow = Range("EditRow").Value - 0
    Sheets("Database").Select
    Range("b1").Select

    ActiveCell.Offset(EditRow, 0).Value = Val(TxtBoxCustCt)
    ActiveCell.Offset(EditRow, 1).Value = Val(TxtBoxNet)

    ActiveCell.Offset(EditRow, 2 + InfoForm.CBOPdOut1.ListIndex).Value = Val(TxtBoxPdOut1)
    ActiveCell.Offset(EditRow, 9 + InfoForm.CBOPdOut2.ListIndex).Value = Val(TxtBoxPdOut2)

    Unload Me

    Sheets("Calendar").Select
    Application.ScreenUpdating = True

    End Sub
    Attached Files Attached Files
    - Ricky

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Ricky,

    If you want just one value for each category, why have two columns for each. In your example sheet just populate columns D to J, and abandon the columns K to Q. Then insted of using the 9 + Offset in the second assignment, use 2 + Offset as you do in the first.

    Your code will overwite the existing value in a cell, but only if it is addressing the same cell. For example if youmake an entry for Fuel using the first combobox, the entry goes to column G. If you then make a second entry for Fuel using the second combo box the entry goes to column N.

    Reconsider the design of your data and the need for columns K to Q.

    Andrew

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    Andrew,

    We have to retain the ability for the user to enter multple daily paidouts, thus two sets of identical accounts. It is acceptable that the 1st paidout might be for fuel while the 2nd paidout could be for fuel as well.

    But there can only be ONE 1st paidout and ONE 2nd paidout

    So, in the database itself, there can only be one entryin columns K through Q for any given row. The sam goes for D through J.

    The problem is not having more than one value for each category (or Account), the problem is having more than one value for each paidout.

    Thanks,

    Ricky
    - Ricky

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Ricky,

    It's a little confusing . If you make an entry for Fuel in paidout1, (D to J), you cannot have an entry for postage ?, or if you do you must enter it in paidout2 (K to Q)? And if you do that you can have no other entries for anything else ? can you have a paidout1 for fuel and a paidout2 for fuel?

    If this is the case you need to include an indicator for each value as to whether it belongs in Paidout 1 or 2. If in 1 use offset 2 + Listindex, otherwise use 9 + Listindex. However I would recommend a redesign of you database, to simply record a Date, Code (for Fuel, Postage etc, and which could be tied to teh listindex, i.e codes 0 to 6). and amount. There should be no reason why you cannot have more than one record per day. Is it policy of your organisation to allow only 2 paidouts per day, or is that restriction imposed by the design of the database?

    Andrew

    Andrew

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    Andrew,

    The statements you make in your first paragraph are correct. "If you make an entry for Fuel in paidout1, (D to J), you cannot have an entry for postage ?, or if you do you must enter it in paidout2 (K to Q)? And if you do that you can have no other entries for anything else ?" Correct.
    " can you have a paidout1 for fuel and a paidout2 for fuel?" Yes, Correct.

    In the "real" program, there is actually FIVE paidouts allowed daily - the example here allows two for simplicity (if I can do 2, I can do 5!).

    Each paidout has to be dealt with individually as the entries will be used later on various forms and reports. That's why you may have two separate paidouts for fuel and they cannot be combined.

    If the user had a paidout for fuel $5.00 and another paid out for fuel $8.00, then that would be listed later on a daily report as two separate paid outs. It would not be correct to list a paidout for $13 as there wasn't one!

    I'm glad you can't get your hands around my neck! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    - Ricky

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Ricky,

    I still think a redisgn would be the best answer, but in th emeantime you will need code to check the sum of all entries in each paidout block, and if it exceeds zero do not allow another entry, e.g for the first paidouts, IFsum(DX:JX)> 0, do not allow another entry. Maybe a subtotal field for each paidout would help, and before allowing an entry check the subtotal value.

    Andrew

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Andrew: Looking at Ricky's first post, I don't think he wants to prevent an entry if one already exists, he want's to replace what is already there. Therefore, I think that all that is needed is to clear Dx:Jx before making a new entry in that range. Something like this, adjusted to get the proper row:

    <pre> Worksheets("Sheet1").Range("D3:J3").Value = Array("", "", "", "", "", "", "")
    </pre>

    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Legare,

    reading Ricky's post again I would say you are correct in your interpretation, but I would consider it a very unwise approach, as a legitimate payment could be overwritten by another, and hence the record lost. I would suggest that before a value is overwritten it should be deleted and such deletion recorded, and some sort of audit trail included.

    Andrew

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    Andrew / Legare,

    Consider this, please: The entries will only be used by store managers to generate reports. There's no "official" accounting being done from these entries. No audit trails, etc.

    But, I do want to prevent the manager from making a mistake - the users of the program are not computer whizzes!

    If a user opens the form for October 1st and makes the entries for sales, ticket count and two cash paidouts AND THEN reopens the form to edit the same day again - those previous entries would need to be present on the form.

    The user can then decide what needs to be edited. Example: maybe the 2nd paid out was for 20.00, not 2.00 or perhaps the paid out was for fuel, not office supplies.

    In the above example, the edited entry would need to replace the original. If the sales field were edited and the paidouts were unchanged, then clicking on the [FINISH] button would retain the original paidout entries.

    We certainly don't want to keep the user from editing the previous entries - that ability is a must. But one amount and one account per paid out!

    All of the advice is appreciated - it continues to be a learning process for me. Further, if design changes are necessary, so be it. I'm open for anything that works! Thanks much
    - Ricky

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    The following code works as desired but can it be simplified?
    It sets all values in the paidout fields to zero before posting newer entries.

    Before this can be truly effective, there needs to be code in the userform initialize that will display the paidout boxes and comboboxes with previous entries, if any.


    Private Sub ComFinish_Click()

    Application.ScreenUpdating = False
    Dim EditRow As Integer
    EditRow = Range("EditRow").Value - 0
    Sheets("Database").Select
    Range("b1").Select

    ActiveCell.Offset(EditRow, 0).Value = Val(TxtBoxCustCt)
    ActiveCell.Offset(EditRow, 1).Value = Val(TxtBoxNet)

    ActiveCell.Offset(EditRow, 2).Value = 0
    ActiveCell.Offset(EditRow, 3).Value = 0
    ActiveCell.Offset(EditRow, 4).Value = 0
    ActiveCell.Offset(EditRow, 5).Value = 0
    ActiveCell.Offset(EditRow, 6).Value = 0
    ActiveCell.Offset(EditRow, 7).Value = 0
    ActiveCell.Offset(EditRow, 8).Value = 0
    ActiveCell.Offset(EditRow, 9).Value = 0
    ActiveCell.Offset(EditRow, 10).Value = 0
    ActiveCell.Offset(EditRow, 11).Value = 0
    ActiveCell.Offset(EditRow, 12).Value = 0
    ActiveCell.Offset(EditRow, 13).Value = 0
    ActiveCell.Offset(EditRow, 14).Value = 0
    ActiveCell.Offset(EditRow, 15).Value = 0

    ActiveCell.Offset(EditRow, 2 + InfoForm.CBOPdOut1.ListIndex).Value = Val(TxtBoxPdOut1)
    ActiveCell.Offset(EditRow, 9 + InfoForm.CBOPdOut2.ListIndex).Value = Val(TxtBoxPdOut2)

    Unload Me

    Sheets("Calendar").Select
    Application.ScreenUpdating = True

    End Sub
    - Ricky

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    Can this be simplified into one line of code using "array"?
    I tinkered with:
    "Worksheets("Sheet1").Range("D3:J3").Value = Array("", "", "", "", "", "", "")
    for awhile but culd not figure out how to implement!

    ActiveCell.Offset(EditRow, 2).Value = 0
    ActiveCell.Offset(EditRow, 3).Value = 0
    ActiveCell.Offset(EditRow, 4).Value = 0
    ActiveCell.Offset(EditRow, 5).Value = 0
    ActiveCell.Offset(EditRow, 6).Value = 0
    ActiveCell.Offset(EditRow, 7).Value = 0
    ActiveCell.Offset(EditRow, 8).Value = 0
    ActiveCell.Offset(EditRow, 9).Value = 0
    ActiveCell.Offset(EditRow, 10).Value = 0
    ActiveCell.Offset(EditRow, 11).Value = 0
    ActiveCell.Offset(EditRow, 12).Value = 0
    ActiveCell.Offset(EditRow, 13).Value = 0
    ActiveCell.Offset(EditRow, 14).Value = 0
    ActiveCell.Offset(EditRow, 15).Value = 0
    - Ricky

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    <pre>Range("D3:J3") = Array(1, 2, 3, 4, 5, 6, 7)</pre>

    works, but if you just want to delete the contents of the cells, use
    <pre>Range("D3:J3").ClearContents</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    "Range("D3:J3").ClearContents"

    This assumes that the row is "3", which won't work. The contents being cleared or zeroed are part of a database where the rownumber changes and is determined by a particular date.

    ActiveCell.Offset(EditRow, 2).Value = 0

    In the above example, which works, "EditRow" is the row and the column is 2 - but I need to zero 2:15 without using separate lines of code for each!

    Sometimes, I confuse myself! Hope I haven't confused you.
    - Ricky

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: UserForm / ComboBox (Win 98 XL2002)

    Try <pre><big> Range("D" & EditRow" & ":J" & EditRow).ClearContents</big></pre>


    Andrew

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UserForm / ComboBox (Win 98 XL2002)

    I tried and it returned an error message the moment I clinked ENTER.

    Compile Error:
    Expected: List seperator or )

    This was highlighted in the code line as if to suggest the location of the problem:

    " & "
    - Ricky

Page 1 of 2 12 LastLast

Posting Permissions

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