code cleanup - anyone interested enough to take a look?

A

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
Office Version
  1. 365
Platform
  1. Windows
I have been making some changes to a petty cash worksheet over the last couple of weeks and have now got it to where its almost ready to be set free in the wilds of our finance department. however, its doing some weird stuff where i thought it should be rather straight forward. it has four sheets, two of which are hidden - the first contains lookup tables, business rules, validation lists, etc and the second one is a clipboard of sorts used to split particular expenses before writing them back to the main spreadsheet.

The main spreadsheet has two buttons on it: one for creating the pivot table Summary of the expenses, and the second to reset sheet level event code and/or clear the contents to begin a new month.

firstly, if there is nothing on the data sheet, and the reset sheet button is pressed, i can get a run time error stating that I can not do this on a protected sheet, but then when i press it again, it runs without hassle.

secondly, while mucking about with the protection function i have, i noticed that everytime i ran the reset macro, my ComboBox change event ran through a couple of tiimes.

the following is in my Data Input sheet:

[face=Calibri]Option Explicit

Private Sub ComboBox1_Change()
SetProtection (False)
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Range("b4").Select
SetProtection (True)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Origin As Excel.Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False

End With

If Sheets("Parameters").Range("MacroOff") Then Exit Sub
If Sheets("Parameters").Range("HasCalc") Then Exit Sub
If ActiveCell.Row < 10 Then Exit Sub
If ActiveCell.Column > 12 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")
'/// "ORIGIN" VALUE (Receipt amount):
Case 5 'Amount
If Origin = 0 Then
Sheets("Parameters").Range("HasCalc") = True
SetProtection (False)
Origin.Offset(0, 1).Formula = ""
Origin.Offset(0, 2).Formula = ""
SetProtection (True)
Sheets("Parameters").Range("HasCalc") = False
Else
Sheets("Parameters").Range("HasCalc") = True
SetProtection (False)
Range("F" & ActiveCell.Row).FormulaR1C1 = "=+ROUND(IF(RC4=""GST"",RC5/11,0),2)"
Range("G" & ActiveCell.Row).FormulaR1C1 = "=+RC[-2]-RC[-1]"
SetProtection (True)
Sheets("Parameters").Range("HasCalc") = False
End If
'/// "ORIGIN" VALUE (GL Expense Account):
Case 8 'Expense Account
Select Case Left(Origin, 1)

'///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 3:
Case "3"
'///if selected unibis branch starts with CH 999, clear this branch
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
'///also, if Activity is other than 2,3,or 4, clear Activity
If Left(Origin.Offset(0, 2), 1) <> "2" And _
Left(Origin.Offset(0, 2), 1) <> "3" And _
Left(Origin.Offset(0, 2), 1) <> "4" Then
Origin.Offset(0, 2) = ""
End If
'///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 8:
Case "8"
'///same as above for Branch
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
'///also, if Activity is other than 5,5,7,or 8, clear Activity
If Left(Origin.Offset(0, 2), 1) <> "5" And _
Left(Origin.Offset(0, 2), 1) <> "6" And _
Left(Origin.Offset(0, 2), 1) <> "7" And _
Left(Origin.Offset(0, 2), 1) <> "8" Then
Origin.Offset(0, 2) = ""
End If
'///IF FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 0:
Case "0"
If Left(Origin, 1) = "0" Then
'///set activity and set unibis branch
Origin.Offset(0, 2) = "0 - 999"
Origin.Offset(0, 1) = "CH 999 - Balance Sheet"
End If
End Select
'/// "ORIGIN" VALUE (Unibis Branch):
Case 9 'Location
Select Case True
'///when user selects this Unibis branch
Case Origin = "QF QHR - TN TOLL REF- Administration"
'/// if activity is other than 6 or 8, clear
If Left(Origin.Offset(0, 1), 1) <> "6" And _
Left(Origin.Offset(0, 1), 1) <> "8" Then
Origin.Offset(0, 1) = ""
End If
'///when unibis location starts with "CH"
Case Left(Origin, 2) = "CH"
'///set activity to this value
Origin.Offset(0, 1) = "7 - CRP"
End Select
Case 10 'Activity
Select Case True
'///when Activity starts with 0
Case Left(Origin, 1) = "0"
'///if GL account starts with anything but 0, clear GL Account
'///and input CH 999 - BS in Unibis Branch
If Left(Origin.Offset(0, -2), 1) <> "0" Then
Origin.Offset(0, -2) = ""
Origin.Offset(0, -1) = "CH 999 - Balance Sheet"
End If
'///when Activity starts with 2,3,or 4
Case Left(Origin, 1) = "2", _
Left(Origin, 1) = "3", _
Left(Origin, 1) = "4"
'///if GL starts with other than 3, clear GL
If Left(Origin.Offset(0, -2), 1) <> "3" Then
Origin.Offset(0, -2) = ""
End If
'///when Activity starts with 5,6,7, or 8
Case Left(Origin, 1) = "5", _
Left(Origin, 1) = "6", _
Left(Origin, 1) = "7", _
Left(Origin, 1) = "8"
'///if GL starts with other than 8, clear GL
If Left(Origin.Offset(0, -2), 1) <> "8" Then
Origin.Offset(0, -2) = ""
End If
End Select
End Select
Sheets("Parameters").Range("ActiveCellColumn") = Target.Column
If Target.Row > 9 Then Sheets("Parameters").Range("ActiveCellRow") = Target.Row

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

