how to make multiple Combo boxes feed off one change handler.

[superchew]

Hi. Newbi here. I have 6 combo boxes that each have a change handler that is a request for ID # to choose operator. how can I tighten this code up to feed off on handler?

Picking log-Rev6.xlsm

[Greg M]

Hi there,

Well I think it's true to say that your code could be made a LOT simpler!

I'm taking a look at it and I hope to be able to post something here in a day or two.

A question: Events seem to be disabled (and seldom re-enabled) in many places throughout the application, even by the Workbook_BeforeClose routine - is this intentional?

Regards,

Greg M

[Leith Ross]

Hello superchew,

You don't need the to use the 6 combo boxes and the worksheet event handler. A better approach is to use a VBA User Form. This can be called by each button.

Entry validation can be accomplished using a single macro. Using the active cell and the cell position of the command button, entries can be validated for the name, start, and stop times having been entered, and if the active cell is in a valid entry zone. By fully validating entries, you prevent errors and "buddy punching".

Once the entry is validated, the user form is displayed requesting the name and password. The names and passwords are checked against a list on another worksheet. This worksheet is "very hidden" which prevents the users from being able to unhide it from the ribbon. A list makes it easy to edit the names and passwords. Hard coding them into a routine is okay if you are masochist.

The attached workbook does all this. However, the workbook events have been modified and you will need to uncomment code in them. I did this to eliminate problems running the workbook on my machine. Have look and let me know what you think.

[superchew]

I am lost. it looks like you got rid of my combo boxes making it impossible for an EE to log in.

[superchew]

I believe it is intentional, but I am a newbie and have had alot of great people on here helping me out.

[Leith Ross]

Hello superchew,

All the employee needs to do is click a cell in the "Init" column and click "Start". The login form will be displayed. When finished, click "Stop". Does not get easier than that.

[superchew]

Ok Leith I see where you were going with this and like it. only issue is EE should only have to enter password when first logging into line. and secondaly job requires frequent start stop times, and you code does not seem to allow for this. Also multiple people might work on one line through out the batch.

[Leith Ross]

Hello superchew,

I can change it. Can you give a couple of examples?

[Greg M]

Hi again,

Take a look at the attached workbook and see if it does what you need.

One point - the passwords associated with the various employees have been moved to a hidden column (B) on the "Calculations" worksheet. The column can be displayed/hidden by using the "Show/Hide Passwords" button - you will be prompted for a password - the password to enter is the same as the one which you're currently using to protect the "Picking Log" worksheet.

I think you will find it a lot easier to synchronise passwords with employee names by using this approach.

Just for information, your original workbook contained 1,211 lines of code - the current version contains 463

Hope this helps - please let me know how you get on with it.

Regards,

Greg M



P.S. Each "Start" button is disabled until a name is selected in its associated combobox.

[superchew]

Greg so far this works awesome. i am just getting into it. One question how do i add names and passwords in the future

[Greg M]

Hi again,

Glad to hear that so far it's working well for you

As regards adding new names and passwords:

On the "Calculations" worksheet there's a Named Range ("tblEmployees") which refers to the cells A1:A90 - this contains the names of all the employees and is used to populate the dropdown lists of the various comboboxes. New names can be entered anywhere within this range.

Column B on this worksheet contains the passwords associated with each employee name and is normally hidden. To add a password for a new name, click on the "Show/Hide Passwords" button (cells C1:D2). This will display a dialog box into which you should enter the password which you are currently using to protect the "Picking Log" worksheet. After clicking on the "OK" button on the dialog box, column B will be displayed. Enter the password for the new employee name in the column B cell beside the employee name.

When all new passwords have been entered, click on the "Show/Hide Passwords" button - no password is required when column B is being hidden.

The previous version of the workbook linked the dropdown lists of the various comboboxes to the range A1:A75 on the "Calculations" worksheet - the attached version links the dropdown lists to the "tblEmployees" Named Range - i.e. A1:A90

Hope the above helps - please let me know how you get on with it.

Regards,

Greg M

[superchew]

Many Many thanks for you help. this code looks and works so well appreciate everything.

[Greg M]

Hi again,

You're very welcome. Thanks for your feedback - I'm very pleased that I was able to help.

Best regards,

Greg M

[superchew]

question... this works great on my PC, but when I open it on the PC that is will be used on, after entering techs name when i hit start button it saids password supplied in incorrect. check caps (which are not on) and insure proper password. Not sure whats happening. also where is the password located so if I want to change it.

[superchew]

not sure if this helps but the computes in the work space is using excel 2007