Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Iím using Excel 2007 and on a spreadsheet I presently am using Data Validation to populate a cell with comments I use for my reports and then using conditional formatting to change the fill colors for the particular selection. The problem I found is I am limited to the amount of text I can populate using data Validation. Could I use a Combo Box (form control) with code to achieve this same feature? I not totally sure how to populate or to change the color file.
    Below is a sample of the fill color with comment.
    Code:
    Sub ColourIt()
     Dim Pwd As String
     Dim Tint As Long
     On Error GoTo ExitHere:
     Pwd = ""
     ActiveSheet.Unprotect Password:=Pwd
     Select Case ff.Result
     Case "Inspected", "Functional", "Inspected - Appears Functional", _
       "Operational"
      Tint = RGB(171, 229, 123)
     Case "Not Inspected", "Non-Accessible", "Limited Inspection"
      Tint = RGB(190, 190, 190)
     Case "Not Present", "Absent / None", "Missing"
      Tint = RGB(120, 220, 255)
     Case "Damaged / Repair Needed", "Non-Functional", "Recommend Repairs", _
       "Monitor Conditions", "Unsatisfactory", "Unacceptable", _
       "Attention Recommended", "Attention Required", "Defective", _
       "Further Inspection Needed", "Further Inspection Recommended", _
       "Investigate Further"
      Tint = RGB(255, 220, 110)
     Case "Safety Hazard", "Safety Concern", "Dangerous"
      Tint = RGB(250, 100, 95)
     End Select
     cl.Range.Shading.BackgroundPatternColor = Tint
    ExitHere:
     ActiveSheet.Protect , Password:=Pwd
    End Sub

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    The "ff.Result" looks like it came from Word VBA (FormField.Result), which isn't going to compile or run in Excel.

    Excel Help indicates you can populate a forms List or ComboBox control like this:

    Worksheets(1).Shapes(1).ControlFormat.List = Array("cogs", "widgets", "sprockets", "gizmos")

    and you should be able to return the index of the selected item like this:

    lngIndex = Worksheets(1).Shapes(1).ControlFormat.ListIndex

    Gary

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Gary
    Greatly Appreciated, I'll give it a try
    Also the previous code was from a word doc that I have, I was just using as a sample, good eye

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The "proper" code to address a forms listbox in Excel is:

    ActiveSheet.ListBoxes("List Box 1").List = Array("a", "b", "c")

    You won't find these in Help, because MSFT has designated these as "old" stuff, they are hidden members. Silly IMO.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel 5 Help lists there methods for the Listbox:

    AddItem
    BringToFront
    Copy
    CopyPicture
    Cut
    Delete
    Duplicate
    RemoveAllItems
    RemoveItem
    Select
    SendToBack

    And these properties:

    Application
    BottomRightCell
    Creator
    Display3DShading
    Enabled
    Height
    Index
    Left
    LinkedCell
    LinkedObject
    List
    ListCount
    ListFillRange
    ListIndex
    Locked
    MultiSelect
    Name
    OnAction
    Parent
    Placement
    PrintObject
    Selected
    Top
    TopLeftCell
    Value
    Visible
    Width
    ZOrder

    This is what it says about the List property:

    Applies To

    DrawingObjects, DropDown, DropDowns, ListBox, ListBoxes

    Description

    Returns or sets the text entries in a list box or drop-down list box, as an array of strings (Syntax 1), or returns or sets a single text entry (Syntax 2). Returns an error if there are no entries in the list. Read-write.

    Syntax 1

    object.List

    Syntax 2

    object.List(index)

    Elements

    object

    Required. The object to which this property applies.

    index

    Required for syntax 2. The text entry number.

    Remarks

    Setting this property clears any ListFillRange.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Jan
    Greatly Appreciated

Posting Permissions

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