Help !, problem updating data through userform !

[Pierre]

Hi,

I have a multipage on a userform.
On the multipage i have an number of textboxes (>20).
The contect of the textbox is looked up in a databasesheet ("datadga")
through this code;

Private Sub but_ophalen_Click()
Dim klantkeuze As String
klantkeuze = zoekwg.Value
Set klantmatrix = Worksheets("datadga").Range("B2:b500").Find(klantkeuze)
txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column)
txt_dganaam = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 1)

where:
klantkeuze is the result from a combobox called "zoekwg"

What is the problem ?
When the data is shown in the textboxes, i can overwrite it, but it will not
be written in the database sheet.
I know that, if you use controlsource, it will be overwritten, but in this
ase, bacause the data is pciked out of a database, i do not know how i can
get de database updated if someone changes de content of one of the
textboxes

Can anyone please help ?
Thanks,
Pierre

 

[Incidental]

Hi Pierre

I believe you would need to call a routine that will transfer the data
between the form and worksheet, this can be done by adding a save
button to your form or you could use it in the after update of the
textboxes, though if you have >20 textboxes you may want to look in to
a collection or something similar. The code below is how I would go
about doing this the only thing being is that I always use the default
name of textboxes as it is easier to iterate through them if they have
a standard name. I hope this code is of some use to you I have found
that it is a quick painless way to get the data from a userform onto
the worksheet.

Option Explicit
Dim ctrl As Control
Dim i As Integer
Dim CellRow As Integer

Sub SaveData()

CellRow = zoekwg.ListIndex + 2
'if you are loading the textboxes from the combobox you
'can figure out the row holding you data by adding 2 to
'zoekwg listindex, add 1 because the listindex starts at 0
'and add another 1 because you start on B2 not B1

For i = 1 To 5 '5 is the number of textboxes on the page
'this will start a loop through the controls

Set ctrl = UserForm1.MultiPage1.Pages(1).Controls("TextBox" & i)
'this will set each textbox in turn to the variable "Ctrl" this
'will allow you to minimize the code required to pass the data
'from you userform to the worksheet.

Cells(CellRow, i).Value = ctrl.Value
'here we pass the value of "Ctrl" to a cell this is done
'using the "CellRow" variable that holds the row number
'which will not change throughout the loop, however the
'column number will have to increase 1 each iteration
'of the loop, since we already have a variable that is
'going to iterate with the loop "i" we can use this to
'reference the next column to the right

Next 'Iterate the Loop

'zoekwg.RowSource = "B2:b22"
'if you have a textbox that will hold the same value as
'the combobox zeokwg which will allow the user to change
'it you may want to reload your combobox again to show
'the updated data

End Sub

The same style of code can be used to load the data into the form by
using the code below in the combobox change event

CellRow = zoekwg.ListIndex + 2

For i = 1 To 5

Set ctrl = UserForm1.MultiPage1.Pages(1).Controls("TextBox" & i)

ctrl.Value = Cells(CellRow, i).Value

Next

Hope it helps

Steve