Custom Userform to update sheet (Excel 2007)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Hans,

Thanks…it works great. Much appreciated!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hans:[indent]

---

In VBA, True = -1

---

[/indent] As a matter of interest, why is that used in preference to 1 ?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

Thanks Steve (I think )

Reply | Quote