Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am using Data Validation for my drop down list, using “List” and “source” =$A$132:$A$159 along with Conditional Formatting that changes different fill colors, depending on selection. Is there a way to use VBA with a command button that when selected would give the user a dialog box so that they could enter additional comment that would be added to existing list and would also be able to select a fill color for that new selection.

    Below I have a recorded Marco to give an idea of what I would like to achieve, but of course using a input box of some sort to make selections of text and color
    Also I'm using Excel 2007
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="My Name"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        Selection.FormatConditions.Add Type:=xlTextString, String:="My Name", _
            TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 4.99893185216834E-02
        End With
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 6214812
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Where do you want the additional comment to go? Do you want the user to pick colors from a list? If so, you must create a UserForm and a CommandButton. The CommandButton goes on the sheet, and its Click event displays the form. I would show you an example, but it's been so long since I actually put a UserForm into a VBA application that I'm having some trouble finding an example.

    In any case, you'll have to roll most of the code by hand. How good are your VBA skills?
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi David
    Where do you want the additional comment to go?
    if possible I would like new comment with the new formatted fill color to be added to existing cell that have been pre populated / formatted. So basically, adding to existing. Location of new entry will be determined by cell selection prior to adding new comment. Because I have other cells within this sheet that are also formatted using Data Validation / Conditional Formatting and are populated with different comments.
    Do you want the user to pick colors from a list?
    Yes Please
    If so, you must create a UserForm and a CommandButton. The CommandButton goes on the sheet, and its Click event displays the form. I would show you an example, but it's been so long since I actually put a UserForm into a VBA application that I'm having some trouble finding an example.
    I’m pretty good with UserForms I’ve constructed a few of those
    To open a UserForm I figure I’ll use “UserForm1.Show”
    What I was thinking was to attach UserForm code to a macro and attach it to an icon as a customized quick access toolbar and set it on top of the ribbon or maybe use UI Editor and create a tool bar ribbon.

    In any case, you'll have to roll most of the code by hand. How good are your VBA skills?
    I'm not all that, My skills are very limited
    and thank you for your assistance

Posting Permissions

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