worksheet selection change event code tidy up

[ajm]

folks,

I have written a somewhat messy piece of code that seems to do the trick. however, it could always be neater, so please feel free to make suggestions.

the event targets entries into the worksheet and depending upon which column is the active cell ("Origin"), does different things. for example, if column 5 is the target, and the user enters a $0 value, then no tax is calculated in the adjacent cells. it goes on column by column to do its thing. I have added an extra column to the table and a new Select Case to the macro. The idea is for the user to be able to select Yes to split an expense item and the macro will copy the active line to the Parameters sheet, split out the amounts, then copy the three new lines back into place where the original record came from.

[face=Calibri]Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim Origin As Excel.Range
    Dim ToBeSplit As Range

    If Sheets("Parameters").Range("MacroOff") Then Exit Sub
    If Sheets("Parameters").Range("HasCalc") Then Exit Sub
    If ActiveCell.Row < 10 Then Exit Sub
    Set Origin = Sheets("Data Input").Cells(Sheets("Parameters").Range("ActiveCellRow"), _
                                            Sheets("Parameters").Range("ActiveCellColumn"))
    '///sets "Origin" to be the active cell

    Select Case Sheets("Parameters").Range("ActiveCellColumn")[/face]

'///SELECT CASE ARGUMENTS HERE FOR 5 - 10'///

[face=Calibri]'///for those expenses which must be split across FRL, QFX, and QRR
    Case 11    'Split
        Select Case Left(Origin, 1)
        Case "Y"
            Sheets("Parameters").Range("HasCalc") = True
            SetProtection (False)
            Set ToBeSplit = Sheets("Data Input").Range("A" & ActiveCell.Row & ":K" & ActiveCell.Row)
            ToBeSplit.Copy
            Sheets("Parameters").Range("SplitterOne").PasteSpecial (xlPasteValuesAndNumberFormats)
            Application.CutCopyMode = False

            Calculate

            With ToBeSplit
                .ClearContents
                .Copy
                .Offset(1).Resize(2, 1).EntireRow.Insert shift:=xlDown
            End With
            Application.CutCopyMode = False
            Sheets("Parameters").Range("SplitResults").Copy
            Sheets("Data Input").Range("A" & ActiveCell.Row).PasteSpecial (xlPasteValuesAndNumberFormats)
            Application.CutCopyMode = False
            Sheets("Parameters").Range("HasCalc") = False
        End Select
    End Select[/face]

I am using data validation to list Yes and No in the split column (Col K on Data Input). How do i delete this for the newly created entries so that the user doesn't then try to split one of the these new line items?

What could be done if the user decides to "unsplit" an item? for example, what if they have split the wrong item and need to consolidate the three new line items?

what can i do to tidy up the code itself?

cheers,

ajm

 

[ajm]

bump