none
Determine control location and status in document RRS feed

  • Question

  • I have a Word (2013) document that is being generated from a template that has 16 tables. Tables 7 through 16 have identical layouts and all are irregular. Row 4 of tables 7 through 16 has 5 columns and the user wants to be able to click in each cell and change the color. I’ve added a command bar button in each cell that will allow the user to select the correct color. However, I’m at a loss as to how to code (VBA) it to determine which table and cell is being selected by the user. Can anyone provide me some help on this?

    Mark   
    Friday, August 22, 2014 3:46 PM

Answers

  • Thanks, Cindy. I was afraid that all of the command buttons would need a separate call to the "master" macro, but thought I would ask anyway. Everything works great. Thanks for all of your help.

    Mark

    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 2:51 PM
    • Unmarked as answer by 19Mark7 Tuesday, August 26, 2014 3:46 PM
    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 9:13 PM
    Tuesday, August 26, 2014 2:51 PM
  • Apparently, this doesn't want to work on a consistent basis, so I'm attaching how the macro as I have adapted this to fit my needs. When executing the macro, the cell's colors do not change.

    Sub Recolour()
    'Paul Edstein 08/25/14 MSDN Forum
    'Cindy Meister 08/26/14 MSDN Forum
     Dim cel1 As Word.Cell, cel2 As Word.Cell
     Dim rw As Word.Row
     Application.ScreenUpdating = False
     Set cel1 = Selection.Cells(1)
     Set rw = cel1.Row
     ActiveDocument.Unprotect
     ApplyShading cel1
     If cel1.Range.Information(wdEndOfRangeColumnNumber) < rw.Cells.Count Then
        Set cel2 = rw.Cells(cel1.Range.Information(wdEndOfRangeColumnNumber) + 1)
            ApplyShading cel2
     End If
     ActiveDocument.Protect Type:=wdAllowOnlyFormFields, noreset:=True
     End Sub

     Sub ApplyShading(cel As Word.Cell)
        With cel.Shading
          Select Case .ForegroundPatternColorIndex
                Case wdColorYellow
                    .ForegroundPatternColorIndex = wdColorBrightGreen
                Case wdColorBrightGreen
                    .ForegroundPatternColorIndex = wdColorAutomatic
                Case Else
                    .ForegroundPatternColorIndex = wdColorYellow
            End Select
        End With
     
     End Sub

    If I change  .ForegroundPatternColorIndex to .BackgroundPatternColor, then it appears to work correctly.

    Mark

     

    • Edited by 19Mark7 Tuesday, August 26, 2014 4:16 PM
    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 9:12 PM
    Tuesday, August 26, 2014 3:52 PM