[/face]

while the following is in two modules: the first:[face=Calibri]Option Explicit

Sub CalculatePivot()
If Sheets("Parameters").Range("MacroOff") Then Exit Sub
SetProtection (False)

Application.Run "splittercode"

SetProtection (True)
Sheets("Summary").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Sub ResetSheet()
Dim Response As Variant

If Sheets("Parameters").Range("MacroOff") Then Exit Sub
SetProtection (False)
Response = MsgBox("Clear all records from sheet?", vbCritical + vbYesNoCancel, "Reset")
Select Case Response
Case vbNo
Sheets("Parameters").Range("HasCalc") = False
Exit Sub
Case vbCancel
Exit Sub
Case vbYes
Range("A10").Select
Selection.CurrentRegion.Select
Range("A10:j" & LTrim(Str(Selection.Row + Selection.Rows.Count - 1))).ClearContents

Range("CashOnHand").ClearContents
Range("InputDate").ClearContents
Range("InputWeek").ClearContents
Range("A10").Select
Sheets("Parameters").Range("HasCalc") = False
SetProtection (True)
MsgBox "Sheet has been reset"
End Select
Calculate
End Sub

Public Sub SetProtection(sProtect As Boolean)
If Sheets("Parameters").Range("MacroOff") Then Exit Sub
Select Case sProtect
Case True
If Sheets("Parameters").Range("Protection") Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Case False
ActiveSheet.Unprotect
End Select
End Sub
[/face]
and the second (which deals with expenses to be split)[face=Calibri]Sub splittercode()
'Sub SearchForString()

Dim LSearchRow As Integer
'Dim LCopyToRow As Integer
Dim ToBeSplit As Range
Dim SplitTerm As String
With Application
.ScreenUpdating = False

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 10
SplitTerm = Sheets("Parameters").Range("Spliff").Value

With Sheets("Data Input")
While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("I" & CStr(LSearchRow)).Value = SplitTerm Then

'Select row in Data Input to copy
Set ToBeSplit = .Range(Cells(CStr(LSearchRow), 1), Cells(CStr(LSearchRow), 10))
SplitVal = .Range("i" & LSearchRow).Address

ToBeSplit.Copy '///copy the active range

Sheets("SplitSht").Range("SplitOne").PasteSpecial (xlPasteValuesAndNumberFormats) '///paste it to splitter range
Application.CutCopyMode = False

Calculate

.Range(SplitVal).Validation.Delete '///remove validation from column K

With ToBeSplit
.ClearContents '///empty range holding expense to be split
.Copy '///copy this empty range
.Offset(1).EntireRow.Insert shift:=xlDown '///paste empty range down one row
.Offset(1).EntireRow.Insert shift:=xlDown '///paste empty range down another row. result is three empty lines with only
End With

