Re: one column listbox, multiple bookmarks, spread*** link

From: Jean-Guy Marcil (no-spam_at_leaveme.alone)
Date: 02/19/04 

Date: Thu, 19 Feb 2004 10:57:15 -0500

Hi Liz,

To test the code I am posting, you will need the following:

A userform (UserForm1) with:
    A frame (Frame1), and in the frame:
        An option button (optSelect), followed by
        a list box (ListBox1), then
        another option (optCustom) button followed by
        5 text boxes and their corresponding labels
        (txtIni, txtName, txtGenre, txtTitle, txtOther)
    Under the frame:
    2 command buttons
    (cmdCancel, cmdInsert)
In the document, you will need at least 5 bookmarks:
    "bkmInitials"
    "bkmName"
    "bkmGenre"
    "bkmTitle"
    "bkmOther"
where the data will be inserted.

Then, you will need an Excel work*** ("Authors.xls") with five columns:
initials, name, genre, title, others
with all the data defined by the name "initials"

Just place the Excel work*** in the same folder as the Word document from
which you execute the code and it will find it.

The principle is that when the userform is displayed, the user must select
either from the list or create customize data, thus one of the option button
must be selected.

'_______________________________________
'If the name of the bookmark are changed in the document
'Just change them here
Const BookMarkIni = "bkmInitials"
Const BookMarkName = "bkmName"
Const BookMarkGenre = "bkmGenre"
Const BookMarkTitle = "bkmTitle"
Const BookMarkOther = "bkmOther"

'_______________________________________
Private Sub cmdCancel_Click()

    Me.Hide

    Unload Me

End Sub
'_______________________________________

'_______________________________________
Private Sub cmdInsert_Click()
Dim myList As Long

If optCustom.Value Then
    InsertData txtIni.Text, BookMarkIni
    InsertData txtName.Text, BookMarkName
    InsertData txtGenre.Text, BookMarkGenre
    InsertData txtTitle.Text, BookMarkTitle
    InsertData txtOther.Text, BookMarkOther
Else
    If optSelect.Value Then
        myList = ListBox1.ListIndex
        InsertData ListBox1.List(myList, 0), _
            BookMarkIni
        InsertData ListBox1.List(myList, 1), _
            BookMarkName
        InsertData ListBox1.List(myList, 2), _
            BookMarkGenre
        InsertData ListBox1.List(myList, 3), _
            BookMarkTitle
        InsertData ListBox1.List(myList, 4), _
            BookMarkOther
    Else
        MsgBox "You must select a type of data.", _
            vbExclamation, "Nothing selected"
        Exit Sub
    End If
End If

Me.Hide
Unload Me

End Sub
'_______________________________________

'_______________________________________
Private Sub optCustom_Click()

    ListBox1.Enabled = False

    ToggleTextBox True

End Sub
'_______________________________________

'_______________________________________
Private Sub optSelect_Click()

    ListBox1.Enabled = True

    ToggleTextBox False

End Sub
'_______________________________________

'_______________________________________
Private Sub UserForm_Initialize()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim Col As Long
Dim ColWidth As String
Dim DataPath
Dim FullDataPath As String
Dim DataName As String

DataPath = ActiveDocument.Path
DataName = "Authors.xls"
FullDataPath = DataPath & "\" & DataName

' Open the database
Set db = OpenDatabase(FullDataPath, False, _
    False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `initials`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields
'in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

'This bit sets the width of the first column to a value,
'and all others to 0, so they are invisible
'any number of column can be accommodated with this code
With ListBox1
    For Col = 1 To .ColumnCount
        If Col = 1 Then
            ColWidth = "75"
        Else
            ColWidth = ColWidth & ";0"
        End If
    Next Col
    .ColumnWidths = ColWidth
    .BoundColumn = 1
End With

'Fill in the text box on the userform from the value in the document,
'in case a user goes back to change a customize entry,
'he/she will not have to retype everything, just adjust what is needed
txtIni.Text = ActiveDocument.Bookmarks(BookMarkIni) _
    .Range.Text
txtName.Text = ActiveDocument.Bookmarks(BookMarkName) _
    .Range.Text
txtGenre.Text = ActiveDocument.Bookmarks(BookMarkGenre) _
    .Range.Text
txtTitle.Text = ActiveDocument.Bookmarks(BookMarkTitle) _
    .Range.Text
txtOther.Text = ActiveDocument.Bookmarks(BookMarkOther) _
    .Range.Text

'disable everything (except the option buttons)
'to force the user to use the option buttons
ListBox1.Enabled = False

ToggleTextBox False

End Sub
'_______________________________________

'_______________________________________
Sub ToggleTextBox(MyState As Boolean)

Dim myBox As Control

For Each myBox In Frame1.Controls
    If (TypeOf myBox Is MSForms.TextBox) Then
        myBox.Enabled = MyState
    End If
Next myBox

End Sub
'_______________________________________

'_______________________________________
Sub InsertData(DataForm As Variant, DocBkmName As String)
Dim DataRange As Range

Set DataRange = ActiveDocument.Bookmarks(DocBkmName).Range
DataRange.Text = DataForm
ActiveDocument.Bookmarks.Add DocBkmName, DataRange

End Sub
'_______________________________________ 

--
Cheers!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org
"LizPCS" <LizPCS.11v0el@nospam.WordForums.com> a écrit dans le message de
news: LizPCS.11v0el@nospam.WordForums.com...
>
> My brain hurts again...I have been searching for a short while on this
> and after looking at code for 5 hours a day for 3 days now and being a
> beginner - its all starting to turn into mush in my head.
> Here is what I want to do:
> I have a userform with a list box that loads the listbox from a
> spread*** using the MVP method from "Load a ListBox from a Named
> Range in Excel using DAO".  So that all works but my range is only one
> column because that is all I want to show in the listbox.
> For people like me who need some kind of reference - this is for a
> letter and the list box is the author so I only want it to show the
> initials of the author on the form.  Then once an author is selected 5
> different bookmarks on the document should be filled out. I want to
> grab the data for the 5 different bookmarks from the 5 columns of the
> spread***-for easy updating.  Make sense?
> Another question - the author may be someone other than a person on the
> spread*** - in this case I would like to add another selection on the
> list box in which case it would get the text from a text box on the
> form to fill out a few of those bookmarks.
> I thought I may be able to just do an if-then statement for the list
> box - like if they select something in the list box then get the data
> from the spread*** but if they enter something in the other author
> text box then fill in the bookmarks with the text from the text box.
> Hopefully that all makes sense.
> Here is the code for the "Load a ListBox from a Named Range in Excel
> using DAO" modified for my project:
>
> Private Sub UserForm_Initialize()
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim NoOfRecords As Long
>
> ' Open the database
> Set db = OpenDatabase("S:\rjmWORD\authors.xls", False, False,
> "Excel 8.0")
>
> ' Retrieve the recordset
> Set rs = db.OpenRecordset("SELECT * FROM `initials`")
>
> ' Determine the number of retrieved records
> With rs
> MoveLast
> NoOfRecords = .RecordCount
> MoveFirst
> End With
>
> ' Set the number of Columns = number of Fields in recordset
> ListBox1.ColumnCount = rs.Fields.Count
>
> ' Load the ListBox with the retrieved records
> ListBox1.Column = rs.GetRows(NoOfRecords)
>
> ' Cleanup
> rs.Close
> db.Close
>
> Set rs = Nothing
> Set db = Nothing
>
> End Sub
>
>
> Any help would be greatly appreciated.  And I will continue searching.
> Thanks to everyone for all the help I've already found just browsing
> here and the MVP site - you guys are awesome!
> Liz
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>