Chosen option of drop-down changes another cell content

Weed · #1 01-19-2012, 04:03 AM

I am working with one-page document for our stakeholders where I need to activate an option of changing the content of 2nd cell once one of the option of drop-down field in 1st cell is chosen.

For example first cell contains Dropdown options for the question "This equipment is related to safety" - "Yes", "No" and "Partially". Correspondingly I want 2nd cell shows responses "Highest Urgency", "Normal urgency" and "High urgency" with changing font color (or even cell color) from Red for "Highest Urgency" to Green for "Normal urgency" or Yellow.

Or 1st cell contains dropdown questions "This equipment is for export" and "This equipment is for import", and once option chosen in first dropdown field 2nd field shows list of docs for this.

Thanks.

macropod · #2 01-26-2012, 02:44 PM

Hi Weed,

You would need 'On Exit' macros attached to the dropdowns for this.

For the "Highest Urgency", "Normal urgency" and "High urgency" dropdown, the code might be:

Code:

Sub ColorIt()
Dim Pwd As String
Pwd = ""
With ActiveDocument
  If .ProtectionType = wdAllowOnlyFormFields Then
    .Unprotect Password:=Pwd
  End If
  With .FormFields("Dropdown1")
    Select Case .Result
      Case "Normal urgency"
        .Range.Font.Color = wdColorBrightGreen
      Case "High urgency"
        .Range.Font.Color = wdColorYellow
      Case "Highest Urgency"
        .Range.Font.Color = wdColorRed
      Case Else
        .Range.Font.Color = wdColorAutomatic
    End Select
  End With
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
End With
End Sub

For the document list, the issue is somewhat more complicated, since you're wanting to populate one dropdown based on the results of another. Although that part is fairly straightforward, where does the list come from (eg by checking the contents of different folders, by reading a list fromn a separate file, or is it hard-coded)?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Naming a cell then linking with a certain amount of charachters looses content.	shabbaranks	Excel	1	12-27-2011 01:06 AM
using a cell content as a string with COUNTIF	geoffm	Excel	1	08-16-2011 11:31 AM
How can I delete the content of a cell in column if the cell value is more than 1000?	Learner7	Excel	2	06-27-2011 05:44 AM
Cell content splits	Mahen	Excel	2	05-15-2011 01:53 AM
Drop down box in a cell	Grapejuice	Excel	0	10-22-2007 07:40 PM