I'm hoping someone can help me on this as I have been banging my head against this for about a week or so.
I have a userform that creates a set of controls (3 text boxes and a combo box) and interates a counter by 1 each time the button is pressed.
I have written an Excel VBA Macro that should open up a specific document (which will be a dotx in the final version but is a docx for this test) and;
Go to a set bookmark in the Word Document ("table1") Move to the start of the line with the bookmark Move up to the previous line Create a table with 1 row and 3 columns. Inserts the values from the first and second textboxes and combobox into columns 1,2 and 3 respectively Return to the specified "table2" bookmark and repeat this for each iterated line of the dynamically created controls
Also the Macro should
Go to a set bookmark in the Word Document ("table2") Move to the start of the line with the bookmark Move up to the previous line Create a table with 1 row and 2 columns. Inserts the values from the first and third textbox into columns 1 and 2 respectively Return to the specified "table2" bookmark and repeat this for each iterated line of the dynamically created controls
So basically at each bookmark if there are 3 lines of dynamic controls after the macro runs there should be
A table with 3 rows and 3 columns located above bookmark "table1" containing the values from the first and second textbox and the combobox value. A table with 3 rows and 2 columns located above bookmark "table2" containing the values from the first and third textbox values.
So my problem is that when the macro is run it either creates one line of the table at the top of the document, another at the "table1" bookmark and one at the "table2" bookmark or the 3 column table is created then another 3 column table is created inside the first cell of the first table and so on.
I am convinced I am missing something in regards to cursor control (as the usual problem is the first table cell is created at the first line of the document then the cursor seems to move to the "table1" bookmark and the macro continues from there).
If someone could give me some pointers on this I would greatly appreciated as I feel I am almost there but that I'm just overlooking something.
Here is the code I have so far, apologies in advance for any unused variables this is back of a napkin coding.
Private Sub CommandButton14_Click() 'Create WO Letter
'Open WO letter and copy paste data
Dim objWord As Word.Application
Dim objDoc As Word.Document
Dim riskCombo As Control
Dim theTextBox802 As Control
Dim theTextBox803 As Control
Dim theTextBox804 As Control
Dim b As Integer
Dim c As Integer
Dim intNoOfColumns
Dim wdDoc
Dim objRange1
Dim objRange2
Dim objTable1
Dim objTable2
b = iRiskCount
c = 1
If Me.WOLetter1.Value = False And Me.WOLetter2.Value = False And Me.WOLetter3.Value = False And Me.WOLetter4.Value = False Then
MsgBox "You Must Choose a Letter Type"
Exit Sub
End If
If UserForm1.MultiPage1.Pages(2).Frame15.Controls("Risk" & c).Value = "Risk" Then
MsgBox "Select Risk Level for line " & c
Exit Sub
End If
If Me.WOLetter1.Value = True Then
Set objWord = New Word.Application
objWord.DisplayAlerts = False
objWord.Visible = True
objWord.Activate
Set wdDoc = objWord.Documents.Add(ActiveWorkbook.Path & "\WOTest.docx")
ElseIf Me.WOLetter2.Value = True Then
Set objWord = New Word.Application
objWord.DisplayAlerts = False
objWord.Visible = True
'objWord.Documents.Open ActiveWorkbook.Path & "\WOTest.docx", ReadOnly:=True '"\test.dotx", ReadOnly:=True'"\Doc.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True
objWord.Activate
ElseIf Me.WOLetter3.Value = True Then
Set objWord = New Word.Application
objWord.DisplayAlerts = False
objWord.Visible = True
'objWord.Documents.Open ActiveWorkbook.Path & "\WOTest.docx", ReadOnly:=True '"\test.dotx", ReadOnly:=True'"\Doc.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True
objWord.Activate
ElseIf Me.WOLetter4.Value = True Then
Set objWord = New Word.Application
objWord.DisplayAlerts = False
objWord.Visible = True
'objWord.Documents.Open ActiveWorkbook.Path & "\WOTest.docx", ReadOnly:=True '"\test.dotx", ReadOnly:=True'"\Doc.dotm", ReadOnly:=True '"\test.dotx", ReadOnly:=True
objWord.Activate
End If
For Each riskCombo In UserForm1.MultiPage1.Pages(2).Frame15.Controls
If b > 0 Then
Set objRange1 = objWord.Selection.Range
Set objRange2 = objWord.Selection.Range
'Table1
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="table1"
objWord.Selection.HomeKey Unit:=wdLine, Extend:=wdMove
objWord.Selection.MoveUp
wdDoc.Tables.Add objRange1, 1, 3
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="table1"
objWord.Selection.HomeKey Unit:=wdLine, Extend:=wdMove
objWord.Selection.MoveUp
Set objTable1 = wdDoc.Tables(1)
'Table 2
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="table2"
objWord.Selection.HomeKey Unit:=wdLine, Extend:=wdMove
objWord.Selection.MoveUp
wdDoc.Tables.Add objRange2, 1, 2
objWord.Selection.GoTo What:=wdGoToBookmark, Name:="table2"
objWord.Selection.HomeKey Unit:=wdLine, Extend:=wdMove
objWord.Selection.MoveUp
Set objTable2 = wdDoc.Tables(1)
With objTable1
.Cell(0, 1).Range.Text = UserForm1.MultiPage1.Pages(2).Frame15.Controls("Text5" & c).Value
.Cell(0, 2).Range.Text = UserForm1.MultiPage1.Pages(2).Frame15.Controls("Text6" & c).Value
.Cell(0, 3).Range.Text = UserForm1.MultiPage1.Pages(2).Frame15.Controls("Risk" & c).Value
End With
With objTable2
.Cell(0, 1).Range.Text = UserForm1.MultiPage1.Pages(2).Frame15.Controls("Text5" & c).Value
.Cell(0, 2).Range.Text = UserForm1.MultiPage1.Pages(2).Frame15.Controls("Text7" & c).Value
End With
c = c + 1
b = b - 1
End If
Next riskCombo
objTable1.Columns(1).SetWidth ColumnWidth:=30, RulerStyle:= _
wdAdjustNone
objTable1.Columns(2).SetWidth ColumnWidth:=350, RulerStyle:= _
wdAdjustNone
objTable1.Columns(3).SetWidth ColumnWidth:=75, RulerStyle:= _
wdAdjustNone
objTable2.Columns(1).SetWidth ColumnWidth:=30, RulerStyle:= _
wdAdjustNone
objTable2.Columns(2).SetWidth ColumnWidth:=425, RulerStyle:= _
wdAdjustNone
End Sub 'end of test
Even some reassurance that I am on the right track with this would be appreciated as I have been beating my head against this for the past week.
You are setting both table one and table two to be the same.
"Set objTable1 = wdDoc.Tables(1)"
"Set objTable2 = wdDoc.Tables(1)"