combobox selction, multiple bookmarks, spreadsheet link

[greenspec]

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

Canceled:
objExcel.Quit

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
cboIDNumber.Result

.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

[Norie]

What's your actual question?

[mdmackillop]

Is this any help?
http://vbaexpress.com/kb/getarticle.php?kb_id=184

[greenspec]

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
.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

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
With ActiveDocument
.Bookmarks("bkmQuantity").Range.Text = tbQuantity.Value
.Bookmarks("bkmControlNumber").Range.Text = tbControlNumber.Value
.Bookmarks("bkmPONumber").Range.Text = tbPONumber.Value

End With


Me.Hide
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

[mdmackillop]

Glad to help out. Let us know if you have any specific problems.