Filtering a HeaderRowRange list

This code picks up all of the table headers and presents them to a combobox.
Many of the column names on that list are irrelvant to the task of the combobox and present clutter and confusion.
Is their anyway to filter this list before it is presented to the combobox?

Set oTbl = Sheet1.ListObjects(1)
    With Me
        .ComboBox1.List = Application.WorksheetFunction.Transpose(oTbl.HeaderRowRange)

Thank You for having a look

Scott

Hi Scott,

The code you used only seems to work with a continuous range. If there are gaps between columns try something like the following (tested and working):

  Const sGoodCOLUMNS = "E:F,H:K"

  Dim myRange As Range
  Dim r As Range

  Dim oTbl As ListObject

  Set oTbl = Sheet1.ListObjects(1)

  Set myRange = Intersect(oTbl.HeaderRowRange, Range(sGoodCOLUMNS))
  
  Me.ComboBox1.Clear
  For Each r In myRange
    Me.ComboBox1.AddItem r.Value
  Next r

  Set myRange = Nothing
  Set oTbl = Nothing

Lewis

Hi Scott,

Very nice Spreadsheet design and ditto for the UserForm Design.

I think your problems can be solved as follows:
a. Add a helper row (that can be hidden) above row 2 in your table. In each cell above a category to be excluded you would put an 'X' or some other character or group of characters to indicate that that category would not be included in the UserForm. Any category with an 'X' above it would not be included in the ComboBox List.

b. To remove items from the list, is doable, but a little more complicated, because when you change categories in a ComboBox, the old value must be recycled into the list. One way to solve this problem is to:
(1) initially put all the valid categories in a two dimensional array before populating the ComboBoxes. The first index would contain the category names. The second index would indicate whether the category is currently in use by a ComboBox.
(2) When a ComboBox selection is made use the .Tag property of the ComboBox to store the current ComboBox Value. The previous .Tag value (if not blank) would be the category to be recycled.
(3) In the two dimensional array mark the recycled value as available.
(4) Clear all ComboBox Lists.
(5) Create a new list for all ComboBoxes.

----------------------
The .Tag property is a little known item available in UserForm Controls. It is an item that the programmer can use to store anything. It is quite useful in situations like yours. There is very little documentation about the property.

I am very busy the next few days. If I don't see a response that you like, I will try to post possible updates to your file on Friday or Saturday (US Eastern time).

Lewis

Hi Scott,

I had time to implement the changes I mentioned in the previous post. My goal is to make you comfortable with your file. Anything that I did is negotiable, if you don't like the say I did something.See the attached copy of your file which implements the following:
a. Inserted new row 2 in Sheet 'Data'. It is currently VISIBLE, so you can see what I did.
b. Implemented code to seed the UserForm ComboBoxes with some Categories omitted. The omitted categories are NOT BLANK in row 2.
c. Implemented code to remove a Category from all ComboBox lists when the category is used by any ComboBox. If there was a VALUE in the ComboBox, that VALUE is placed in restored in all ComboBox lists.

The changes above were made to the UserForm module (changes in red), and the addition of module 'ModUserFormTransactions'.

UserForm 'Transactions' modified routines:

Private Sub BnResetAllocate_Click()


    'Disable UserForm Events
    bGblTransactionsInhibitUserFormEvents = True
    
    Me.TbDeposit.Value = "0.00"
    Me.TbSpend.Value = "0.00"
    Me.TbSplit1.Value = "0.00"
    Me.TbSplit2.Value = "0.00"
    Me.TbSplit3.Value = "0.00"
    Me.TbSplit4.Value = "0.00"
    Me.TbSplit5.Value = "0.00"
    Me.TbSplit6.Value = "0.00"
    Me.TbPosted.Value = "0.00"
    Me.TbUnposted.Value = "0.00"
    Me.CStore.Value = ""
    Me.CbAllocate1.Clear
    Me.CbAllocate2.Clear
    Me.CbAllocate2.Visible = False
    Me.Lb2.Visible = False
    Me.TbSplit2.Visible = False
    Me.CbAllocate3.Clear
    Me.CbAllocate3.Visible = False
    Me.Lb3.Visible = False
    Me.TbSplit3.Visible = False
    Me.CbAllocate4.Clear
    Me.CbAllocate4.Visible = False
    Me.Lb4.Visible = False
    Me.TbSplit4.Visible = False
    Me.CbAllocate5.Clear
    Me.CbAllocate5.Visible = False
    Me.Lb5.Visible = False
    Me.TbSplit5.Visible = False
    Me.CbAllocate6.Clear
    Me.CbAllocate6.Visible = False
    Me.Lb6.Visible = False
    Me.TbSplit6.Visible = False
    
    Me.LBPosted.Clear
    
    Call PopulateUserFormTransactionsComboBoxes
    Me.CbAllocate1.Value = ""

   'Enable UserForm Events
   bGblTransactionsInhibitUserFormEvents = False

End Sub

Private Sub CbAllocate1_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Me.CbAllocate2.Visible = True
    Me.TbSplit2.Visible = True
    Me.Lb2.Visible = True
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If
End Sub

Private Sub CbAllocate2_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Me.CbAllocate3.Visible = True
    Me.TbSplit3.Visible = True
    Me.Lb3.Visible = True
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If

End Sub

Private Sub CbAllocate3_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Me.CbAllocate4.Visible = True
    Me.TbSplit4.Visible = True
    Me.Lb4.Visible = True
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If

End Sub

Private Sub CbAllocate4_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Me.CbAllocate5.Visible = True
    Me.TbSplit5.Visible = True
    Me.Lb5.Visible = True
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If

End Sub

Private Sub CbAllocate5_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Me.CbAllocate6.Visible = True
    Me.TbSplit6.Visible = True
    Me.Lb6.Visible = True
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If

End Sub

Private Sub CbAllocate6_Change()

  If bGblTransactionsInhibitUserFormEvents = False Then
    Call ProcessUserFormTransactionsComboBoxChangeEvents
  End If

End Sub

Private Sub UserForm_Initialize()

Me.TbDeposit.Value = "0.00"
Me.TbSpend.Value = "0.00"
Me.TbSplit1.Value = "0.00"
Me.TbSplit2.Value = "0.00"
Me.TbSplit3.Value = "0.00"
Me.TbSplit4.Value = "0.00"
Me.TbSplit5.Value = "0.00"
Me.TbSplit6.Value = "0.00"
Me.TbPosted.Value = "0.00"
Me.TbUnposted.Value = "0.00"

Call PopulateUserFormTransactionsComboBoxes

End Sub

New module 'ModUserFormTransactions':

Option Explicit

Const nNumberOfComboBOXES = 6

Public bGblTransactionsInhibitUserFormEvents As Boolean

Public sGblComboBoxMasterList() As String
Public iGblCombBoxItemCount As Long

Sub PopulateUserFormTransactionsComboBoxes()

  Dim myRange As Range
  Dim r As Range
  
  Dim i As Long
  Dim iComboBoxNumber As Long
  
  Dim sComboBoxName As String
  Dim sValue As String
  Dim sIncludeExcludeSentinel As String
  
  'Initialize the Global Master ComboBox List Dynamic Array
  iGblCombBoxItemCount = 0
  ReDim sGblComboBoxMasterList(1 To 2, 1 To 1)
  
  'Use the Table Header Range to obtain a range containing the List
  'of all Possible categories
  'If the cell above a Category is BLANK, include that category in the List (by placing 'OK' in the ComboBox List Array)
  Set myRange = Sheets("Data").ListObjects(1).HeaderRowRange
  For Each r In myRange
  
    sValue = Trim(r.Value)
    sIncludeExcludeSentinel = Trim(r.Offset(-1, 0).Value)
  
    If Len(sIncludeExcludeSentinel) = 0 Then
    
      'Increase the capacity of the Global Dynamic Array
      iGblCombBoxItemCount = iGblCombBoxItemCount + 1
      ReDim Preserve sGblComboBoxMasterList(1 To 2, 1 To iGblCombBoxItemCount)
      
      'Put the Category in the Global Dynamic Array
      sGblComboBoxMasterList(1, iGblCombBoxItemCount) = sValue
      sGblComboBoxMasterList(2, iGblCombBoxItemCount) = "OK"
      Debug.Print r.Column, r.Value, r.Offset(-1, 0).Value
    End If
  Next r

  'Display the contents of the Dynamic Array in the Immediate Window (CTRL G in debugger)
  For i = 1 To UBound(sGblComboBoxMasterList, 2)
    'Debug.Print i, sGblComboBoxMasterList(1, i), sGblComboBoxMasterList(2, i)
  Next i

  'Clear each ComboBox
  'Put the list in each ComboBox
  For iComboBoxNumber = 1 To nNumberOfComboBOXES
    sComboBoxName = "CbAllocate" & iComboBoxNumber
    Transactions.Controls(sComboBoxName).Clear
    For i = 1 To UBound(sGblComboBoxMasterList, 2)
      'Debug.Print i, sGblComboBoxMasterList(1, i), sGblComboBoxMasterList(2, i)
      sValue = sGblComboBoxMasterList(1, i)
      sIncludeExcludeSentinel = sGblComboBoxMasterList(2, i)
      If sIncludeExcludeSentinel = "OK" Then
        Transactions.Controls(sComboBoxName).AddItem sValue
      End If
    Next i
  Next iComboBoxNumber

End Sub

Sub ProcessUserFormTransactionsComboBoxChangeEvents()

  Dim i As Long
  Dim iComboBoxNumber As Long
  
  Dim sComboBoxName As String
  Dim sActiveComboBoxName As String
  Dim sIncludeExcludeSentinel As String
  Dim sValue As String
  Dim sValueArray As String
  Dim sValueTag As String

  'Disable UserForm Events
  bGblTransactionsInhibitUserFormEvents = True
  
  'Get the name of the ComboBox that changed value
  sActiveComboBoxName = Transactions.ActiveControl.Name

  'Get the ComboBox 'Value' and 'Previous Value' (Tag Value)
  sValue = Transactions.Controls(sActiveComboBoxName).Value
  sValueTag = Transactions.Controls(sActiveComboBoxName).Tag
  
  'Remove the 'Value' from the Global Master ComboBox List Dynamic Array
  'Add the 'Tag Value' to  the Global Master ComboBox List Dynamic Array
  For i = 1 To UBound(sGblComboBoxMasterList, 2)
    'Debug.Print i, sGblComboBoxMasterList(1, i), sGblComboBoxMasterList(2, i)
    sValueArray = sGblComboBoxMasterList(1, i)
    If sValueArray = sValue Then
      sGblComboBoxMasterList(2, i) = ""
    End If
    If sValueArray = sValueTag Then
      sGblComboBoxMasterList(2, i) = "OK"
    End If
  Next i
  
  'Save the Current ComboBox 'Value' as the NEW 'Previous Value' (Tag Value)
  Transactions.Controls(sActiveComboBoxName).Tag = sValue
  
  'Clear each ComboBox
  'Save the value in the ComboBox
  'Put the value back in the ComboBox - accidentally erased by the 'Clear' Command
  'Put the list in each ComboBox
  For iComboBoxNumber = 1 To nNumberOfComboBOXES
    sComboBoxName = "CbAllocate" & iComboBoxNumber
    sValue = Transactions.Controls(sComboBoxName).Value
    Transactions.Controls(sComboBoxName).Clear          'This command Triggers a ComboBox Change Event
    Transactions.Controls(sComboBoxName).Value = sValue 'This command Triggers a ComboBox Change Event
    For i = 1 To UBound(sGblComboBoxMasterList, 2)
      'Debug.Print i, sGblComboBoxMasterList(1, i), sGblComboBoxMasterList(2, i)
      sValue = sGblComboBoxMasterList(1, i)
      sIncludeExcludeSentinel = sGblComboBoxMasterList(2, i)
      If sIncludeExcludeSentinel = "OK" Then
        Transactions.Controls(sComboBoxName).AddItem sValue
      End If
    Next i
  Next iComboBoxNumber
  
  'Enable UserForm Events
  bGblTransactionsInhibitUserFormEvents = False
  
End Sub

Lewis

Thank you so much for your time and thought; it appears we crossed in the mail. I wasn’t expecting you back until Friday so thought posting today would give you a heads up on what I had done before you expended so much effort.

I have been working on this since your suggestions on Sunday. With about 100 web sites, a couple of pieces of formula, an answered question and a kick in the butt, I created the posted solution. I will implement yours and study it… no doubt it is better but I would be ever so grateful if you could review the approach I took and let me know, in your opinion, its pros and cons... an exercise in learning.

Thanks

Scott


Expenses V3 Example Modified.xlsm

Hi Scott,

Thanks for the rep points. It's my pleasure to help, especially for someone who is trying to learn to fish.

I was able to find out the cause of some of your problems, but not the reason why the problem occurs.

