Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Custom Userform to update sheet (Excel 2007)

    Hi,

    In the attachment is a sample user form that allows a user to select a supplier. I was helping someone to set this up to collect info about the supplier, but now the user also wants the ability to edit the details in the user form and have it update the cells in the sheet. Can I ask for advice to best get this done before I spend hours on designing code that may not be necessary. TX.

    PS: I informed the user about using the Data Form that is already built into Excel. They are aware of the form, but prefer to customise their own form as the Data Form is less easy to search and also provides deleting options they do not want available.

    Tx for any assistance
    Attached Files Attached Files
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    You could use code like this:

    Option Explicit ' why wasn't this present in your workbook?

    Private r As Long

    Private Sub btnOK_Click()
    If r > 0 Then
    Cells(r, 3) = Me.SupContact
    Cells(r, 4) = Me.SupTel
    End If
    Unload Me
    End Sub

    Private Sub SupName_Change()
    r = Application.Match(Me.SupName, Range("B1:B4"), 0)
    Me.SupContact = Cells(r, 3)
    Me.SupTel = Cells(r, 4)
    End Sub

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Userform to update sheet (Excel 2007)

    Hans,

    Thanks...it works great. Much appreciated!
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Userform to update sheet (Excel 2007)

    Hi,

    This user form has evolved further and now contains a few more combo boxes...ones with the option of Yes and No. I have a Userform_Initialize event that populates these combos with the Yes and No option...this works great.

    Problem...
    When I use the custom form to edit an existing record on the sheet, the Yes/No answers currently in the cells (for the record I am editing) change to the words "True". Then when the macro reads the values from the sheet to update the forms text and combo boxes... the value in the yes/no combos becomes 0 or -1. If I don't select the yes/no options...then it writes 0 or -1 into the cell and I loose the original Yes or No.

    What is happening?? Is there a way to force the combo to be seen as text so it does not convert the data to True/False or 0 and -1??

    Bottom Line: When I edit an existing record, it must read the data in the cells and input it into the form so I can edit details and have the form write the changes back to the sheet. All is great except the Yes/No combos that change the values to TRUE and 0/-1. This must not be! How do I fix this?

    Many tx.
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    In VBA, True = -1 and False = 0.
    In Excel, TRUE and FALSE are special values, equivalent to +1 and 0.

    Please attach a sample workbook.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Userform to update sheet (Excel 2007)

    Hans, tx for the reply. After my long initial post, I was testing things and it looks like my problem is resolved.

    I attached a txt with the code I recieved from someone, and notived that the code (which I commented out) was conflicting with the SupName_Change() macro. So the cells were being edited twice. When I removed the code it worked well.

    Tx
    Attached Files Attached Files
    Regards,
    Rudi

  7. #7
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    Hans:
    <hr>In VBA, True = -1

    <hr>
    As a matter of interest, why is that used in preference to 1 ?
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    My understanding is a Boolean string is stored as a 16-bit integer. FALSE is a string of all 0s and TRUE is a string of all 1s. so that NOT TRUE = FALSE and NOT FALSE = TRUE. The string of 16 1s has a value of -1

    I think the 0, -1 convention is the earlier one from the earlier computers

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    Storing -1 as a string of all 1's is a format known as 2's complement, and enabled simple addition and subtraction of binary numbers.

    StuartR

  10. #10
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    Thanks Steve (I think <img src=/S/confused.gif border=0 alt=confused width=15 height=20> )
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  11. #11
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Userform to update sheet (Excel 2007)

    Thanks for the link, Stuart.

    It gives me something to point to, next time someone asks me the question - even if I don't understand it myself!
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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