Hi Gonzo,
If you want to try something, muck around with the code pasted below.
Just create a userform with a list box (ListBox1), a text box (TextBox1) and
three command buttons (CommandButton1, CommandButton2 and CommandButton3).
Never mind the spacing and the size, my code places and sizes everything.
Basically, it loads an array in the listbox, user selects a row, row gets
transferred to the textbox, if user clicks in the textbox, the listbox is
disabled, user can make changes in the textbox, then either click on
"Accept" to change the array content and the listbox (and reactivate the
listbox), or click on "Cancel" to leave the Array alone and to reactivate
the listbox. Clicking on "Leave" at anytime, well, leaves the userform!.
Leave does not change the content of the array.
The difficult part with this system is that I needed to parse the text box
text into bits to recreate an array row. So I used the "|" as a delimiter.
If the user removes a "|", he is warned and has to start over. You could
avoid this by having 18 little textboxes. When a user click on a row, each
element from each column in the rows could be placed in its own text box,
then the user makes the changes and click on the Accept button to update the
array and the list box.
Good luck!
'_______________________________________
Dim MyIndex As Long
Dim MyArray(9, 17) As Variant
'_______________________________________
Private Sub CommandButton1_Click()
Me.Hide
Unload Me
End Sub
'_______________________________________
'_______________________________________
Private Sub CommandButton2_Click()
Dim MyString As String
Dim CheckString As String
Dim ToArray As String
Dim i As Long, x As Long, y As Long
CheckString = TextBox1.Text
Do While Not InStr(1, CheckString, "|") = 0
CheckString = Replace(CheckString, "|", "", 1, 1)
y = y + 1
Loop
If y < 17 Then
MsgBox "You have deleted a ""|"", please start again."
ListBox1_Click
Exit Sub
End If
'In my case I do not want spaces in the array...
MyString = Trim(Replace(TextBox1.Text, " ", ""))
For x = 0 To 17
i = InStr(1, MyString, "|")
If i = 0 Then
MyArray(MyIndex, x) = MyString
Else
MyArray(MyIndex, x) = Left(MyString, i - 1)
End If
MyString = Right(MyString, Len(MyString) - i)
Next x
ListBox1.Clear
ListBox1.List() = MyArray
ListBox1.Enabled = True
End Sub
'_______________________________________
'_______________________________________
Private Sub CommandButton3_Click()
ListBox1.Enabled = True
End Sub
'_______________________________________
'_______________________________________
Private Sub ListBox1_Click()
Dim MyString As String
Dim x As Long
MyIndex = ListBox1.ListIndex
For x = 0 To 17
MyString = MyString & ListBox1.List(MyIndex, x) & " | "
Next x
TextBox1.Text = Left(MyString, Len(MyString) - 3)
End Sub
'_______________________________________
'_______________________________________
Private Sub TextBox1_Enter()
ListBox1.Enabled = False
End Sub
'_______________________________________
'_______________________________________
Private Sub UserForm_Initialize()
Dim x As Long, y As Long
Dim ColW As String
Const BasicWidth As String = 30
Me.Width = (BasicWidth * 18) + 50
Me.Height = (BasicWidth * 5) + 18
For x = 0 To 9
For y = 0 To 17
MyArray(x, y) = x & "--" & y
Next y
Next x
CommandButton1.Left = (BasicWidth * 17)
CommandButton1.Top = (BasicWidth * 3.5)
CommandButton1.Width = BasicWidth * 2
CommandButton1.Height = (BasicWidth * 5) / 6
CommandButton1.Caption = "Leave"
CommandButton2.Left = (BasicWidth * 14.5)
CommandButton2.Top = (BasicWidth * 3.5)
CommandButton2.Width = BasicWidth * 2
CommandButton2.Height = (BasicWidth * 5) / 6
CommandButton2.Caption = "Accept"
CommandButton3.Left = (BasicWidth * 12)
CommandButton3.Top = (BasicWidth * 3.5)
CommandButton3.Width = BasicWidth * 2
CommandButton3.Height = (BasicWidth * 5) / 6
CommandButton3.Caption = "Cancel"
ListBox1.Width = (BasicWidth + 1) * 18
ListBox1.Height = BasicWidth * 1.5
ListBox1.Top = BasicWidth / 2
ListBox1.Left = BasicWidth / 2
TextBox1.Width = (BasicWidth + 1) * 18
TextBox1.Height = (BasicWidth * 3) / 5
TextBox1.Top = BasicWidth * 2.5
TextBox1.Left = BasicWidth / 2
ListBox1.ColumnCount = 18
ColW = BasicWidth
For x = 1 To 17
ColW = ColW & ";" & BasicWidth
Next x
ListBox1.ColumnWidths = ColW
ListBox1.List() = MyArray
End Sub
'_______________________________________
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site:
http://www.word.mvps.org