folks, i have been asked to make a few changes to an existing file and it has a few macros in it. no problem, i say, lets have it. but..... i am struggling to understand the what and why of it. I understand the code snippets but when put together themeaning eludes me. I am hoping that someone may be able to walk through it with me. I can forward a workbook to anyone who wants to play also.
firstly, its a petty cash input sheet: data is entered from Row 10 down, across to Column J, the rows above contain summary information about the details in Row 10 and below. so in the sample below, the headers are in Row 9 and the data in row 10.
<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) --><table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2" align="left"><tr><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Purchase Date</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Purchased By</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Description</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Code</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Invoice /Receipt Amount</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>GST</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Excl. Amount</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>GL Expense A/C</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Unibis Branch</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Activity</b></font></th></tr><tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15-Sep-71</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">JO Mould</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Baby boy</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">GST</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">500,000.00</font></td><td bgcolor="#CCFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">45,454.55</font></td><td bgcolor="#CCFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">454,545.45</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">8726 - Catering - Inhouse meetings</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">QX 999 - TN Q- Balance Sheet</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3 - PUD</font></td></tr></table>
this is the code that is held in a worksheet selection change event:
this is the workbook open event:
then in a global module, there is a function to handle sheet protection:
big question is, apart from the select case looking at column 5, "Amount" what do the others actually achieve? pm me if you want to see the actual workbook. otherwise, please assist where you can. cheers, ajm
firstly, its a petty cash input sheet: data is entered from Row 10 down, across to Column J, the rows above contain summary information about the details in Row 10 and below. so in the sample below, the headers are in Row 9 and the data in row 10.
<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) --><table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2" align="left"><tr><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Purchase Date</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Purchased By</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Description</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Code</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Invoice /Receipt Amount</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>GST</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Tax Excl. Amount</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>GL Expense A/C</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Unibis Branch</b></font></th><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Activity</b></font></th></tr><tr><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">15-Sep-71</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">JO Mould</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Baby boy</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">GST</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">500,000.00</font></td><td bgcolor="#CCFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">45,454.55</font></td><td bgcolor="#CCFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">454,545.45</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">8726 - Catering - Inhouse meetings</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">QX 999 - TN Q- Balance Sheet</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">3 - PUD</font></td></tr></table>
this is the code that is held in a worksheet selection change event:
Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Origin As Excel.Range
'///sheet "Parameters" holds all lookup tables
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"))
Select Case Sheets("Parameters").Range("ActiveCellColumn")
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
Case 8 'Expense Account
Select Case Left(Origin, 1)
Case "3"
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
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
Case "8"
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
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
Case "0"
If Left(Origin, 1) = "0" Then
Origin.Offset(0, 2) = "0 - 999"
Origin.Offset(0, 1) = "CH 999 - Balance Sheet"
End If
End Select
Case 9 'Location
Select Case True
Case Origin = "NX NHN - NQX Administration", _
Origin = "QX QHO - Administration RAIL", _
Origin = "QF QHR - Administration REFRIG"
If Left(Origin.Offset(0, 1), 1) <> "6" And _
Left(Origin.Offset(0, 1), 1) <> "8" Then
Origin.Offset(0, 1) = ""
End If
Case Left(Origin, 1) = "CH"
Origin.Offset(0, 1) = "7 - CRP"
End Select
Case 10 'Activity
Select Case True
Case Left(Origin, 1) = "0"
If Left(Origin.Offset(0, -2), 1) <> "0" Then
Origin.Offset(0, -2) = ""
Origin.Offset(0, -1) = "CH 999 - Balance Sheet"
End If
Case Left(Origin, 1) = "2", _
Left(Origin, 1) = "3", _
Left(Origin, 1) = "4"
If Left(Origin.Offset(0, -2), 1) <> "3" Then
Origin.Offset(0, -2) = ""
End If
Case Left(Origin, 1) = "5", _
Left(Origin, 1) = "6", _
Left(Origin, 1) = "7", _
Left(Origin, 1) = "8"
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
End Sub
this is the workbook open event:
Code:
Option Explicit
Private Sub Workbook_Open()
'///if macros are off exit sub
If Sheets("Parameters").Range("MacroOff") Then Exit Sub
'/// check to see if mave after return is on, if yes, turn it off.
If Application.MoveAfterReturn Then
Sheets("Parameters").Range("MoveAfterSelection").Formula = "=true"
Application.MoveAfterReturn = False
Else
Sheets("Parameters").Range("MoveAfterSelection").Formula = "=false"
End If
End Sub
then in a global module, there is a function to handle sheet protection:
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
big question is, apart from the select case looking at column 5, "Amount" what do the others actually achieve? pm me if you want to see the actual workbook. otherwise, please assist where you can. cheers, ajm