...showed a problem wherein only the first textbox value associated with the comboboxes posts to the spreadsheet. That problem persists.

1. For some unknown reason the ComboBox .ListIndex value always had the value -1, even when ComboBoxes had lists in them. When I changed the test from .ListIndex to looking for the value in the ComboBox I had success. Your test for the first ComboBox always tested for the VALUE.

2. The use of 'Option Explicit' at the top of each code module is highly recommended. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer).
http://www.cpearson.com/excel/DeclaringVariables.aspx

Important excerpts of the code I used from Sub xxx follow:

Dim RowCount As Long
Dim response As Long

If Len(Trim(CbAllocate1.Value)) = 0 Then
    Exit Sub  ' nothing selected
  End If
    
  Set tbl = Sheets("Data").ListObjects(1)
  Set col = tbl.ListColumns(CbAllocate1.Value)
  Set findheader = col.Range
  Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(1).Value = TbSplit1.Value
    
  If Len(Trim(CbAllocate2.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate2.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit2.Value
  End If

If Len(Trim(CbAllocate3.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate3.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit3.Value
  End If
  
  If Len(Trim(CbAllocate4.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate4.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit4.Value
  End If
        
  If Len(Trim(CbAllocate5.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate5.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit5.Value
  End If
        
  If Len(Trim(CbAllocate6.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate6.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit6.Value
  End If

I will address the questions you raise inside your sample file in the following post.

Lewis

1. The 'masterlist in column A is a linked list to the header row of the table on the Data sheet. This allows that any changes in header names are posted to this list. The problem is that this list is not dynamic in that if I add a new column, it is not added to this list. I don't know how to address this problem

It's almost as if you have two Master Lists, which increases the likelihood that at least one of them is going to be wrong.

If you're going to have a Master List on Sheet 'formulas', one way to implement it would be:
a. There would be NO FORMULAS linking the lists on Sheet 'Data' and Sheet 'formulas'.
b. Column 'A' in Sheet 'formulas' would be the Master List. The items on the SpreadSheet in Sheet 'Data' and in the ComboBoxes would be in the order that is in Column 'A' on Sheet 'formulas'.
c. Each time Sheet 'Data' is activated, there would be a check to make sure all the columns are in the correct order, and there were the correct number of columns. If not, then the data in Sheet 'Data' would be manipulated to add/delete columns. If a column were deleted that had data, some adjustment would be necessary to keep the 'Balance' correct.
d. The data in columns 'D' thru 'I' on sheet 'formulas' would have to be verified and/or changed each time a Change is made in Column 'A' or 'B' of sheet 'formulas'.

It could be done, but it seems like a lot of duplication of work to me. It seems that it isn't that difficult to add Columns to the Table on Sheet 'Data', or to move columns around.

Lewis

Hi Scott,

Your test conditions are incorrect. Each test will always be true unless the Category Name is "-1". My test works as follows:
a. Look at the category name 'CbAllocate2.Value'
b. Remove leading and trailing spaces 'Trim'
c. If the number of characters remaining is greater than zero, it must be a category name
NOTE: If you type in a Category name that isn't in the list, the test will pass and you will get the same error that you had highlighted in yellow, because the 'CATEGORY DOES NOT EXIST' and there is no valid 'ListObjects' column.

If Len(Trim(CbAllocate2.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate2.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit2.Value
  End If

If Len(Trim(CbAllocate3.Value)) > 0 Then
    Set tbl = Sheets("Data").ListObjects(1)
    Set col = tbl.ListColumns(CbAllocate3.Value)
    Set findheader = col.Range
    Sheets("Data").Cells(Rows.Count, findheader.Column).End(xlUp).Offset(0).Value = TbSplit3.Value
  End If

-------------------
Your error isn't due to 'Option Explicit'. 'Option Explicit' saves a lot of headaches when you spell something wrong. As a test, comment out 'Option Explicit', and replace 'response ' with 'resp0nse ' (ZERO that looks a little like the letter 'o'). The logic is correct but the answer will always NOT be 'vbYes'. I run across errors like that all the time in other people's code, and in my code when 'Option Explicit' is accidentally erased. I once spent more than two weeks confused by a variable spelling error, when I knew the logic was correct.

If response = vbYes Then

becomes

If resp0nse = vbYes Then

I hope this helps.

Lewis