View Full Version : combobox selction, multiple bookmarks, spreadsheet link

08-18-2006, 05:49 PM
Newbie question im trying to use a word userform combo box selection (that is filled with named range in excel 10 columns each col a named range matching bookmark names in word document) and a command button that inserts selection data into word document to various bookmarks. Any help is greatly appreciated. Sorry to be so VBA illeterate, im a newbie.

Private Sub UserForm_Initialize()

Dim objExcel As New Excel.Application
Dim wb As Excel.Workbook
Dim FName As String
Dim AreaMan
Dim HydEmb
Dim PNumb
Dim sp, f

'Get data from Excel, based on
'DRJ http://www.vbaexpress.com/kb/getarticle.php?kb_id=179
FName = tbDataSource
If FName = "" Then
GoTo Canceled:
End If
Set wb = objExcel.Workbooks.Open(FName)

AreaMan = wb.Sheets(1).Range("AreaManagers").Value
comboManager.List = AreaMan
comboManager.ListIndex = 0

'This Data (HydEmb)is a named range with 10 columns. each col
'a named range matching bookmark names in word document
HydEmb = wb.Sheets(2).Range("HydEmb").Value
cboIDNumber.List = HydEmb
cboIDNumber.ListIndex = 0


With Me
.Width = 406
.Height = 300
End With
With CommandButton2
.Top = 222
.Left = 312
.Width = 60
.Height = 30
.Caption = "Expand"
End With

End Sub

Private Sub CommandButton1_Click()

With ActiveDocument

.Bookmarks("IDNumber").Range = cboIDNumber.List(cboIDNumber.ListIndex1)

.Bookmarks("PartNumber").Range = cboIDNumber.List(cboIDNumber.ListIndex2)

.Bookmarks("Condition").Range = cboIDNumber.List(cboIDNumber.ListIndex3)

.Bookmarks("HeatCert").Range = cboIDNumber.List(cboIDNumber.ListIndex1)

.Bookmarks("MaterialID").Range = cboIDNumber.List(cboIDNumber.ListIndex, 0)

End If
If chkGrainDirection = True Then
.Bookmarks("lh_name").Range = txtAuthor.Text
.Bookmarks("aname").Range = txtAuthor.Text
End If
End With

Application.ScreenUpdating = True
Unload Me
Unload UserForm1

End Sub

08-19-2006, 07:58 AM
What's your actual question?

08-19-2006, 10:48 AM
Is this any help?

08-21-2006, 11:15 AM
Hello Norie,
my question was how to populate a user selected combobox or listbox with data from excel (10 columns of data). with users selection of combobox fill word bookmarks that have same excel column name.

Hello mdmackillop ,
yes that helps that is a great sample and the one I started my VBA with. I have a long way to go. lol

that is a great sample thanks

this is what im looking into now and it seems to be close to what im lookin for. thanks for the replys I will keep checking back while Im not reading up on VBA.

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 = "PartData.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
NoOfRecords = .RecordCount
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
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"
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) _
txtName.Text = ActiveDocument.Bookmarks(BookMarkName) _
txtGenre.Text = ActiveDocument.Bookmarks(BookMarkGenre) _
txtTitle.Text = ActiveDocument.Bookmarks(BookMarkTitle) _
txtOther.Text = ActiveDocument.Bookmarks(BookMarkOther) _
'disable everything (except the option buttons)
'to force the user to use the option buttons
ListBox1.Enabled = False
ToggleTextBox False
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
If optSelect.Value Then
myList = ListBox1.ListIndex
InsertData ListBox1.List(myList, 0), _
InsertData ListBox1.List(myList, 1), _
InsertData ListBox1.List(myList, 2), _
InsertData ListBox1.List(myList, 3), _
InsertData ListBox1.List(myList, 4), _
MsgBox "You must select a type of data.", _
vbExclamation, "Nothing selected"
Exit Sub
End If
End If
With ActiveDocument
.Bookmarks("bkmQuantity").Range.Text = tbQuantity.Value
.Bookmarks("bkmControlNumber").Range.Text = tbControlNumber.Value
.Bookmarks("bkmPONumber").Range.Text = tbPONumber.Value

End With

Unload Me
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

08-21-2006, 11:27 AM
Glad to help out. Let us know if you have any specific problems.