Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2010
    Location
    Los Angeles, Califorania, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a template that has a table in it. In one of the cells I have a control that is a drop down box. There are 3 choices in the drop down box and one choice that is just blank spaces in case the user doesn't want any of the other 3 choices. I put an underline code around the control so that the choices would be underlined after the template is completed, but I don't want the blank spaces to show up as an underline, so I need a macro to associate with this template that will search the cells and look for six blank spaces and delete the underline. Or is there a better way to do this? Can anyone help? Thanks in advance.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,913
    Thanks
    0
    Thanked 192 Times in 176 Posts
    Hi Tammy,

    I see you've posted the same question at:
    http://www.techsupportforum.com/micr...underline.html
    Please read:
    http://www.excelguru.ca/node/7

    As for the solution to the problem, I'm assuming you're referring to a dropdown formfield. In that case, you could call the following macro from the dropdown's 'on exit' property:
    Code:
    Sub ToggleUnderline()
    Dim Pwd As String
    Pwd = "YourPassword"
    With ActiveDocument
      .Unprotect (Pwd)
      With .FormFields("Dropdown1")
        If Trim(.Result) = "" Then
          .Range.Font.Underline = wdUnderlineNone
        Else
          .Range.Font.Underline = wdUnderlineSingle
        End If
      End With
      .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #3
    New Lounger
    Join Date
    Feb 2010
    Location
    Los Angeles, Califorania, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Macropod,

    Thanks for the macro as well as the advice. In my template I now want to make the table larger and have several cells with this dropdown box occurrance, so if I put 5 dropdown boxes in, would I have to duplicate the statement below 5 times in the macro or is there a way to put a wildcard character in to search for all Dropdowns?

    With .FormFields("Dropdown1")
    If Trim(.Result) = "" Then
    .Range.Font.Underline = wdUnderlineNone
    Else
    .Range.Font.Underline = wdUnderlineSingle
    End If
    End With

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,913
    Thanks
    0
    Thanked 192 Times in 176 Posts
    Hi Tammy,

    There are two ways you can approach this - have a:
    1. different sub for each dropdown. For that, you'd need to have subs named something Sub ToggleUnderline1(), ToggleUnderline2(), etc, each calling their own dropdowns, via .FormFields("Dropdown1"), .FormFields("Dropdown2"), etc, respectively, or;
    2. generic sub that processes all dropdowns, in which case, each formfield calls the same sub. For that, you might use code like:
    Code:
    Sub ToggleUnderline()
    Dim Pwd As String, oFld As Field
    Pwd = "YourPassword"
    With ActiveDocument
      .Unprotect (Pwd)
      For Each oFld In .Fields
      With oFld
        If .Type = wdFieldFormDropDown Then
          If Trim(.Result) = "" Then
            .Range.Font.Underline = wdUnderlineNone
          Else
            .Range.Font.Underline = wdUnderlineSingle
          End If
        End If
      End With
      .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    To add a third option, based on Paul's code, you could also have it generic, but only work on the current dropdown:

    Code:
    Sub ToggleUnderline()
    Dim Pwd As String
    Pwd = "YourPassword"
    With ActiveDocument
      .Unprotect (Pwd)
      With .FormFields(Selection.Range.FormFields(1).Name)
        If Trim(.Result) = "" Then
          .Range.Font.Underline = wdUnderlineNone
        Else
          .Range.Font.Underline = wdUnderlineSingle
        End If
      End With
      .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
    End With
    End Sub
    Gary

  7. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,913
    Thanks
    0
    Thanked 192 Times in 176 Posts
    Hi Gary,

    That won't work if the formfield doesn't have a name. The following works without needing a name:
    Code:
    Sub ToggleUnderline()
    Dim Pwd As String
    Pwd = "YourPassword"
    With ActiveDocument
      .Unprotect (Pwd)
      With .FormFields(Selection.Range.Fields(1).Index)
        If Trim(.Result) = "" Then
          .Range.Font.Underline = wdUnderlineNone
        Else
          .Range.Font.Underline = wdUnderlineSingle
        End If
      End With
      .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Even better - thanks!

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •