Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / December 2007

Tip: Looking for answers? Try searching our database.

Help !, problem updating data through userform !

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pierre - 10 Dec 2007 10:18 GMT
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 - 10 Dec 2007 14:38 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.