InputBox

[ScotyB]

Hi all,

in the attached workbook, each person has a shift assigned (via drop-down list) and hours for that shift (via LOOKUp in the table to the right) - this works well most of the time because the hours for each shift are fixed. Two of the shift types (ie: Admin and Training) have varying hours and as a quick fix for that I've again used a drop-down list in cells R4 and R19 - what this does however is to change ALL the values in cells B4 to H59 which is not always elegant if it's only 2 hours on Tuesday but changes to 6 hours on Friday.

My question then, is this...

is there a way to precede the LOOKUP function in those cells to check whether "Admin" or "Training" appears, and if it does, to then provide an input box for the user to stipulate the hours?

So, for example, in cell B4 where the code appears

Please Login or Register  to view this content.

it would look something like IF B4 = Admin OR Training then do an InputBox, else just do the LOOKUP

Many thanks in advance.

AJ

[JBeaucaire]

1) Right-click the sheet tab and select VIEW CODE to open the sheet module
2) Paste in this event macro to handle these manual prompts:

Please Login or Register  to view this content.

3) Close the VBEditor and save the workbook. Now try making a change to B4 and see what happens based on your various choices.

[JBeaucaire]

This version eliminates the formula altogether, since we're using a macro might as well let it put in the automatic values, too.

Please Login or Register  to view this content.

[reachhari]

Hi,

If i understood your issue correctly. Here is my suggestion..

I have made some small changes in your Roaster which can solve your issue. I have created 6 more columns and changed the formulas accordingly. For training or admin or any dept you have the option to input the working hours it will take the respective hours accordingly.

Check out and let me know

Regards,
Hariharan R

[ScotyB]

Wow, thanks everyone. All of those solutions worked and being a "put formula in cell" type of guy I tried reachari's solution because it was something I was familiar with.

I was more than a little daunted when looking at Jbeaucaire's solutions since I have little to zero experience with vba coding but after trying it and seeing how well it worked, I think I'm gonna have spend more time learning to do things the vba way.

Again, many, many thanks for the help - that has saved me many hours of hair-pulling (and I don't have much of that left to work with, lol)

Cheers,

AJ