How to select the first row of the current table

Jennifer Murphy · #1 01-28-2012, 12:39 PM

I am trying to write a macro to change some table settings. It's sorta working, but I could use a little help.

1. I can't figure out how to select the first row of the current table so I can set the font and flag it as a header row.

As I understand it, this code selects the second row in the first table of a document:

Code:

ActiveDocument.Tables(1).Rows(2).Select

Can I then use this code to select the first row in the table where the cursor is?

Code:

Selection.Tables(1).Rows(1).Select

I am also using this code to ensure that the cursor is actually in a table.

Code:

If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", , "MyTableSettings macro"
  Exit Sub
End If

Jennifer Murphy · #2 01-28-2012, 03:41 PM

I think I got it. Here's the macro. It changes three settings:

It turns off the Break Across Pages setting (for rows).
It turns off the Auto Fit setting (for columns).
It set row 1 as a header row if the user says "Yes".

Here's the code. Comments appreciated.

Code:

'===========================================================================
'                        My Table Settings
' Correct the table settings to what Word should have made them.
' 07/23/10  Recorded and modified.
' 01/28/12  Add code for header row
'===========================================================================
Sub MyTableSettings()
Const MyName = "MyTableSettings"
Dim SettingBreakOld                 'Old setting
Dim SettingAutoFitOld               'Old setting
Dim SettingHeaderRow As Boolean     'True = set first row as header
Dim SettingHeaderRowOld As Boolean  'Old setting
Dim Msg
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
 
'Ask if they want a header row?
Select Case MsgBox("Set row 1 as header?", vbYesNoCancel, MyName)
  Case vbYes
    SettingHeaderRow = True
  Case vbNo
    SettingHeaderRow = False
  Case vbCancel
    MsgBox "Aborted", vbOKOnly, MyName
    Exit Sub
End Select
'Save old settings
SettingBreakOld = Selection.Rows.AllowBreakAcrossPages
If SettingBreakOld = -1 Then   '-1 = On, 0 = Off
  SettingBreakOld = "On"
ElseIf SettingBreakOld = 0 Then
  SettingBreakOld = "Off"
End If
SettingAutoFitOld = Selection.Tables(1).AllowAutoFit
If SettingAutoFitOld = "True" Then   'True = On, False = Off
  SettingAutoFitOld = "On"
ElseIf SettingAutoFitOld = "False" Then
  SettingAutoFitOld = "Off"
End If
SettingHeaderRowOld = Selection.Tables(1).Rows(1).HeadingFormat
'Set the new ones
Selection.Tables(1).Select                                    'Select the entire table
Selection.Rows.AllowBreakAcrossPages = False                  'Stop rows from breaking
Selection.Tables(1).AllowAutoFit = False                      'Stop auto-resizing
Selection.Tables(1).Rows(1).HeadingFormat = SettingHeaderRow  'Set the heading setting
'Report the results
Msg = "Autofit = Off (was " & SettingAutoFitOld & "), " & vbCrLf & _
      "Break = Off (was " & SettingBreakOld & "), " & vbCrLf & _
      "Header row = " & SettingHeaderRow & " (was " & SettingHeaderRowOld & ")"
MsgBox Msg, vbOKOnly, MyName
End Sub

macropod · #3 01-28-2012, 07:03 PM

Hi Jennifer,

Your code could be simplified a bit. In particular, there's no need to select the whole table before working on it. Aside from executing faster, not selecting it has the advantage of leaving the selected range unchanged:

Code:

Sub MyTableSettings()
Const MyName = "MyTableSettings"
Dim SettingBreakOld                 'Old setting
Dim SettingAutoFitOld               'Old setting
Dim SettingHeaderRowOld As Boolean  'Old setting
Dim Msg
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
'Save old settings
With Selection.Tables(1)
  SettingBreakOld = (.Rows.AllowBreakAcrossPages = True)
  SettingAutoFitOld = (.AllowAutoFit = True)
  SettingHeaderRowOld = (.Rows(1).HeadingFormat = True)
  'Set the new ones
  .Rows.AllowBreakAcrossPages = False                  'Stop rows from breaking
  .AllowAutoFit = False
  'Ask if they want a header row?
  Select Case MsgBox("Set row 1 as header?", vbYesNoCancel, MyName)
    Case vbYes
      .Rows(1).HeadingFormat = True
    Case vbNo
      .Rows(1).HeadingFormat = False
    Case vbCancel
      MsgBox "Aborted", vbOKOnly, MyName
      Exit Sub
  End Select
  'Report the results
  Msg = "Autofit = True (was " & SettingAutoFitOld & "), " & vbCrLf & _
      "Break = True (was " & SettingBreakOld & "), " & vbCrLf & _
      "Header row = " & (.Rows(1).HeadingFormat = True) & " (was " & SettingHeaderRowOld & ")"
  MsgBox Msg, vbOKOnly, MyName
End With
End Sub

