Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have a Control Form Combo box on several worksheets. From the Combo I can select an upper or lower case lettter and the active cell is coloured accordingly and this works fine. I now wish to add an action for 'L' and 'l' and have amended the code accordingly (please see below) and I have also changed the Combobox Listfillrange to accomodate the extra entries.

    My problem is that if I select the 'L' or the 'l' from the Combobox it inserts the letter OK but does not change the font or cell colour. I have tried experimenting with different colours but it just does not work, any ideas please

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        For Each cel In Range("C6:AG1227").Cells
            If IsError(cel.Value) Then
                ' do you want to colour these?
            Else
                Select Case UCase(cel.Value)
                    Case "H", "h"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 3
                    Case "S", "s"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 51
                    Case "M"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 26
                    Case "P"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 9
                    Case "U", "u"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 1
                    Case "A", "a"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 16
                    Case "B", "b2"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 13
                    Case "T", "t"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 25
                     Case "C", "c"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 49
                     Case "L", "l"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 46
                    Case "X", "x"
                        cel.Font.ColorIndex = 2
                        cel.Interior.ColorIndex = 2
    Case Else
                        cel.Font.ColorIndex = 1
                End Select
                End If
        Next cel
    End Sub
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Selecting an item from the combobox does NOT trigger the "Change Event", so the code will not run after you use the combo box. You would have to manually change a cell to make the code run this way.

    What you could do is to copy the code to a general module and give it a name. Then you could have the worksheet change event call this macro, which will then work as you have it now. Then you can right-click the combo box and assign the macro to it so it runs when the combo box is called.

    Steve


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By the way, since you're checking UCase(cel.Value), there's no point in including lower case letters - UCase(cel.Value) will never be a lower case letter. Instead of

    Case "H", "h"

    it is sufficient to use

    Case "H"

    and similar for the other letters.

    Steve has already explained that you need to use the code differently. As a Worksheet_Change event procedure it is horribly inefficient, since the code will work through each and every cell in the range C6:AG1227 whenever the user edits ANY cell in the workbook.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Selecting an item from the combobox does NOT trigger the "Change Event", so the code will not run after you use the combo box. You would have to manually change a cell to make the code run this way.

    What you could do is to copy the code to a general module and give it a name. Then you could have the worksheet change event call this macro, which will then work as you have it now. Then you can right-click the combo box and assign the macro to it so it runs when the combo box is called.

    Steve
    Hi Steve

    Thanks for the input. I have been using this for a couple of years and the way it works is that a user double clicks the relevant cell and that makes the ComboBox visible, the user then selects one of the letters from the Combo selection and the cell that has been double clicked is populated by that letter and the cell background colour changes and the letter is inserted in white (you can of course just type in the lettter but I use the Combo as it makes the user beleive that is the only way it works and then then can only select a lettter from the Combo)

    All I wanted to do was to add a couple of days as the Company has introduced Lieu days when people have accrued enough hours and this is working OK insofar as the Combo selection of 'L' or 'l' is placed into the cell but the background colour is not working but all of the others do. I can live without it being coloured in but was just trying to understand why everything else worked and this did not.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    By the way, since you're checking UCase(cel.Value), there's no point in including lower case letters - UCase(cel.Value) will never be a lower case letter. Instead of

    Case "H", "h"

    it is sufficient to use

    Case "H"

    and similar for the other letters.

    Steve has already explained that you need to use the code differently. As a Worksheet_Change event procedure it is horribly inefficient, since the code will work through each and every cell in the range C6:AG1227 whenever the user edits ANY cell in the workbook.
    Hi Hans

    If for example the user has selected H his personal summary on a seperate page updates and deducts 1 day from his holiday allocation, and if h is selected the same thing happens but it deducts 1/2 a day. If I change this to case 'H' would that still work?

    I will copy the worksheet and try to change the 'Worksheet_Change' event procedure but in all honesty there never seems to be any delay and I am adminstering 5 countries, 12 departments and 90 people. :-) expect a barrage of questions as I change and play about with it.

    Thanks as usual
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by stevehocking View Post
    If for example the user has selected H his personal summary on a seperate page updates and deducts 1 day from his holiday allocation, and if h is selected the same thing happens but it deducts 1/2 a day. If I change this to case 'H' would that still work?
    The Select Case in the code that you posted is only used to change the colouring of the cell, it has nothing to do with the calculation of holiday deductions.

    Since you have Select Case UCase(cel.Value), the code will NEVER have to check "h" or "s" or "u" etc. UCase converts the value of the cell to upper case.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I can't see anything different about the code for L, so are you sure that it's actually just L and not for example L with a leading or trailing space?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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