Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help !, problem updating data through userform !
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(klantk euze) txt_naamwg = Worksheets("datadga").Cells(klantmatrix.Row, klantmatrix.Column) txt_dganaam = Worksheets("datadga").Cells(klantmatrix.Row, klantmatrix.Column + 1) whe 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help !, problem updating data through userform !
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform to show and update data problem | Excel Programming | |||
VBA userform troubles... adding to database no problem....updating modifying info = :( | Excel Programming | |||
problem getting data from sheet into txt_boxes in userform | Excel Programming | |||
Userform updating | Excel Programming | |||
Excel VBA - Updating of data problem | Excel Programming |