Jennifer Murphy · #4 01-28-2012, 08:12 PM

Paul,

Thanks for the suggestions.

The reason I selected the whole table was because I wanted the "Do not break" option to be applied to all rows. From your code, it appears that the

Code:

With Selection.Tables(1)
 
   ...
 
End With

acts as if the table were selected within that With block. Is that correct? This is indeed much better code.

The other change that I noticed is you changed my test from "<> True" to "= False". Is there a reason for that? I wrote it my way in case there is some other possibility besides True and False, such as Null or Empty. I have been burned before, especially with VBA, when the application (Word) returns odd results. I want to do something if it's true only.

macropod · #5 01-28-2012, 08:24 PM

Hi Jennifer,

In a sense the 'With Selection.Tables(1) .. End With' block does act as if the table were selected. Since your selection point is already within the table, that's all that's needed.

Quote:

The other change that I noticed is you changed my test from "<> True" to "= False". Is there a reason for that?

Only that your SettingHeaderRowOld variable returns True/False, whilst the others were returning On/Off - which amounted to the same thing. AllowAutoFit can only be True/False, though .Rows.AllowBreakAcrossPages (and, hence SettingBreakOld) could be True/False/Undefined - the latter if some rows were allowed to break and others weren't.

Jennifer Murphy · #6 01-29-2012, 11:41 AM

I am making progress, but have run into a couple of puzzling results.

I wanted to use this same syntax to change the font and the borders. I started by capturing the code using the recorder. This is what I got:

Code:

Sub Macro1()
  Selection.Font.Name = "Calibri"
  Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
  Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
End Sub

Then I tried to adapt it to my macro.

For the font change line, I got a Compile error: "Methor or data member not found".

For the last two lines of the border settings, I got a Runtime error '5941': "The requested member of the collection does not exist".

These lines are shown in red and commented out.

Code:

With Selection.Tables(1)
  .Rows.AllowBreakAcrossPages = False
  .AllowAutoFit = False
  .Rows(1).HeadingFormat = SettingHeaderRow
'  .Font.Name = "Calibri"
  If SettingHeaderBorderOff Then  'Turn all but bottom border off
    With Selection.Tables(1).Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
  End If
End With

What am I doing wrong?

macropod · #7 01-29-2012, 03:35 PM

Hi Jennifer,

For the font, you need to use:
.Range.Font.Name = "Calibri"

For the borders, I deleted the reference to 'Selection.Tables(1)'. I don't get any errors with:

Code:

  If SettingHeaderBorderOff Then  'Turn all but bottom border off
    With .Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
  End If

I did have to comment out the 'If' test, as there doesn't seem to be a SettingHeaderBorderOff variable setting to test in the code you've supplied.

Jennifer Murphy · #8 01-29-2012, 05:40 PM

OK. I changed the font code to use the ".range". That now works. Is there some way I should have known that or been able to figure it out?

I deleted the 'Selection.Tables(1)', but I still get the error.

I've posted the entire macro below. It has the right variables declared. I made a bunch of changes to the way the options are selected and reported, but the core code is the same.

Code:

Sub MyTableSettings()
 
Const MyName = "MyTableSettings"
Dim SettingBreakOld, SettingAutoFitOld, SettingFontSw, SettingFontOld, _
    SettingHdrRowSw, SettingHdrRowOld, SettingHdrBrdrOffSw, SettingSelectTableSw _
    As Boolean  'Setting variables
Dim Msg, MsgAutoFit, MsgBreak, MsgHdrRow, MsgFont, MsgBorder As String
 
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) <> True Then
  MsgBox "The cursor is not in a table", vbOKOnly, MyName
  Exit Sub
End If
 
'Get user options
If vbYes = MsgBox("Take all defaults?", vbYesNo, MyName) Then
  SettingHdrRowSw = True
  SettingHdrBrdrOffSw = True
  SettingFontSw = True
  SettingSelectTableSw = True
Else
  SettingHdrRowSw = (vbYes = MsgBox("Set row 1 as header?", vbYesNo, MyName))
  SettingHdrBrdrOffSw = (vbYes = MsgBox("Header row borders off?", vbYesNo, MyName))
  SettingFontSw = (vbYes = MsgBox("Set font to Calibri?", vbYesNo, MyName))
  SettingSelectTableSw = (vbYes = MsgBox("Leave table selected?", vbYesNo, MyName))
End If
 
