Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    is this possible in Excel? i'm guessing VBA

    hi all..

    ok i'm lost as to where to even start here so any help will be greatly appreciated.

    here is what I want to do, I'd like to be able to choose a task from a drop down menu in a cell, have that change the colour of the cell (this I can do using a list with conditional formatting), but then I want the text in the cell to not show so I can enter a number value in and have a formula work on that number value while the cell remains colour coded..

    is this even possible?

    as far as I can tell, what I want to do is conditionally format the colour of a cell from a list box, then keep the value of the colour and eraze everythign else then manually type in a number. with me?

    please help.. I'm new to VBA, I'll be playing with recording Macros to see how Excel creates the cells and formatting so I can try and work this out..

    thanks in advance

    oh the whole thing has to be triggered by the choice from the list.
    Rob

    Edit:
    as I need to keep the original list available to change the colour formatting and I can only have one cell holding one lot of information I am thinking I will probably need to add a cell to the side for the numerical value, not what I was hoping for but it'll work ;-)
    Last edited by ShuggyShug; 2016-04-30 at 20:06.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    It would be possible to do it once (if you used a macro) but you can't arbitrarily jump between a dropdown and coloured cell behaviour depending on what the user is thinking they want to do.

    A second cell to separate the tasks is the simplest option. It is easy to colour the text to match the background fill so the cell 'appears' empty.

    Alternatively, you could create a ribbon button that presents the dropdown choices and have a macro apply the colour coding based on the results of that selection. This is way more work than you are likely to want to do so I wouldn't recommend it unless the useability is an issue on the easy option.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    ShuggyShug (2016-05-01)

  4. #3
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you Andrew,

    you have confirmed my thoughts, I was hoping I was wrong to be honest but Oh well.. not everything is possible :-) now I just need to work out how to change the colour of the "new" cell based on the drop down choices of the cell to its left so that both appear the same colour.

    once again thank you

    Edit:

    Think I have this solved using conditional formatting, thank you for pointing me in the right direction!! now all I have to do is remake my entire spreadsheet lol Ohh the joys

    Edit:

    Wait.. nope.. I can change the colour of the cell to the right based on the contents of the cell to the left, but what I would really like to do is change the colour based on the colour of the cell to the left. (the reason is that the sheet is a calendar and i have the weekends automatically highlighted, though when I added the new column they are now white whether it is a weekend or not). I should add the weekends are mostly blank though they do have a dropdown option as well.

    any help? with a pointer or two?

    thanks in advance.
    Last edited by ShuggyShug; 2016-05-01 at 08:00.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    ShaggyShug,

    Here is the code to do what you want to do. In this example, I have enabled cells A1 through A10. Clicking on any of those cells will open a dropdown listing the tasks pulled from column H. Selecting a task changes the cell to a certain color and allow the input of a number to be used in your formula. To clear the color and the cell value, select the cell then select <blank> in the dropdown. The code uses a listbox called ListBox1 placed on the sheet with its visibility set to False

    HTH,
    Maud

    Shaggy1.png

    Place in the worksheet module"
    Code:
    Private Sub ListBox1_Click()
    With ListBox1
        .Visible = False
        Select Case .ListIndex
            Case 0
                ActiveCell.Interior.Pattern = xlNone
                ActiveCell = ""
                Exit Sub
            Case 1
                c = vbRed
            Case 2
                c = vbGreen
            Case 3
                c = vbBlue
            Case 4
                c = vbYellow
            Case 5
                c = vbMagenta
            Case 6
                c = vbCyan
            Case Else
                ActiveCell.Interior.Pattern = xlNone
                ActiveCell = ""
                Exit Sub
        End Select
        ActiveCell.Interior.Color = c
    End With
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Target.Interior.Pattern = xlNone
        ListBox1.Left = Target.Left
        ListBox1.Top = Target.Offset(1, 0).Top
        ListBox1.Width = Target.ColumnWidth * 5.5
        ListBox1.Value = ""
        ListBox1.Visible = True
        Exit Sub
    End If
    ListBox1.Visible = False
    End Sub
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    ShuggyShug (2016-05-01)

  7. #5
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you Maudibe,

    I have tried to use your code but getting the error "Run time Error '424' Object required" with the line "ListBox1.Left = Target.Left" Highlighted.. no drop downs appear in the cells either.

    I also tried the attachment, here i get no error codes but I also dont get the drop down menu in cells A1:A10. Have I done something wrong? Im using excel 2016.

    thank you for your help

    Rob

    Edit:

    its ok I think it may be me being an idiot.. I didn't put a listbox on the sheet to start with, I'll have a play with that and see if that helps.

    Rob

    Edit:

    IT WORKS !!
    Last edited by ShuggyShug; 2016-05-01 at 17:18.

  8. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    ShuggyShug,

    Glad you thought it through. Don't forget to set the listbox's visibility property to false else it will display when you first open the workbook.

    Maud

  9. #7
    New Lounger
    Join Date
    Apr 2016
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Maud,

    been a tad busy so my apologies for the late reply. Working a dream now :-)

    Rob

Posting Permissions

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