Select from dynamic ComboBox

[ElBombay]

Hello Board,

I want to write a SUB() that will be scalable and transportable but is best explained with a simple accounting example. First sheet in the workbook will be the General Journal, site of original entry. Each subsequent sheet will be a ledger acct such as Cash, A/P or Expense, e.g. I want the accounts for each entry (Petty Cash and Office Expense, e.g.) to be selected from a ComboBox called from the alt-F8 menu, created and displayed at whichever row is currently active, That selection will then be stored to a memory variable and its value will be saved to the current row in the Acct Name column. The ComboBox will be created with a "FOR/EACH ws in worksheets" loop. I would like to exclude the 1st sheet (GJ) from the selections available.

Thanks

 

[AFPathfinder]

Which type of combobox are you looking to use, validation list, a worksheet control (Form or ActiveX), or a userform? I'm confused on what you need the worksheet loop for. Can you post your code so far?

 

[ElBombay]

There is no code. 1st sheet is general journal. One of the columns entered is Account Name. Next two columns are Debit & Credit. Each subsequent sheet is an individual is a specific acct. A worksheet loop will read each of these. Collecting all sheets/Acct Names in a combo box and using that to populate the Account Name cell ensures that any acct name entered is valid and correctly spelled. I know my accts but this technique provides data integrity for the further uses I refer to in my opening post. I envision a drop-down menu appearing when the procedure is invoked. I m asking how to most efficiently accomplish this; that would involve guidance on the best type of combo box to use

 

[ElBombay]

AFPathfinder, Reviewing what I just posted, I should add that it seems a drop-down box offering only valid choices would be the simplest (and aesthetically pleasing) of my options but I'd be interested in any further observations you might have.

 

[AFPathfinder]

A data validation list (in-cell drop-down) would be the easiest to implement, but may not be as flexible of a solution. You would have lists referenced in a different location if the values in the drop-down won't change often. I typically have my lists in a hidden column or sheet that other users won't accidentally change.

A VBA userform could be used if that list changes often, but requires more work to implement as you envisioned. If you need to update your lists from the general ledger, but not show the items already listed on the balance sheets, VBA may be the way to go. You could do a mix where you use the in-cell drop-down lists, but VBA would update those lists and keep them relevant for the user.

Does the general ledger sheet identify the expense account (sheet name) of each expense?