We help IT Professionals succeed at work.

Editable user form list in Word VBA

Lawyer Luddite
Lawyer Luddite used Ask the Experts™
on
As part of a larger VBA project in Word (2016) , I want to present the user with a list of entries (say five columns and multiple lines) with data from an Access database.  This will be displayed in a WORD VBA User Form.  

I can populate the table fine and I can re save the entries to the database. What I need to know is how I can make the table/list editable so that  the user can correct or change the entries before the whole list is re saved (using a Save Command Button on the UserForm)

What I am doing is reading a temporary database table and displaying it and then re-saving it to the permanent table.

List boxes themselves do not appear to be editable in Word VBA?  How else can I achieve the same thing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Listboxes aren't editable.

What you could do is something like this.

1 User clicks item in listbox.

2 Item transferred to textbox on userform.

3 User changes value in textbox as required.

4 User clicks button to replace clicked item with updated value.
Lawyer Ludditesolicitor

Author

Commented:
That is a possible solution but not really what I wanted.   I wanted the row itself to appear editable.
The reason I wanted it this way is that the users existing solution has this facility (albeit a VB solution not Word VBA) and I want to minimise the changes imposed by this new program.

How easy would it be to superimpose a row of text boxes over the row in the list box when the user clicks to edit the row?  
This would give the appearance of an editable row.
Is there an easy way to work out the screen position of the row in question to set the position of the text boxes over the top?
Consultant and developer
Commented:
Unfortunately, you cannot superimpose a text box on a listbox in a user form, for some reason. The listbox is always in the foreground.

I think the only way to achieve what you want may be to create a "pseudo listbox" out of textboxes.

Attached is a demonstration macro-enabled word document, which works as follows:

There's a button to open the demo user form.

When the form is initialised, it creates an array of names of animals -- this would be your Access data. For demo purposes mine is a single-dimension array made from a hard-coded list of words, but there is no reason you could not populate an array with multiple columns, and create side-by-side textboxes for each row of data.

The form then creates 10 textboxes on the fly, names them "tb0" to "tb9", positions them one below the other, and populates them with the first ten words from my array.

In order to be able to respond to textbox events, it also makes use of a user-defined class module - tbClass - which consists of a text box and a "Change" event for the box. The code creates 10 instances of this class, and assigns each of the newly created textboxes to the "box" property of each class instance. It then adds the class object to a Collection object so they don't get lost.

The form also contains a rather basic "pseudo scrollbar" -- two buttons, for up and down. As you click the "down" or "up" buttons, the code adjusts which item from the array of animals appears in which box, giving the impression of scrolling. (You could also build a rather more sophisticated fake scrollbar, by using a collection of super-thin labels - also created on the fly - to emulate the effect of clicking somewhere in a scrollbar, rather than on just the up or down arrows).

When you change the text in a text box, the "change" event in the class module uses the name of the textbox to figure out where we are in the list, and alters the contents of the master array.

This means that you can then put the changed contents of the array back into Access whenever you like (the List property of a Listbox, after all, is secretly just an array).

Here's the code:
This is what's in the userform: consisting of some variables, a routine to respond to an up click and a downclick, and the initialisation routine which creates the textboxes, aligns them, populates them, adds them to the user-defined class and creates the collection.

Option Explicit
'marker integer for top of list
Public iTop As Long
'collection for textboxes
Dim collTB As Collection

Private Sub bDown_Click()
Dim i As Long
'scroll down

'don't scroll beyond list
If iTop < UBound(aText) - 9 Then iTop = iTop + 1
For i = 0 To 9
Me.Controls("tb" & i).Text = aText(i + iTop) 'start populating the ten boxes from wherever we are in the array, as designated by iTop
Next
End Sub

Private Sub bUp_Click()
Dim i As Long
'scroll up
'don't scroll beyond list
If iTop > 0 Then iTop = iTop - 1
For i = 0 To 9
Me.Controls("tb" & i).Text = aText(i + iTop)
Next

End Sub

Private Sub UserForm_Initialize()
Dim i As Long, sText As String
Dim oTB As MSForms.TextBox, cBox As tbClass 'user-defined class

'set up array of text
sText = "Dog,Cat,Goat,Mouse,Elephant,Crab,Lizard,Snake,Lion,Dolphin,Blue Whale,Rat,Porcupine"
aText = Split(sText, ",")
'set up collection to store textboxes
Set collTB = New Collection
'create and populate 10 boxes
For i = 0 To 9
Set cBox = New tbClass 'make new user-defined class object
Set oTB = Me.Controls.Add("Forms.TextBox.1", "tb" & i, True) 
oTB.Top = 25 + (i * 16)
oTB.Left = 20
oTB.Height = 18
If i <= UBound(aText) Then oTB.Text = aText(i) Else oTB.Text = "-"
Set cBox.box = oTB  'add the new textbox to the user class object
collTB.Add cBox 'add the class object to the collection of textboxes
Next
iTop = 0

End Sub

Open in new window


And this is the code from the class module
Option Explicit
'class module to handle box events on form
Public WithEvents box As MSForms.TextBox

Private Sub box_Change()
Dim iIndex As Long
'get index in array
iIndex = CLng(Replace(box.Name, "tb", "")) + uDemo.iTop
'replace text in master array
aText(iIndex) = box.Text

End Sub

Open in new window


There is also a regular code module, which contains only the definition of the array to hold the list, since you can't declare a "public" array in a userform, for reasons best known to Microsoft.

Hope this makes sense, and helps
Editable-Listbox.docm
Lawyer Ludditesolicitor

Author

Commented:
Thanks very much. That is probably the nearest I am going to get given what I wanted doesn't exist. I think I can work with that.
The only other option I came up with was to work with a table in a Word document rather than a User Form but that gives the user too many opportunities to go wrong as its just a document. This is a better solution. Thank you for your trouble.
Neil FlemingConsultant and developer

Commented:
My pleasure. Hope it works as you would like.