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