Updating Data - Userform

[koutsos1]

Hello,

I created a userform which takes the data that has been inputted and does calculations from a database and places the finished calculations in a new sheet. The data inputted and matched up with the database data through a VLoopup function. However, I want to change the data that is in the database. Is there a way to update the database and update the finished calculations without having to redo them?

Thanks

 

[BrianExcel]

What database are you accessing? How are you pulling the info, etc?

 

[koutsos1]

Hi Brian,

Sorry for the misunderstanding. The "databases" are all in excel, they are just tables. The data itself is being called using the VLookup function

 

[BrianExcel]

If you are working in VBA (and I assume you are since you are using a Userform) I would suggest using the offset property rather than the vlookup. vlookup is great for working in Excel directly, but it can get complicated when working in VBA since the formulas get so long.

Assuming you know VBA, what you can do is the following:

1. However you are pulling the data into the userform (a button, a drop down, etc.) you can identify that value as the "target"
2. Populate all other textboxes with offset values from the target (for example, if your location number 1234 was in cell A1, and the phone number for that store was in B1, you would use target.offset(0, 1) to specify that.
3. To change the data once it's in your form through steps 1 and 2, you can create a button that says "Edit" or "Make Changes" or whatever. When you select it, you basically reverse the code in steps 1 & 2, where rather than saying textbox1 = target and textbox2 = target.offset(0, 1), the edit button says target.offset(0, 1) = textbox2.

This may sound confusing, but once you see it in action it's actually quite simple. Feel free to send me an email address and I can send you an example workbook if you would like.

 

[koutsos1]

Ah ha, that makes sense. Thank you very much, I would not have thought doing it that way.