Long MsgBox -- need to display in 2 paragraphs

I

iris

Hi Graham.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub
 
I

iris

Hi Doug.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub
 
D

Doug Robbins - Word MVP

You might look at:

The following pages of fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

iris said:
Hi Greg.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE
THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub

Greg Maxey said:
Iris,

Yes. There is a 10 column limit when using an unbounded source.
Consider
the following code run with two comboboxes each set with a column count =
12:

Private Sub UserForm_Initialize()
Dim myArray(11, 11) As String
Dim i As Long
Dim j As Long
With Me.ComboBox1
.AddItem
For i = 0 To 12
On Error GoTo Err_Handler
.Column(i, 0) = "Column" & i & " Test"
Next i
End With
Err_ReEntry:
For j = 0 To 11
For i = 0 To 11
myArray(j, i) = "Row" & i + 1 & " Column" & j + 1
Next i
Next j
Me.ComboBox2.Column = myArray
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_ReEntry
End Sub

ComboBox1 throws the error. ComboBox2 is populated with 12 rows and 12
columns.
 
D

Doug Robbins - Word MVP

That makes six times

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

iris said:
Hi Graham.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE
THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub

Greg Maxey said:
Iris,

Yes. There is a 10 column limit when using an unbounded source.
Consider
the following code run with two comboboxes each set with a column count =
12:

Private Sub UserForm_Initialize()
Dim myArray(11, 11) As String
Dim i As Long
Dim j As Long
With Me.ComboBox1
.AddItem
For i = 0 To 12
On Error GoTo Err_Handler
.Column(i, 0) = "Column" & i & " Test"
Next i
End With
Err_ReEntry:
For j = 0 To 11
For i = 0 To 11
myArray(j, i) = "Row" & i + 1 & " Column" & j + 1
Next i
Next j
Me.ComboBox2.Column = myArray
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_ReEntry
End Sub

ComboBox1 throws the error. ComboBox2 is populated with 12 rows and 12
columns.

Graham Mayor said:
To use Chr(13) in Greg's macro use it in place of vbCr eg

MsgBox "Aaaaaaaaaaa." _
& Chr(13) & Chr(13) _
& "Bbbbbbbbbbbbb", _
vbInformation, "MESSAGE FROM xxx"

The result is the same.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
D

Doug Robbins - Word MVP

And that's five

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

iris said:
Hi Doug.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE
THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub

Greg Maxey said:
Iris,

Yes. There is a 10 column limit when using an unbounded source.
Consider
the following code run with two comboboxes each set with a column count =
12:

Private Sub UserForm_Initialize()
Dim myArray(11, 11) As String
Dim i As Long
Dim j As Long
With Me.ComboBox1
.AddItem
For i = 0 To 12
On Error GoTo Err_Handler
.Column(i, 0) = "Column" & i & " Test"
Next i
End With
Err_ReEntry:
For j = 0 To 11
For i = 0 To 11
myArray(j, i) = "Row" & i + 1 & " Column" & j + 1
Next i
Next j
Me.ComboBox2.Column = myArray
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_ReEntry
End Sub

ComboBox1 throws the error. ComboBox2 is populated with 12 rows and 12
columns.

Doug Robbins - Word MVP said:
Listbox Column Headings (Titles) cannot be populated using VBA.

If the source of the data is an Excel Spreadsheet, and the RowSource
property of the List Box starts one row below the row that contains the
titles, then the information from the row containing the tiles will
appear
as the column headings.

See: http:/support.microsoft.com/kb/q165494/

AFAIK that is the only way to populate column headings.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
I

iris

Thanks Doug,

I studied those articles, but they show how to populate a listbox

Doug Robbins - Word MVP said:
You might look at:

The following pages of fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

iris said:
Hi Greg.

I have a problem with building an array for a listbox. I hope you can help
me....

I have a userform in word that feeds from an access database.

here is the code:

Private Sub CommandButton50_Click()
Dim dbDatabase As Database
Dim rse As Recordset
Dim e As Integer
Dim i As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rse = dbDatabase.OpenRecordset("SELECT * FROM expressions ;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
i = 0
e = 0

If TextBox1.Text = "" Then
MsgBox "Enter a search value", vbOKOnly + vbExclamation
d = True
Else

If OptionButton1.Value = False Then

MsgBox "Choose a search field", vbOKOnly + vbExclamation
d = True

Else
If OptionButton1.Value = True Then

***** MY PROBLEM STARTS HERE.... HOW DO i BUILD THE ARRAY TO POPULATE
THE
LISTBOX? *****

ListBox1.Clear

With rse
Do Until .EOF
eStr = ![heb]
If InStr(![heb], TextBox1.Text) > 0 Then
d = True
i = i + 1
ListBox1.AddItem (i)
ListBox1.ColumnCount = 7
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;1 in;1 in;1 in;1 in;1 in;0 in;"
ListBox1.Column(6, i) = ![hebrt]
ListBox1.Column(5, i) = ![heb]
ListBox1.Column(4, i) = ![engRT]
ListBox1.Column(3, i) = ![eng]
ListBox1.Column(2, i) = ![employeeName]
ListBox1.Column(1, i) = ![lastUpdate]
ListBox1.Column(0, i) = ![hebshort]

End If
.MoveNext
e = e + 1
Loop
End With
End If
End If
End If

If d = False Then
MsgBox "no values were found", vbOKOnly + vbExclamation
End If

rse.Close
dbDatabase.Close
End Sub

Greg Maxey said:
Iris,

Yes. There is a 10 column limit when using an unbounded source.
Consider
the following code run with two comboboxes each set with a column count =
12:

Private Sub UserForm_Initialize()
Dim myArray(11, 11) As String
Dim i As Long
Dim j As Long
With Me.ComboBox1
.AddItem
For i = 0 To 12
On Error GoTo Err_Handler
.Column(i, 0) = "Column" & i & " Test"
Next i
End With
Err_ReEntry:
For j = 0 To 11
For i = 0 To 11
myArray(j, i) = "Row" & i + 1 & " Column" & j + 1
Next i
Next j
Me.ComboBox2.Column = myArray
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Err_ReEntry
End Sub

ComboBox1 throws the error. ComboBox2 is populated with 12 rows and 12
columns.



iris wrote:
Hi Greg

I'm trying to send you messages and failing everytime...

I will post my question here and hope you will recieve it...


I was refered to your site to see your solution to populating
auserform combobox in WORD from an access table, I used a code
simillar to yours (populating a listbox) - insted of listbox I
populated a combobox - but there is a problem there I can't solve...

It works OK with

ComboBox1.Column(0, i) = rsi.Fields("hebMakor")
to
ComboBox1.Column(9, i) = rsi.Fields("chiled")

but when it gets to

ComboBox1.Column(10, i) = rsi.Fields("mm")

it encounters error 380

"could not get the column property. Invalid property array index"

and for the lyfe of me!!!! I can't understand this!

Is there a limit on the columns number?

I tried to send you the question via your site and got an answer that

Delivery to the following recipients failed.

(e-mail address removed)

Can you Pleeeeaas!!!! I'm desperate!


:

Hopefully line breaks on your end won't wreck this:

Sub ScratchMacro()
MsgBox "Aaaaaaaaaaa." _
& vbCr & vbCr _
& "Bbbbbbbbbbbbb", _
vbInformation, "MESSAGE FROM xxx"
End Sub


C''''est_moi wrote:
I have a long MsgBox to user upon opening (autoopen) of a document
that goes like this:

MsgBox "Aaaaaaaaaaa. Bbbbbbbbbbbbb", vbInformation, "MESSAGE FROM
xxx"

I want to add a line between "Aaa" and "Bbb" so when pop-up is
displayed on screen, text is divided into 2 parts, "Aaa" on one line
(first paragraph within the box) -- space -- "Bbb" on the second
line (second paragraph within the box).

I looked it up and came up with a "char(13)" carriage return
function which for the life of me, I cannot find where to insert
between the "" of the prompt after the MsgBox or how. I have tried
every which way, to no avail.

Can anoyone help me?
 
I

iris

Hi Doug

I have a question in a totaly different subject. I tried posting it in a new
posting - but failed. I hopr you can help me with that...

I have a userform in word with 1 listbox and 3 textboxes.

the database is an access database with 7 columns.

I am initializing the listbox with 7 columnbound columns.

this is my code:

Private Sub UserForm_Initialize()
On Error Resume Next

Dim dbDatabase As Database
Dim rsd As Recordset
Dim d As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsd = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER BY
heb;", dbOpenSnapshot)

ListBox1.Clear
d = 0
With rsd
Do Until .EOF
ListBox1.AddItem (d)
ListBox1.ColumnCount = 3
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;4 in;0.5 in;"
ListBox1.Column(6, d) = ![hebrt]
ListBox1.Column(0, d) = ![heb]
ListBox1.Column(1, d) = ![eng]
ListBox1.Column(2, d) = ![engRT]
.MoveNext
d = d + 1
Loop
End With

rsd.Close
dbDatabase.Close
Set rsd = Nothing
Set dbDatabase = Nothing
End Sub

after initializing the list box, I want to click on an item in the listbox
and see the coloumnbound data from the database in the 3 textboxes (each
column in an other textbox).

Thank you in advance for your help!

Iris
 
I

iris

Hi Greg

I have a question in a totaly different subject. I tried posting it in a new
posting - but failed. I hopr you can help me with that...

I have a userform in word with 1 listbox and 3 textboxes.

the database is an access database with 7 columns.

I am initializing the listbox with 7 columnbound columns.

this is my code:

Private Sub UserForm_Initialize()
On Error Resume Next

Dim dbDatabase As Database
Dim rsd As Recordset
Dim d As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsd = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER BY
heb;", dbOpenSnapshot)

ListBox1.Clear
d = 0
With rsd
Do Until .EOF
ListBox1.AddItem (d)
ListBox1.ColumnCount = 3
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;4 in;0.5 in;"
ListBox1.Column(6, d) = ![hebrt]
ListBox1.Column(0, d) = ![heb]
ListBox1.Column(1, d) = ![eng]
ListBox1.Column(2, d) = ![engRT]
.MoveNext
d = d + 1
Loop
End With

rsd.Close
dbDatabase.Close
Set rsd = Nothing
Set dbDatabase = Nothing
End Sub

after initializing the list box, I want to click on an item in the listbox
and see the coloumnbound data from the database in the 3 textboxes (each
column in an other textbox).

Thank you in advance for your help!

Iris
 
D

Doug Robbins - Word MVP

PLEASE LOOK AT MY RESPONSES TO YOUR OTHER QUESTION WITH THE SUBJECT OF
CASCADING LIST BOXES.



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
iris said:
Hi Doug

I have a question in a totaly different subject. I tried posting it in a
new
posting - but failed. I hopr you can help me with that...

I have a userform in word with 1 listbox and 3 textboxes.

the database is an access database with 7 columns.

I am initializing the listbox with 7 columnbound columns.

this is my code:

Private Sub UserForm_Initialize()
On Error Resume Next

Dim dbDatabase As Database
Dim rsd As Recordset
Dim d As Integer
Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsd = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER BY
heb;", dbOpenSnapshot)

ListBox1.Clear
d = 0
With rsd
Do Until .EOF
ListBox1.AddItem (d)
ListBox1.ColumnCount = 3
ListBox1.BoundColumn = 7
ListBox1.ColumnWidths = "4 in;4 in;0.5 in;"
ListBox1.Column(6, d) = ![hebrt]
ListBox1.Column(0, d) = ![heb]
ListBox1.Column(1, d) = ![eng]
ListBox1.Column(2, d) = ![engRT]
.MoveNext
d = d + 1
Loop
End With

rsd.Close
dbDatabase.Close
Set rsd = Nothing
Set dbDatabase = Nothing
End Sub

after initializing the list box, I want to click on an item in the listbox
and see the coloumnbound data from the database in the 3 textboxes (each
column in an other textbox).

Thank you in advance for your help!

Iris


Doug Robbins - Word MVP said:
You do not need a macro to do that. From the View menu, select Toolbars
and
then check the Mail Merge toolbar item. It has a button on it that will
allow you to view the recipients of your mail merge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Greg Maxey said:
Hopefully line breaks on your end won't wreck this:

Sub ScratchMacro()
MsgBox "Aaaaaaaaaaa." _
& vbCr & vbCr _
& "Bbbbbbbbbbbbb", _
vbInformation, "MESSAGE FROM xxx"
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top