Application.CutCopyMode = False
Sheets("SplitSht").Range("SplitResults").Copy
Sheets("Data Input").Cells(LSearchRow, 1).PasteSpecial (xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False

LSearchRow = LSearchRow + 3

Else
LSearchRow = LSearchRow + 1

End If

Wend
End With

Exit Sub

Err_Execute:
MsgBox "An error occurred."
.ScreenUpdating = True
End With

End Sub
[/face]


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">BRANCH:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TREF MORNINGSIDE-ADMIN</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Authorised Signature</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">VENDOR No:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2PETTY27</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">RECONCILIATION</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">DATE:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-left: 1px solid black;text-decoration: underline;;">V2011.3</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CLAIMED VOUCHERS</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> - </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WEEK NO:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TOTALS</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CASH ON HAND (Incl Unclaimed Vouchers)</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Receipt Total</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GST</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Excl. Total</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FLOAT</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> 500.00 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;border-right: 1px solid black;text-decoration: underline;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CASH ON HAND RECONCILIATION</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> 500.00 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Purchase Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Purchased By</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Invoice /Receipt Amount</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GST</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Excl. Amount</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GL Expense A/C</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Unibis Branch</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Activity</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Data Input</p><br /><br />
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Geez, ajm
With No code tags AND No indenting....AND a Duplicate post, probably unlikely anyone will venture into it !
But this has given you a bump anyway !
 
Last edited:
Upvote 0
Bugger! I can never seem to get my code tags right. normally, i manually wrap the code but this time i thought i might try an addin i picked up which converts the code to BB code. obviously should have wrapped it in tags also.as for duplicate post, i must have hit the big red button twice inadvertantly while my machine crashed at this end.

perhaps mrexcel could delete the other post. i will request now.

thanks for the heads up.
 
Upvote 0
with tags:

Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim Origin As Excel.Range
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

    End With

    If Sheets("Parameters").Range("MacroOff") Then Exit Sub
    If Sheets("Parameters").Range("HasCalc") Then Exit Sub
    If ActiveCell.Row < 10 Then Exit Sub
    If ActiveCell.Column > 12 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")
        '/// "ORIGIN" VALUE (Receipt amount):
    Case 5    'Amount
        If Origin = 0 Then
            Sheets("Parameters").Range("HasCalc") = True
            SetProtection (False)
            Origin.Offset(0, 1).Formula = ""
            Origin.Offset(0, 2).Formula = ""
            SetProtection (True)
            Sheets("Parameters").Range("HasCalc") = False
        Else
            Sheets("Parameters").Range("HasCalc") = True
            SetProtection (False)
            Range("F" & ActiveCell.Row).FormulaR1C1 = "=+ROUND(IF(RC4=""GST"",RC5/11,0),2)"
            Range("G" & ActiveCell.Row).FormulaR1C1 = "=+RC[-2]-RC[-1]"
            SetProtection (True)
            Sheets("Parameters").Range("HasCalc") = False
        End If
        '/// "ORIGIN" VALUE (GL Expense Account):
    Case 8    'Expense Account
        Select Case Left(Origin, 1)

            '///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 3:
        Case "3"
            '///if selected unibis branch starts with CH 999, clear this branch
            If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
                Origin.Offset(0, 1) = ""
            End If
            '///also, if Activity is other than 2,3,or 4, clear Activity
            If Left(Origin.Offset(0, 2), 1) <> "2" And _
               Left(Origin.Offset(0, 2), 1) <> "3" And _
               Left(Origin.Offset(0, 2), 1) <> "4" Then
                Origin.Offset(0, 2) = ""
            End If
            '///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 8:
        Case "8"
            '///same as above for Branch
            If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
                Origin.Offset(0, 1) = ""
            End If
            '///also, if Activity is other than 5,5,7,or 8, clear Activity
            If Left(Origin.Offset(0, 2), 1) <> "5" And _
               Left(Origin.Offset(0, 2), 1) <> "6" And _
               Left(Origin.Offset(0, 2), 1) <> "7" And _
               Left(Origin.Offset(0, 2), 1) <> "8" Then
                Origin.Offset(0, 2) = ""
            End If
            '///IF FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 0:
        Case "0"
            If Left(Origin, 1) = "0" Then
                '///set activity and set unibis branch
                Origin.Offset(0, 2) = "0 - 999"
                Origin.Offset(0, 1) = "CH 999 - Balance Sheet"
            End If
        End Select
        '/// "ORIGIN" VALUE (Unibis Branch):
    Case 9    'Location
        Select Case True
            '///when user selects this Unibis branch
        Case Origin = "QF  QHR - TN TOLL REF- Administration"
            '/// if activity is other than 6 or 8, clear
            If Left(Origin.Offset(0, 1), 1) <> "6" And _
               Left(Origin.Offset(0, 1), 1) <> "8" Then
                Origin.Offset(0, 1) = ""
            End If
            '///when unibis location starts with "CH"
        Case Left(Origin, 2) = "CH"
            '///set activity to this value
            Origin.Offset(0, 1) = "7 - CRP"
        End Select
    Case 10    'Activity
        Select Case True
            '///when Activity starts with 0
        Case Left(Origin, 1) = "0"
            '///if GL account starts with anything but 0, clear GL Account
            '///and input CH 999 - BS in Unibis Branch
            If Left(Origin.Offset(0, -2), 1) <> "0" Then
                Origin.Offset(0, -2) = ""
                Origin.Offset(0, -1) = "CH 999 - Balance Sheet"
            End If
            '///when Activity starts with 2,3,or 4
        Case Left(Origin, 1) = "2", _
             Left(Origin, 1) = "3", _
             Left(Origin, 1) = "4"
            '///if GL starts with other than 3, clear GL
            If Left(Origin.Offset(0, -2), 1) <> "3" Then
                Origin.Offset(0, -2) = ""
            End If
            '///when Activity starts with 5,6,7, or 8
        Case Left(Origin, 1) = "5", _
             Left(Origin, 1) = "6", _
             Left(Origin, 1) = "7", _
             Left(Origin, 1) = "8"
            '///if GL starts with other than 8, clear GL
            If Left(Origin.Offset(0, -2), 1) <> "8" Then
                Origin.Offset(0, -2) = ""
            End If
        End Select
    End Select
    Sheets("Parameters").Range("ActiveCellColumn") = Target.Column
    If Target.Row > 9 Then Sheets("Parameters").Range("ActiveCellRow") = Target.Row

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

and then at module level:

Code: 
Option Explicit

Sub CalculatePivot()
    If Sheets("Parameters").Range("MacroOff") Then Exit Sub
    
    With Application
        .EnableEvents = False
    End With
    
    SetProtection (False)

    Application.Run "splittercode"

    SetProtection (True)
    Sheets("Summary").Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
    
    With Application
        .EnableEvents = True
    End With
    
End Sub


Code: 
Sub ResetSheet()
    Dim Response As Variant
    
    With Application
        .EnableEvents = False
    End With

    If Sheets("Parameters").Range("MacroOff") Then Exit Sub
    SetProtection (False)
    Response = MsgBox("Clear all records from sheet?", vbCritical + vbYesNoCancel, "Reset")
    Select Case Response
    Case vbNo
        Sheets("Parameters").Range("HasCalc") = False
        Exit Sub
    Case vbCancel
        Exit Sub
    Case vbYes
        Range("A10").Select
        Selection.CurrentRegion.Select
        Range("A10:j" & LTrim(Str(Selection.Row + Selection.Rows.Count - 1))).ClearContents
        Range("CashOnHand").ClearContents
        Range("InputDate").ClearContents
        Range("InputWeek").ClearContents
        Range("A10").Select
        Sheets("Parameters").Range("HasCalc") = False
        SetProtection (True)
        MsgBox "Sheet has been reset"
    End Select
    Calculate
    
    With Application
        .EnableEvents = True
    End With
    
End Sub

Code: 
Public Sub SetProtection(sProtect As Boolean)
    If Sheets("Parameters").Range("MacroOff") Then Exit Sub
    Select Case sProtect
    Case True
        If Sheets("Parameters").Range("Protection") Then
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    Case False
        ActiveSheet.Unprotect
    End Select
End Sub

It will be much simpler to pm the workbook around the place if anyone wants to have a look.
 
Last edited:
Upvote 0
ajm said:
perhaps mrexcel could delete the other post. i will request now.
Click to expand...
I haven't deleted the other thread but I have locked it with a pointer to this one.

In relation to your question about editing to add code tags, you need to do that within the 10 minute (not 20 minute, unless there has been a recent change) edit window. Otherwise, as you have done here, you need to just post again clarifying your original post.
 
Upvote 0
To avoid having your ComboBox Change event firing make sure it's not bound to a worksheet range, ie the LinkedCell and ListFillRange properties are not set. You can use the DropButton Click event procedure to fill the List and the Change event procedure to populate a cell.

The alternative is to use a boolean variable to disable control events at appropriate places in your code. When you want to disable events set it to True and check it's setting at the beginning of the event, exiting if necessary.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
673
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
502
VBAEXCELNew
V
E
  • Question Question
VBA code clean up
Replies
9
Views
596
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
671
Micron
M

Forum statistics

Threads
1,224,893
Messages
6,181,616
Members
453,057
Latest member
LE102024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top