With Selection.Tables(1)
 
  'Save the old settings
  SettingBreakOld = .Rows.AllowBreakAcrossPages
  SettingAutoFitOld = .AllowAutoFit
  SettingHdrRowOld = .Rows(1).HeadingFormat
  SettingFontOld = .Range.Font.Name
 
  'Set the rest of the new settings
  .Rows.AllowBreakAcrossPages = False                 'Do not allow rows to break across pages
    MsgBreak = "Break = " & .Rows.AllowBreakAcrossPages & " (was " & SettingBreakOld & ")" & vbCrLf
  .AllowAutoFit = False                               'Do not autofit columns
    MsgAutoFit = "Autofit = " & .AllowAutoFit & " (was " & SettingAutoFitOld & ")" & vbCrLf
  .Rows(1).HeadingFormat = SettingHdrRowSw            'Set header row on/off
    MsgHdrRow = "Header row = " & .Rows(1).HeadingFormat & " (was " & SettingHdrRowOld & ")" & vbCrLf
 
  If SettingFontSw Then         'Set font?
    .Range.Font.Name = "Calibri"
    MsgFont = "Font = " & .Range.Font.Name & " (was " & SettingFontOld & ")" & vbCrLf
  Else
    MsgFont = ""
  End If
 
  If SettingHdrBrdrOffSw Then  'If no header row borders, turn all but bottom off
    With .Rows(1)
      .Borders(wdBorderTop).LineStyle = wdLineStyleNone
      .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
      .Borders(wdBorderRight).LineStyle = wdLineStyleNone
      .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
'      .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    End With
    MsgBorder = "Header row borders off"
  Else
    MsgBorder = "Header row borders unchanged"
  End If
 
  If SettingSelectTableSw Then Selection.Tables(1).Select 'Leave table selected?
 
  'Report the results
  Msg = MsgAutoFit & MsgBreak & MsgHdrRow & MsgFont & MsgBorder
  MsgBox Msg, vbOKOnly, MyName
 
End With
End Sub

macropod · #9 01-29-2012, 06:15 PM

Hi Jennifer,

The code runs just fine for me, on both Word 2003 & 2010. Maybe there's a problem with your document or your Word 2007 installation. Repairing Word (Word Options|Resources|Diagnose) may help.

I note that you've declared your variables thus:

Code:

Dim SettingBreakOld, SettingAutoFitOld, SettingFontSw, SettingFontOld, _
    SettingHdrRowSw, SettingHdrRowOld, SettingHdrBrdrOffSw, SettingSelectTableSw _
    As Boolean  'Setting variables
Dim Msg, MsgAutoFit, MsgBreak, MsgHdrRow, MsgFont, MsgBorder As String

You should be aware that this means that all except SettingSelectTableSw and MsgBorder are Variants.

I also note that, with the borders for example, you have an option to delete them or leave them alone. IMHO it would be better to allow the user to choose to have or not have the borders, including on a table that presently lacks them. In that case, you'd need something like:

Code:

  Dim Bdr As Long
  If SettingHdrBrdrOffSw Then  'If no header row borders, turn all but bottom off
    Bdr = 0
    MsgBorder = "Header row borders off"
  Else
    Bdr = 1
    MsgBorder = "Header row borders on"
  End If
  With .Rows(1)
    .Borders(wdBorderTop).LineStyle = Bdr
    .Borders(wdBorderLeft).LineStyle = Bdr
    .Borders(wdBorderRight).LineStyle = Bdr
    .Borders(wdBorderVertical).LineStyle = Bdr
    .Borders(wdBorderDiagonalDown).LineStyle = Bdr
    .Borders(wdBorderDiagonalUp).LineStyle = Bdr
  End With

BTW, you could reduce:
If SettingSelectTableSw Then Selection.Tables(1).Select 'Leave table selected?
to:
If SettingSelectTableSw Then .Select 'Leave table selected?

Jennifer Murphy · #10 01-29-2012, 06:50 PM

Dang. You are right about the Dim statement. I looked on the MSFT website:

http://msdn.microsoft.com/en-us/library/7ee5a7s1(v=vs.80).aspx

and found this paragraph.

Different Types. You can specify different data types for different variables by using a separate As clause for each variable you declare. Alternatively, you can declare several variables to be of the same type by using a common As clause. Each variable takes the data type specified in the first As clause encountered after its variablename part.

It sure seems to me to say that I can group them as I did. They even give this example:

Code:

Dim a, b, c As Single, x, y As Double, i As Integer
' a, b, and c are all Single; x and y are both Double

I guess it works in VB but not in VBA. Another cute little gotcha from MSFT.

I ran the diagnostics. It found no errors. I don't need those 2 lines, so I'll just delete them.

As for the borders, I would do as you suggest it I were writing this for general use. It's just for me and mainly for one project where I need the tables to look a certain way. I don't need every option.

Anyway, thanks for all your help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to call current PC date and/or current PC year	KIM SOLIS	Excel	2	11-04-2011 06:09 PM
Select Text in Table but Table Gets Selected Too	RBusiness	Word	1	06-07-2011 04:26 PM
Access to the property of the current table	b0x4it	Word VBA	2	05-26-2011 06:25 AM
Table - Check Boxes - Create Select All	lajohn1963	Word Tables	2	09-25-2010 11:18 AM
Auto insert current month's name and current year	Styler001	Word	4	01-25-2010 06:40 PM