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.
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
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.
Rich (BB code):
[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