All replies

  • Since there is no such thing as a 'command bar button' content control, it's quite unclear what you mean. Assuming you're using a text, combobox or dropdown content control, the simplest approach is to use a ContentControlOnExit macro like:

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim StrTxt As String
    With ContentControl.Range
      If .Information(wdWithInTable) = False Then Exit Sub
      With ActiveDocument.Range(0, .End).Tables
        If (.Count < 7) Or (.Count > 16) Then Exit Sub
      End With
      StrTxt = .Text
      With .Cells(1)
        If .RowIndex = 4 Then
          With .Shading
            If StrTxt = "1" Then .BackgroundPatternColorIndex = wdBrightGreen
            If StrTxt = "2" Then .BackgroundPatternColorIndex = wdYellow
            If StrTxt = "3" Then .BackgroundPatternColorIndex = wdRed
          End With
        End If
      End With
    End With
    End Sub

    In the above macro, the cell colour is determined by the content control's text (in this case, 1, 2 or 3).

    The alternative is to use a WindowSelectionChange event-driven macro that works on the same logic as the ContentControlOnExit macro.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Saturday, August 23, 2014 6:44 AM
  • Hi Mark

    <<the user wants to be able to click in each cell and change the color>>

    If the user has clicked in the cell, then the Selection will be in the cell. That makes it simple enough to identify the cell that should be formatted. For example:

    Selection.Cells(1).Shading.ForegroundPatternColorIndex = wdBlue

    By command buttons I assume you mean an ActiveX control. In that case, assuming the button is inserted as an InlineShape (no text wrap formatting):

        Dim rng As Word.Range
       
        Set rng = Me.InlineShapes(1).Range.Cells(1).Range
        rng.Shading.ForegroundPatternColorIndex = wdBrightGreen


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, August 24, 2014 9:43 AM
    Moderator
  • That makes it simple enough to identify the cell that should be formatted. For example:

    Selection.Cells(1).Shading.ForegroundPatternColorIndex = wdBlue


    Yes, but the OP also specified this is to occur only in "Row 4 of tables 7 through 16".

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Sunday, August 24, 2014 11:52 AM
  • Well, he can certainly build a check around it for the location - I shouldn't have to repeat code you already supplied for determining that? OTOH I find no mention of content controls in the original message, so no idea where that came from in your code...

    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, August 24, 2014 12:19 PM
    Moderator
  • I apologize, but what I meant was an active X command button inserted into the table cell and I didn't mean to mis-lead people that my approach was what I was determined to use. Obviously, I am looking for the best solution to my issue.

    Mark

     
    Monday, August 25, 2014 1:34 PM
  • In that case, you could add code to the document's 'ThisDocument' code module, like either:

    1. the following for each ActiveX button you want to have trigger the colour-change -

    Private Sub CommandButton1_Click()
    With Selection.Cells(1).Shading
      If .ForegroundPatternColorIndex = wdBlue Then
        .ForegroundPatternColorIndex = wdWhite
      Else
        .ForegroundPatternColorIndex = wdBlue
      End If
    End With
    End Sub

    or;

    2. the following 'CommandButton1_Click' sub to for each ActiveX button, with a generic 'Recolour' macro they all call -

    Private Sub CommandButton1_Click()
    Call Recolour
    End Sub

    Sub Recolour()
    With Selection.Cells(1).Shading
      If .ForegroundPatternColorIndex = wdBlue Then
        .ForegroundPatternColorIndex = wdWhite
      Else
        .ForegroundPatternColorIndex = wdBlue
      End If
    End With
    End Sub

    In either case, you'd change 'CommandButton1' to whatever name each ActiveX button has. Example 1 has the benefit of allowing each control to be independently coloured. Example 2 has the benefit of reusing code for simplicity and ease of maintenance.

    Do note that neither version affects the content control's colour. Extra code would be required for that.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, August 25, 2014 10:53 PM
  • Thanks, Paul! It looks like it works great to me. I do have two questions. First, is there a way to have a generic button click event that can identify which control has focus or will I need to have a separate click routine for each button (50 total buttons)? Secondly, aside from highlighting the cell where the control is located, is it possible to highlight the next adjacent cell to the right, also?

    Thanks for all of your help.

    Mark

    Tuesday, August 26, 2014 1:42 PM
  • Hi Mark

    You do need a separate macro for each button, but you can have each call a single macro with all the "real" code, as in Paul's second suggestion. FWIW I'd change that just a bit to have the call pass the Selection object to the "master" macro. Conceivably, the user's finger could "twitch" on the mouse button, changing the Selection in the document. That would be

    Private Sub CommandButton1_Click()
         Call Recolour Selection
    End Sub

    Sub Recolour(Sel as Selection)
        With Sel.Cells(1).Shading

     The reason you need a separate macro for each button is that Word considers ActiveX button on the document surface to be individual objects of the document, itself. That's why the code is (and must be) in the ThisDocument module (class). There's no way to make them "generic" or assign a single event procedure to them.

    << Secondly, aside from highlighting the cell where the control is located, is it possible to highlight the next adjacent cell to the right, also?>>

    Here's one possible way to do it. Note that I've built in a test to make sure there IS another cell to the right...

    Sub Recolour()
     Dim cel1 As word.Cell, cel2 As word.Cell
     Dim rw As word.Row
     
     Set cel1 = Selection.Cells(1)
     Set rw = cel1.Row
     ApplyShading cel1
     If cel1.Range.Information(wdEndOfRangeColumnNumber) < rw.Cells.Count Then
        Set cel2 = rw.Cells(cel1.Range.Information(wdEndOfRangeColumnNumber) + 1)
        ApplyShading cel2
     End If
     End Sub
     
     Sub ApplyShading(cel As word.Cell)
        With cel.Shading
          If .ForegroundPatternColorIndex = wdBlue Then
            .ForegroundPatternColorIndex = wdWhite
          Else
            .ForegroundPatternColorIndex = wdBlue
          End If
        End With
     
     End Sub
    


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, August 26, 2014 2:14 PM
    Moderator
  • Thanks, Cindy. I was afraid that all of the command buttons would need a separate call to the "master" macro, but thought I would ask anyway. Everything works great. Thanks for all of your help.

    Mark

    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 2:51 PM
    • Unmarked as answer by 19Mark7 Tuesday, August 26, 2014 3:46 PM
    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 9:13 PM
    Tuesday, August 26, 2014 2:51 PM
  • Apparently, this doesn't want to work on a consistent basis, so I'm attaching how the macro as I have adapted this to fit my needs. When executing the macro, the cell's colors do not change.

    Sub Recolour()
    'Paul Edstein 08/25/14 MSDN Forum
    'Cindy Meister 08/26/14 MSDN Forum
     Dim cel1 As Word.Cell, cel2 As Word.Cell
     Dim rw As Word.Row
     Application.ScreenUpdating = False
     Set cel1 = Selection.Cells(1)
     Set rw = cel1.Row
     ActiveDocument.Unprotect
     ApplyShading cel1
     If cel1.Range.Information(wdEndOfRangeColumnNumber) < rw.Cells.Count Then
        Set cel2 = rw.Cells(cel1.Range.Information(wdEndOfRangeColumnNumber) + 1)
            ApplyShading cel2
     End If
     ActiveDocument.Protect Type:=wdAllowOnlyFormFields, noreset:=True
     End Sub

     Sub ApplyShading(cel As Word.Cell)
        With cel.Shading
          Select Case .ForegroundPatternColorIndex
                Case wdColorYellow
                    .ForegroundPatternColorIndex = wdColorBrightGreen
                Case wdColorBrightGreen
                    .ForegroundPatternColorIndex = wdColorAutomatic
                Case Else
                    .ForegroundPatternColorIndex = wdColorYellow
            End Select
        End With
     
     End Sub

    If I change  .ForegroundPatternColorIndex to .BackgroundPatternColor, then it appears to work correctly.

    Mark

     

    • Edited by 19Mark7 Tuesday, August 26, 2014 4:16 PM
    • Marked as answer by 19Mark7 Tuesday, August 26, 2014 9:12 PM
    Tuesday, August 26, 2014 3:52 PM