UserForm Checkbox Range/Cell Address

As part of a bigger project, I'm working to have users enable/disable a number of Custom Menu items. The theory here is the user will open a userform and then check the boxes/menus they want enabled and the Menu/Ribbon will then reload with those items.

So..... in getting started, and its been over a year since I have done much and forgot half of what I learned...

I'm setting up a UserForm that will list the Departments of my Company. I have them listed in Column A on worksheet. And I want to show a 0 or 1 in Column B depending on what they select. I will then move onto reading Column B when Excel opens to set the Visible status of a Menu item. This will all be part of Addin eventually, so I'm currently not to concerned with hiding the worksheet at the moment.

This is part of the Userform info right now, and I'm struggling to figure how to add the 1 or 0 into Column B for each item that is selected or unselected. It's probably easier than I'm making it out to be, but I seem to have forgetten so much.

Please Login or Register  to view this content.

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

Sure.

Here is a sample workbook, and its pretty rough at the moment. As the fog lifts, I think I need to maybe include a 2nd hidden field in the check box that includes the Items Cell Address. And then with the OnClick, read the address, use an Offset and then post a value to column B. But i'm still pretty rusty.

Note, I'll correct the fixed Column A range as well eventually, but still trying to remember how to do some things.

I made some further progress this morning and will try and work on this more later today.

I have the userform now using a multicolumn, multiselect. But I can't seem to figure out exactly how to Update Column B to show Yes or No for being 'Active'

Currently, with the OnClick, I am searching column A for a match on the selected items. But it seems to only update 1 item and not each selected items.

The updated code and sample attached.

Please Login or Register  to view this content.

I just can not seem to figure out why this doesn't work.

The userform listbox is a multicolumn, multiselect is generated from Columns A and B on the worksheet. And the user should then be able to select the items they want and Column B should then Update to Yes for a Checked Item and No for an Unchecked.

This in the onclick code.

Please Login or Register  to view this content.

When you change the listbox it deselects the selected items.

For example, let's say you've selected 'Audit' and 'HR'.

At the first iteration of this loop the cell next to 'Audit' will be set to Yes but it will also 'reset' the listbox so 'HR' will not be selected anymore.

Please Login or Register  to view this content.

One way of avoiding this would be to use List to populate the listbox rather than RowSource, however if you use List, or any method other than RowSource, to populate the listbox you can't have headers in the listbox itself.