MeisterConrad
New Member
- Joined
- Jan 17, 2017
- Messages
- 39
- Office Version
- 2007
I have some code that creates a bunch of Sheets, each with an identical Table.
The Sheets are all personal finance accounts, and the Table includes (several, but here specifically,) 2 column headings: BudgetCategory, which involves a drop-down list of BudgetCategories (housing, food, etc.); and BudgetSubCategory, whose drop-down menu is a list that is named in the BudgetCategory column - there is a list named "housing" (and "food", etc), but "housing" is also an item in the BudgetCategories list.
My trouble is in coding this dependent column.
I tried the INDIRECT referencing the cell by address, like "G11", but the reference needs to be for whatever row#. So, using the counter n, I tried "G" & this row. No good.
I could probably use a reference like ActiveCell(R0,C-1), where we make it look at the cell that is one column to the left of the selected cell.
I have no preference as to the method/type of reference, as long as it works for every row in the Table.
Alas, I am not good with syntax, and I keep doing it wrong somehow to the point where I don't know if it's my method of reference that is bad or if it's the way I say it that's messed up.
Can somebody straighten me out?
Thanks in advance.
VBA Code:
' Sub MakeSheets_Mod()
Dim c As Range 'c is one of the items in the LedgerLocList.
Dim n As Integer 'n is our counter for the loop. (n-3) also coincides with the Account# for the Sheet that gets created, as well as the name of the Table on each of those Sheets.
Dim lo As ListObject 'lo is the Table that becomes the LedgerTable.
' Set the counter's initial value
n = 3
' STARTING THE LOOP
' First, we add the sheet(s).
For Each c In Sheets("AccountInfoSheet").Range("LedgerLocList")
Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
'Starting the counter
n = n + 1
'Begin building the sheet.The Sheets are all personal finance accounts, and the Table includes (several, but here specifically,) 2 column headings: BudgetCategory, which involves a drop-down list of BudgetCategories (housing, food, etc.); and BudgetSubCategory, whose drop-down menu is a list that is named in the BudgetCategory column - there is a list named "housing" (and "food", etc), but "housing" is also an item in the BudgetCategories list.
My trouble is in coding this dependent column.
VBA Code:
' Create as Table
Range("D10:N12").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$D$10:$N$12"), , xlYes).Name = _
"Table" & n
Set lo = Range("Table" & n).ListObject
Range("Table" & n).Select
ActiveWorkbook.Names.Add Name:="LedgerTable" & n - 3, RefersToR1C1:="=Table" & n
'Enter Column Headings & format
Range("D10").FormulaR1C1 = "Re-ceipt?"
Range("E10").FormulaR1C1 = "Trans-action Date"
Range("F10").FormulaR1C1 = "Date Cleared"
Range("G10").FormulaR1C1 = "Budget Category"
Range("H10").FormulaR1C1 = "Budget Sub-Category"
Range("I10").FormulaR1C1 = "To / From"
Range("J10").FormulaR1C1 = "Trans-action Method (VIA)"
Range("K10").FormulaR1C1 = "Debits"
Range("L10").FormulaR1C1 = "Credits"
Range("M10").FormulaR1C1 = "Operating Balance"
Range("N10").FormulaR1C1 = "Actual Balance"
With Range("D10:N10")
.WrapText = True
.EntireRow.RowHeight = 52.5
End With
' Data Validation
Range("Table" & n & "[Budget Category]").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=BudgetingCategoriesList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'Range("Table" & n & "[Budget Sub-Category]").Select
'With Selection.Validation
' .Delete
' .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT("Table" & n & "[Budget Category]")"
' .IgnoreBlank = True
' .InCellDropdown = True
' .InputTitle = ""
' .ErrorTitle = ""
' .InputMessage = ""
' .ErrorMessage = ""
' .ShowInput = True
' .ShowError = True
'End WithI tried the INDIRECT referencing the cell by address, like "G11", but the reference needs to be for whatever row#. So, using the counter n, I tried "G" & this row. No good.
I could probably use a reference like ActiveCell(R0,C-1), where we make it look at the cell that is one column to the left of the selected cell.
I have no preference as to the method/type of reference, as long as it works for every row in the Table.
Alas, I am not good with syntax, and I keep doing it wrong somehow to the point where I don't know if it's my method of reference that is bad or if it's the way I say it that's messed up.
Can somebody straighten me out?
Thanks in advance.
Last edited:
