Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2014, 06:16 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default Referencing rows of a table at a bookmarked location based on the value of a column


I have a table in word that has a column which contains a drop down list for priority (the choices are Low, Medium, High). I have a bookmarked section of the same document that I would like to show all rows of this table that are designated high priority. Can this be acheived with a macro by using tables in word? Would this be made simpler by inserting an excel worksheet object instead?
Reply With Quote
  #2  
Old 05-21-2014, 07:31 AM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

Assumes the column containing the Low, Med, High is column 2:

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oTbl As Word.Table
Dim oCol As New Collection
Dim lngIndex As Long
Dim oRng As Word.Range
Dim oRow As Row
Dim lngCell As Long
  Set oTbl = ActiveDocument.Tables(1)
  For Each oRow In oTbl.Rows
    If Left(oRow.Cells(2).Range.Text, Len(oRow.Cells(2).Range.Text) - 2) = "High" Then
      oCol.Add oRow
    End If
  Next oRow
  Set oRng = ActiveDocument.Bookmarks("bmRows").Range
  If oRng.Tables.Count = 1 Then
    oRng.Tables(1).Delete
  End If
  Set oTbl = oRng.Tables.Add(oRng, oCol.Count, 3)
  For lngIndex = 1 To oCol.Count
    For lngCell = 1 To oCol(lngIndex).Range.Cells.Count
      oTbl.Rows(lngIndex).Cells(lngCell).Range.Text = Left(oCol(lngIndex).Cells(lngCell).Range.Text, _
          Len(oCol(lngIndex).Cells(lngCell).Range.Text) - 2)
    Next
  Next
  ActiveDocument.Bookmarks.Add "bmRows", oTbl.Range
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 05-21-2014, 07:58 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default

This will help me for sure. Thanks pal!
I now have another problem though; I created a user form for the user to enter new items into the original table and I'm having problems loading the form data into the cells of a row object.
Code:
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub OKButton_Click()
'Error handling for empty boxes
Dim msg, msg2, msg3 As String
msg = "You must enter a Description"
msg2 = "You must enter a Date"
msg3 = "You must enter a Priority"
If (DescriptionBox = "") Then
    MsgBox (msg)
ElseIf (DateBox = "") Then
    MsgBox (msg2)
ElseIf (PriorityCombo = "") Then
    MsgBox (msg3)
Else
    'No errors detected, add row to table and populate
    Dim oTbl As Word.Table
    Dim oRow As Row
    Dim oCol As Column
    Dim oRng As Word.Range
    Dim lngIndex As Long
    Dim lngCell As Long
    Set oTbl = ActiveDocument.Tables(1)
    oRow.Cells(0).Range.Text = DescriptionBox.Text
    oRow.Cells(1).Range.Text = DateBox.Text
    oRow.Cells(2).Range.Text = PriorityCombo.Text
    oRow.Cells(3).Range.Text = ActiveDocument.Tables(1).Rows(0).Cells(3).Range.Text
    ActiveDocument.Tables(1).Rows.Add oRow
    Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
PriorityCombo.Clear
PriorityCombo.AddItem "Low"
PriorityCombo.AddItem "Medium"
PriorityCombo.AddItem "High"
End Sub
Reply With Quote
  #4  
Old 05-21-2014, 09:44 AM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

On second thought this might be better. This way you can use non-uniform tables:

Code:
Sub ScratchMacro()
Dim oTbl As Word.Table, oTblTarget As Table
Dim arrIndex() As Long
Dim lngIndex As Long
Dim oRng As Word.Range
  Set oTbl = ActiveDocument.Tables(1)
  oTbl.Range.Copy
  Set oRng = ActiveDocument.Bookmarks("bmRows").Range
  If oRng.Tables.Count = 1 Then
    oRng.Tables(1).Delete
  End If
  oRng.Paste
  Set oTblTarget = oRng.Tables(1)
  ActiveDocument.Bookmarks.Add "bmRows", oTblTarget.Range
  ReDim arrIndex(0)
  For lngIndex = oTbl.Rows.Count To 1 Step -1
    If Not Left(oTbl.Cell(lngIndex, 1).Range.Text, Len(oTbl.Cell(lngIndex, 1).Range.Text) - 2) = "High" Then
      arrIndex(UBound(arrIndex)) = oTbl.Rows(lngIndex).Index
      ReDim Preserve arrIndex(UBound(arrIndex) + 1)
    End If
  Next lngIndex
  For lngIndex = 0 To UBound(arrIndex) - 1
    oTblTarget.Rows(arrIndex(lngIndex)).Delete
  Next lngIndex
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #5  
Old 05-21-2014, 09:55 AM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

