Userform problems with merged worksheet cells

Hi,

I have Userform and It worked fine, but now I included merged cells on worksheet and nothing works anymore. Userfrom is for employees work schedule.

Userform has combobox to select a day in month, and then listbox gets filled with data for that day.

First problem is that when selecting index from Combobox, worksheet should move to a selected day to left side of Userfom. It worked, but now merged cells are problem.

Second problem is that listbox doesn't show entries properly. Under label ""Job title" there should entries for each employee, now It shows only entry for first one.

And third problem is that I don't know how to fill listbox with start/end values, that should be under "Start/End" label. For now I only have Start time in listbox, but there should be both together like "06:00-14:00". I tried with this but not working:

Please Login or Register  to view this content.

I also noticed that Combobox doesn't show names correctly, 1st of month is listed twice, with different days

Any help appreciated, thanks in advance !!

Hi Norie,

merged cell in Row 2 must remain, as I allready have macro for creating months with merged cells, I don't want to change that.

Didn't notice about different dates, this is only a sample. But If you change first day to 1 Jan 1900, combobox still shows incorrectly.

Yes they are, but unfortunatelly I can't change that, only with VBA maybe, for time of opening Userform. Do you have any suggestions maybe ? Or show in sample what you meant with center across selection, maybe I can change macro for creating months ?

Ok,

I changed to across selection, but still nothing works. See attached sample !

Thanks,

I will check this solution, I hope It will work with my macro for creating months. Don't bother about holidays, I didn't include It in sample. I will let you know If everything works.

Thanks again !!

Hi Luka,

I think I was able to address all your known problems in your original file with the 'Merged Cells' based on the following assumptions:
a. Dates must be fixed at the top of Sheet1 (dates are for 1900 and should be 2014).
b. Job Titles are only displayed on the 1st day of the month and not for each day.
c. When a time is 0:00, display for that time on the UserForm should be blank.

Lewis

Changes in red:

Please Login or Register  to view this content.

Hi Luka,

You might want to try adding the following code, that will do the following when you 'Double Click' on a column in a 'Month' Worksheet:
a. Set the focus (top left cell) on row 2 for that date
b. Open up UserForm1 with data for that date.

Lewis

To implement this you need to add the following:

Add the following code to the UserForm1 module:

Please Login or Register  to view this content.

Add the following code to the Sheet1 module. When you create new months, the code will automatically replicate in the Sheet for each month.

Please Login or Register  to view this content.

Add the following in a NEW Ordinary Code module:

Please Login or Register  to view this content.

Hi LJMetzger,

I tried your code & prefer your solution much better. Just one problem:

a. When you enter some data in first day, that data is showed always on Listbox (except Start/End values) - I want Listbox to show data only for selected day in combobox. It works for first day, but not for other days.

I like your "double-click" event, looks like I'm going to delete cmdbuttons for Userform.

I applied your code in this sample, check It yourself ! I added "Refresh" button !

Looks like this line was a problem :

Please Login or Register  to view this content.

I commented It, and now It works. What have I missed, will that affect something else not to work ?

Hi Luka,

I apologize for not testing my previous file enough. I think this file will fix the problems you had. I made changes to the following modules in the
attached file in an attempt to simplify, and increase capabilitites:
a. ThisWorkbook
b. Sheet1
c. UserForm1 and UserForm1 code
d. Module2

Added items include:
a. Automatic Refresh when a time value or Job is changed. Manual refresh required for Name change.
b. Locked the top (dates) and left side (names) [and made row 1 hidden]
c. Simplified Refresh and added comments. It is now the only routine that updates the list box.
d. Added command buttons to UserForm to shift the dates on the spreadsheet to the left or right.

Please Login or Register  to view this content.

The above code from your last question should put the 'Job' in the list box.

Please Login or Register  to view this content.

Lewis

Hi Lewis,

again you did a lot of work

I checked all your code, but I have allready made Userform as told in previous post, and It suits my needs. About this :

a. Automatic Refresh when a time value or Job is changed. Manual refresh required for Name change.
b. Locked the top (dates) and left side (names) [and made row 1 hidden]
d. Added command buttons to UserForm to shift the dates on the spreadsheet to the left or right.

I love It & I will include It in my Userform, specially automatic refresh

thanks for all your help !!

I was happy to help. Your project has lots of interesting aspects to it. I figured you would use what you liked and/or could use, and not use other things which is fine.

Thanks for the rep points.

Lewis