Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining Worksheet Code with Module Code (XL2

    The following code is used to change the color of a column header when it is clicked (or selected). This code is in the Sheet1 Object:
    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Target.Cells(1)
    If .Row <> 7 Then Exit Sub

    Range("C7:F7").Interior.ColorIndex = 35
    Range("G7:L7").Interior.ColorIndex = 34
    Range("M7").Interior.ColorIndex = 37
    Range("N7:Q7").Interior.ColorIndex = 36
    Range("R7:T7").Interior.ColorIndex = 15

    Select Case Target.Cells(1).Column
    Case 3 To 6
    .Interior.ColorIndex = 4
    Case 7 To 12
    .Interior.ColorIndex = 28
    Case 13
    .Interior.ColorIndex = 28
    Case 14 To 17
    .Interior.ColorIndex = 27
    Case 18 To 20
    .Interior.ColorIndex = 16
    Case Else
    End Select
    End With
    End Sub</pre>


    In the picture, you can see that cell C7 has been clicked (it is colored). When a different cell on row 7 is clicked, C7 will return to its normal state , while the newly clicked cell will change.
    So, here's the question: How can I incorporate the sort code into the color change code ? When a header is selected, not only do I want the header cell to change color, I want it to trigger the appropriate sort code.
    My sort code is shown in the next post (just trying to keep these at a reasonable length).
    - Ricky

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Combining Worksheet Code with Module Code (XL2003)

    This is a bit of a continuation from another post involving <!post=Sorting with VBA,693072>Sorting with VBA<!/post>.
    <UL><LI>I have a database set up where the data begins in cell C8 - covers 18 columns to T.
    <LI>The data is in a named dynamic range ("data_list"). Currently, there are just 24 records (rows used).
    <LI>Row 32 and down are currently blank (unused). Row 32 would be the row for the 25th data record...
    <LI>Row 7 contains the column headers.
    <LI>Rows 1-6 contain titles, dates and the like.[/list]The picture shows a partial view with a couple of "dummy" records; my actual question will be on the next post.
    Attached Images Attached Images
    - Ricky

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining Worksheet Code with Module Code (XL2

    Here's the sort code; it is in a Module:

    <pre>Sub Sort_C()
    Range("data_list").Sort Key1:=Range("C8"), Key2:=Range("D8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_D()
    Range("data_list").Sort Key1:=Range("D8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_E()
    Range("data_list").Sort Key1:=Range("E8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_F()
    Range("data_list").Sort Key1:=Range("F8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_G()
    Range("data_list").Sort Key1:=Range("G8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_H()
    Range("data_list").Sort Key1:=Range("H8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_I()
    Range("data_list").Sort Key1:=Range("I8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_J()
    Range("data_list").Sort Key1:=Range("J8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_K()
    Range("data_list").Sort Key1:=Range("K8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_L()
    Range("data_list").Sort Key1:=Range("L8"), Order1:=xlDescending, Key2:=Range("M8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_M()
    Range("data_list").Sort Key1:=Range("M8"), Order1:=xlDescending, Key2:=Range("C8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_N()
    Range("data_list").Sort Key1:=Range("N8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_O()
    Range("data_list").Sort Key1:=Range("O8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_P()
    Range("data_list").Sort Key1:=Range("P8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_Q()
    Range("data_list").Sort Key1:=Range("Q8"), Key2:=Range("C8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_R()
    Range("data_list").Sort Key1:=Range("R8"), Order1:=xlDescending, Key2:=Range("C8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_S()
    Range("data_list").Sort Key1:=Range("S8"), Order1:=xlDescending, Key2:=Range("R8") _
    , Header:=xlNo
    Range("L4:R4").Select
    End Sub

    Sub Sort_T()
    Range("data_list").Sort Key1:=Range("T8"), Key2:=Range("S8"), Header:=xlNo
    Range("L4:R4").Select
    End Sub</pre>


    If the header for column S (S7) is selected (clicked), then S7 would change color and the data would sort using Sub Sort_S()
    - Ricky

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Worksheet Code with Module Code (XL2

    I don't think you should include Range("L4:R4").Select in a SelectionChange event procedure. It's rather disconcerting to select a cell and see the selection jump elsewhere.

    The code in the attached text file combines the coloring of the active cell and the sorting. The procedure SortIt can be moved to a standard module.
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining Worksheet Code with Module Code (XL2

    You're absolutely right about the jump. Originally, I was going to use the sort code for attaching it to "sort buttons", I needed somewhere "neutral" to go once the sorting was done. L4:R4 is a merged Report Title.
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    - Ricky

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Worksheet Code with Module Code (XL2

    By the way, although it's cool to see the sort order change as you move from cell to cell in row 7, you might consider using the Worksheet_BeforeDoubleClick event instead of the Worksheet_SelectionChange event. If you have large data tables and lots of formulas, changing the sort order each time the user accidentally clicks on or moves to a header cell may not be ideal. Double clicking a cell might be more convenient.
    If you take up this idea, don't forget to include a line

    Cancel = True

    in the code, so that the default action (start editing the cell) is canceled.

  7. #7
    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

    Re: Combining Worksheet Code with Module Code (XL2

    In addition to it being disconcerting, if you are going to select while running a selection change procedure, you should disable events so that the code does not become recursive (changing the selection, calls the event, which changes the selection, which calls the event, etc etc)

    Steve

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combining Worksheet Code with Module Code (XL2

    I appreciate that. For now, I don't see the data table getting that large. It is for an active employee listing. Some may have 20, while some may have 60; everyone who uses this could have a different number of records, but it will never get into the hundreds.

    I made one tweak to the code, only because the sort behavior on column 18 didn't work as I intended (my own fault). For Case 20, I changed the offset to (1, -12). This makes it sort on the data in another column. Works perfect now.

    I do foresee one potential problem: If an employee is lost for whatever reason, I can see someone simply deleting the data in that row, creating a blank row in the middle of the data table... I'm guessing the sort routines probably won't work as intended when a blank row is found?
    - Ricky

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combining Worksheet Code with Module Code (XL2

    If a row in the middle of the data is cleared, the next sort action will move that blank row to the bottom (the Data_List range will still include the rows below the cleared row, I presume).

Posting Permissions

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