Code:
Private Sub OKButton_Click()
Dim oTbl As Word.Table
Dim oRow As Row
Dim oRng As Word.Range
  Select Case True
    Case DescriptionBox = vbNullString
      MsgBox "You must enter a description"
    Case Not IsDate(DateBox)
      MsgBox "You must enter a valid date."
    Case PriorityCombo = vbNullString
      MsgBox "You must select the priority."
    Case Else
      Set oTbl = ActiveDocument.Tables(1)
      Set oRow = oTbl.Rows.Add
      oRow.Cells(1).Range.Text = DescriptionBox.Text
      oRow.Cells(2).Range.Text = DateBox.Text
      oRow.Cells(3).Range.Text = PriorityCombo.Text
      Unload Me
  End Select
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #6  
Old 05-21-2014, 10:23 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default

Thanks again, I've been gradually working through it and I'm making progress.
Reply With Quote
  #7  
Old 05-21-2014, 10:33 AM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

If you experience is anything like mine often is then you are sitting surrounded by clumps of hair and bloody scalp. Gook luck.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #8  
Old 05-21-2014, 11:30 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default

Haha, yeah..you're not programming until you want to set fire to your office.

My code is looking a lot like the last block of code you submitted. The problem I'm struggling with now is that the fourth cell in the new row should contain an ActiveX CheckBox object, but I'm having trouble finding a way to insert it.
Reply With Quote
  #9  
Old 05-21-2014, 01:21 PM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

Code:
  oRow.Range.Cells(4).Range.InlineShapes.AddOLEControl ClassType:="Forms.CheckBox.1"
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #10  
Old 05-22-2014, 07:21 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default

Thanks Greg, that worked great! I don't suppose you would know how to create the checkbox with no caption (or change it to "" after the fact)?
Reply With Quote
  #11  
Old 05-22-2014, 08:20 AM
gmaxey gmaxey is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 32bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2010 (Version 14.0)
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 1,052
gmaxey has a spectacular aura aboutgmaxey has a spectacular aura about
Default

Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRow As Row
Dim oCtr As InlineShape
Set oRow = ActiveDocument.Tables(1).Rows.Last
Set oCtr = oRow.Range.Cells(4).Range.InlineShapes.AddOLEControl(ClassType:="Forms.CheckBox.1")
oCtr.OLEFormat.Object.Caption = ""
End Sub
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #12  
Old 05-22-2014, 08:33 AM
jpb103's Avatar
jpb103 jpb103 is offline Referencing rows of a table at a bookmarked location based on the value of a column Windows 7 64bit Referencing rows of a table at a bookmarked location based on the value of a column Office 2007
Advanced Beginner
Referencing rows of a table at a bookmarked location based on the value of a column
 
Join Date: May 2014
Location: Thunder Bay, Ontario
Posts: 58
jpb103 is on a distinguished road
Default

Thanks again Greg! That resolves all my problems relevant to this thread.
Reply With Quote
Reply

Tags
bookmark, rows, table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mouseover cell to indicate mouse pointer location based on Specific Row/Column values bolandk Excel 1 05-15-2014 08:22 AM
Group Table Rows/Column (like Excel Group) eoinymc Word 1 03-11-2014 04:51 AM
Referencing rows of a table at a bookmarked location based on the value of a column Need help on duplicates in column; deciding which to keep based on other column nwcf Excel 3 01-31-2014 09:43 AM
Referencing rows of a table at a bookmarked location based on the value of a column Grouping table rows to prevent individual rows from breaking across pages dennist77 Word 1 10-29-2013 11:39 PM
Referencing rows of a table at a bookmarked location based on the value of a column Referencing a Image File and Location matt8445 PowerPoint 2 11-08-2012 08:23 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 12:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft