Word form list box - Word и Excel - помощь в работе с программами

Содержание

1 Add items to ListBox
2 Add item to List Box
3 Add names of all open workbooks to the list box
4 Assign the data in a worksheet to RowSource of a ListBox
5 Determining the selected item
6 Evaluating Which Items Are Selected in the Multiselect List Box
7 Get all selected items in a list box
8 Get selected from ListBox
9 Get the selected items in a ListBox
10 Make sure the RowSource property is empty
11 Select the items programmatically

Add items to ListBox

   <source lang="vb">

Sub ShowDialog()

   With UserForm1.ListBox1
       .MultiSelect = fmMultiSelectSingle
       .RowSource = ""
       .AddItem "January"
       .AddItem "February"
       .AddItem "March"
       .AddItem "April"
       .AddItem "May"
       .AddItem "June"
       .AddItem "July"
       .AddItem "August"
       .AddItem "September"
       .AddItem "October"
       .AddItem "November"
       .AddItem "December"
   End With
   UserForm1.Show

End Sub

</source>

Add item to List Box

   <source lang="vb">

Private Sub Form_Load()

   Dim obj As AccessObject
   For Each obj In CurrentData.AllTables
       Me.yourListBox.AddItem obj.Name
   Next obj

End Sub

</source>

Add names of all open workbooks to the list box

   <source lang="vb">

Sub UserForm_Initialize()

   Dim wkBook As Workbook
   For Each wkBook In Workbooks
       lstWorkbooks.AddItem wkBook.Name
   Next

End Sub

</source>

Assign the data in a worksheet to RowSource of a ListBox

   <source lang="vb">

Private Sub obMonths_Click()

   ListBox1.RowSource = "Sheet1!Months"

End Sub

</source>

Determining the selected item

   <source lang="vb">

 Private Sub OKButton_Click()
     Dim Msg As String
     Msg = "You selected item # "
     Msg = Msg & ListBox1.ListIndex
     Msg = Msg & vbNewLine
     Msg = Msg & ListBox1.Value
     MsgBox Msg
     Unload UserForm1
 End Sub
</source>

Evaluating Which Items Are Selected in the Multiselect List Box

   <source lang="vb">

Private Sub cmdRunReports_Click()

   Dim varItem As Variant
   Dim lst As ListBox
   Set lst = Me.yourList
   If lst.MultiSelect > 0 Then
       If lst.ItemsSelected.Count > 0 Then
           For Each varItem In lst.ItemsSelected
               DoCmd.OpenReport lst.ItemData(varItem), acViewPreview
           Next varItem
        End If
    End If

End Sub

</source>

Get all selected items in a list box

   <source lang="vb">

Private Sub OKButton_Click()

   If ListBox1.ListIndex = -1 Then
       msg = "Nothing"
   Else
       msg = ""
       For i = 0 To ListBox1.ListCount - 1
           If ListBox1.Selected(i) Then _
             msg = msg & ListBox1.List(i) & vbCrLf
       Next i
   End If
   MsgBox "You selected: " & vbCrLf & msg
   Unload Me

End Sub

</source>

Get selected from ListBox

   <source lang="vb">

Private Sub OKButton_Click()

   ActiveCell = ListBox1.Value
   Unload Me

End Sub

</source>

Get the selected items in a ListBox

   <source lang="vb">

 Private Sub OKButton_Click()
     Dim Msg As String
     Dim i As Integer
     Msg = "You selected" & vbNewLine
     For i = 0 To ListBox1.ListCount - 1
         If ListBox1.Selected(i) Then
             Msg = Msg & ListBox1.List(i) & vbNewLine
         End If
     Next i
     MsgBox Msg
     Unload UserForm1
 End Sub
</source>

Make sure the RowSource property is empty

   <source lang="vb">

Sub ShowDialog1()

   With UserForm1
       .ListBox1.RowSource = "Sheet1!Months"
       .obMonths.Value = True
   End With
   UserForm1.Show

End Sub

</source>

Select the items programmatically

   <source lang="vb">

Private Sub SelectAllButton_Click()

   For r = 0 To ListBox1.ListCount - 1
       ListBox1.Selected(r) = True
   Next r

End Sub

</source>

Источник

Remove From My Forums

Question
I am creating a listbox for an online Word form I am creating. I am using the legacy list box as I have about 70 records that I need to include in this listbox. I’m in the Visual Basic area and learned my «line was too long» so I researched that
I could add «& _» to break up the lines. That worked great. However, now the record at the end of the line before «& _» shows up on the same line as the record on the next line, when I run the code. How can I fix this. I have searched
and searched and can’t find it.

Example:

ListBox1.List = Array(«Apples», «Oranges», «Watermelon», «Kiwi», «Bananas» & _
«Limes», «Lemons», «Grapes», «Cherries», «Cantelope», «Blackberry»)

After I run and go back to see how it works in the Word form, here is how it shows up in the listbox:

Apples

Oranges

Watermelon

Kiwi

BananasLimes (HERE LIES THE PROBLEM. How do I get «Limes» to go to the next line in the Listbox?)

Lemons

Grapes

etc.

I really appreciate your help. I’m spending way too much time trying to figure this out.

Thanks,

Lauren

Answers

Try:

ListBox1.List = Array(«Apples», «Oranges», «Watermelon», «Kiwi», «Bananas», _
«Limes», «Lemons», «Grapes», «Cherries», «Cantelope», «Blackberry»)

(note the comma after «Bananas»)

Cheers
Paul Edstein
[MS MVP — Word]
- Edited by
  
  Monday, April 7, 2014 4:42 PM
  remove &
- Marked as answer by
  George Hua
  Monday, April 7, 2014 4:43 PM
Sorry, you should omit the & as well.

Cheers
Paul Edstein
[MS MVP — Word]
- Marked as answer by
  lawoman728
  Thursday, March 6, 2014 10:13 PM

Источник

private void button1_Click(object sender, EventArgs e)
        {
            var nomerzakaza = textBox8.Text;
            var firstName = textBox3.Text;
            var lastName = textBox2.Text;
            var otchestvo = textBox4.Text;
            var marka = textBox5.Text;
            var model = textBox6.Text;
            var godvipyska = textBox7.Text;
            var gosnomer = textBox9.Text;
            var opisanie = textBox1.Text;
            
 
            var wordApp = new Word.Application();
            wordApp.Visible = false;
 
            try
            {
                var wordDocument = wordApp.Documents.Open(TemplateFileName);
 
                ReplaceWordSub("{nomerzakaza}", nomerzakaza, wordDocument);
                ReplaceWordSub("{firstname}", firstName, wordDocument);
                ReplaceWordSub("{lastname}", lastName, wordDocument);
                ReplaceWordSub("{otchestvo}", otchestvo, wordDocument);
                ReplaceWordSub("{marka}", marka, wordDocument);
                ReplaceWordSub("{model}", model, wordDocument);
                ReplaceWordSub("{godvipyska}", godvipyska, wordDocument);
                ReplaceWordSub("{gosnomer}", gosnomer, wordDocument);
                ReplaceWordSub("{opisanie}", opisanie, wordDocument);
                ReplaceWordSub("{repairitem}", repairitem, wordDocument);
 
                {
                    wordDocument.SaveAs(@"C:UsersOberleutnantDesktopReportszakaz-naryad.docx");
                    wordApp.Visible = false;
                    MessageBox.Show("Заказ-наряд создан. Вы можете найти его на рабочем столе в папке Reports"); //Сообщение об успешном сохранении файла
                    wordDocument.Close(); //Закрытие документа MS Word
                }
 
            }
            catch
            {
                MessageBox.Show("Error"); //Вывод сообщения в случае ошибки
            }
            finally
            {
                wordApp.Quit(); //Выход из MS Word
            }
        }

Источник

Default

You say you don’t want to open the Excel file? The second line of your code «Opens» the Excel file.

You could use and ADODB connection:

Code:

Option Explicit
Public Function xlFillList(oListOrComboBox As Object, strWorkbook As String, _
                           bSuppressHeader As Boolean, strSQL As String, _
                           bSingleColumn As Boolean)
Dim oConn As Object
Dim oRecordSet As Object
Dim lngNumRecs As Long, lngIndex As Long
Dim strWidth As String
Dim strConnection As String

  
  
  'Create connection:
  Set oConn = CreateObject("ADODB.Connection")
  If bSuppressHeader Then
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  Else
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & strWorkbook & ";" & _
                    "Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
  End If
  oConn.Open ConnectionString:=strConnection
  Set oRecordSet = CreateObject("ADODB.Recordset")
  'Read the data from the worksheet.
  oRecordSet.Open strSQL, oConn, 3, 1 '3: adOpenStatic, 1: adLockReadOnly
  With oRecordSet
    'Find the last record.
    .MoveLast
    'Get count.
    lngNumRecs = .RecordCount
    'Return to the start.
    .MoveFirst
  End With
  With oListOrComboBox
    .Clear
    'Load the records into the columns of the named list/combo box.
    .ColumnCount = oRecordSet.Fields.Count
    .Column = oRecordSet.GetRows(lngNumRecs)
    strWidth = vbNullString
    If bSingleColumn Then
     'Set the widths of the combo/list box columns to display only the first column.
      strWidth = .Width - 20 & " pt;"
      For lngIndex = 2 To .ColumnCount
        strWidth = strWidth & "0 pt"
        If lngIndex < .ColumnCount Then
          strWidth = strWidth & ";"
        End If
      Next lngIndex
    Else
      For lngIndex = 1 To .ColumnCount
        strWidth = strWidth & Val(.Width  .ColumnCount) - 10 & " pt;"
      Next lngIndex
      .ColumnWidths = strWidth
    End If
  End With
Cleanup:
  If oRecordSet.State = 1 Then oRecordSet.Close
  Set oRecordSet = Nothing
  If oConn.State = 1 Then oConn.Close
  Set oConn = Nothing
lbl_Exit:
  Exit Function
End Function

Call from form like this:

Code:

Private Sub UserForm_Initialize()
Dim DataSourcePath As String
Dim strSQL As String
  DataSourcePath = ThisDocument.Path & "Basic Fill.xlsx"
  'Get all data from sheet named "BasicI", exclude heading row, single Column
  strSQL = "SELECT * FROM [BasicI$];"
  mod_ExcelInteropSA.xlFillList lstBasicI, DataSourcePath, "True", strSQL, "True"
  'Get all data from sheet named "BasicII", including heading row, show all columns
  strSQL = "SELECT * FROM [BasicII$];"
  mod_ExcelInteropSA.xlFillList lstBasicII, DataSourcePath, "False", strSQL, "False"
  'Get data from columns headed "Name" and "Amount" from sheet named "BasicIII", exclude heading row, show all columns
  strSQL = "SELECT Name, Amount From [BasicIII$];"
  mod_ExcelInteropSA.xlFillList lstBasicIII, DataSourcePath, "True", strSQL, "False"
lbl_Exit:
  Exit Sub
End Sub

Источник

Kevin Tang said:

Hello,

I have a ListBox in my VBA Form, named ListBox1
In UserForm_Initialize(), I have two statements to enable List Column Head:
ListBox1.ColumnCount = 2
ListBox1.ColumnHeads = True

My questions are:
1) how to set the Column Head’s name? such as «ID», «Name»….

You can’t. There is a bug in VBA and Microsoft never got round to fixing it.
Instead, set ColumnHeads to False, and position a couple of labels
immediately above the listbox whose captions will show the column headings.

2) how to add item into ListBox1 in both columns?? (e.g. Add (00001,

Kevin) )

Two possible approaches to this.

1. If you want to load the entire listbox in one go, put all your items into
a 2-dimensional array, and then assign the array to the List property of the
Listbox.

2. If you just want to add a single new row, use AddItem to add the new row
and assign text to the cell pointed to by the BoundCOlumn property (column 0
by default), Then add the text to the second column by using
ListBox1.List(n, 1) = «My new text», where n is the row number you want.

Источник

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Thanks. We have received your request and will respond promptly.

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It’s Free!

*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ListBox in MS Word

(OP)

24 Apr 01 14:53

I guess I’m spoiled by Access, but I can’t figure out how to insert a listbox into a Word macro to enable the user to select text to be inserted into a document. Everything I’ve read leaves me very confused. Can anyone help a senile old man do this?? Thanks.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Join Tek-Tips^® Today!

Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.

Here’s Why Members Love Tek-Tips Forums:

Talk To Other Members
Notification Of Responses To Questions
Favorite Forums One Click Access
Keyword Search Of All Posts, And More…

Register now while it’s still free!

Already a member? Close this window and log in.

Join Us Close

Источник

Add a reference to Microsoft.Office.Interop.Word to your project, it is under the Extensions tab:

This code will generate a document, create a table, and just populate the table with the string hello. I assume that you know how to read data from your listboxes, so you should be able to easily adapt this code to do that:

Imports Word = Microsoft.Office.Interop.Word
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim word As New Word.Application()
        word.Visible = True

        Dim doc = word.Documents.Add()
        Dim table = doc.ActiveWindow.Selection.Tables.Add(doc.ActiveWindow.Selection.Range, 30, 10)

        For i As Integer = 0 To 9
            table.Cell(i, i).Range.Text = "hello"
        Next
    End Sub
End Class

Источник

Содержание

Add items to ListBox

Add item to List Box

Add names of all open workbooks to the list box

Assign the data in a worksheet to RowSource of a ListBox

Determining the selected item

Evaluating Which Items Are Selected in the Multiselect List Box

Get all selected items in a list box

Get selected from ListBox

Get the selected items in a ListBox

Make sure the RowSource property is empty

Select the items programmatically

Question

Answers

Contact US

Come Join Us!

Posting Guidelines

ListBox in MS Word

ListBox in MS Word

Red Flag Submitted

Join Tek-Tips® Today!

А вот еще интересные статьи:

Join Tek-Tips^® Today!