Borders Macro

[SamanthaP]

Hi all

I'm trying to create a booking sheet that my colleagues and I can use to keep track of our desk allocations to clients; I'm fine with simple macro recording to fill colour, add a border and centre the text etc. (will be a shared workbook so using Alignment > Centre across selection rather than Merge Cells) but if a booking needs to be deleted, I want the borders to go back to how I have already set them up. T

he problem is that Desk 1 and Desk 14 have different borders from the other desks and if I try to format another row and hide it so that the macro can use the borders to format paint the selection using the hidden row, it messes up if the booking is made on a half hour because I have a solid border going down on the hour and a broken border going down the half hour.. I can't reset the whole row as we often have more than one booking a day on a desk and I only want to delete and reset the selection... any ideas would be greatly appreciated!!

I'd upload a document or screenshot but am a newbie so not sure how to do it :/

Am on Excel 2010.

 

[SamanthaP]

Hi Howard

I've deleted the code as instructed... can't tell you how pleased I am with this!! Does exactly what I need!!

Quick question; will I mess everything up if I delete Sheets 2 and 3? I notice that the sheet name 'BLANK' is used in some code so I'll edit as necessary if I change the name...

Samantha

PS: Claus/Howard both geniuses in my eyes!

 

[L. Howard]

Yes, you can delete them.

Howard

 

[L. Howard]

Hi Howard
I've deleted the code as instructed... can't tell you how pleased I am with this!! Does exactly what I need!!

Perhaps you are so pleased with what you have you may not want to mess with either of these two upgrades/add-ons.


https://www.dropbox.com/s/ma6t5jb7sq9dl94/Desk Yellow Monday Key Board Enter Drop Box.xlsm

https://www.dropbox.com/s/e2i77b4i6sxd7hq/Desk Green Monday Mouse Enter Drop Box.xlsm

Here are two nearly identical workbooks.

Both have an added feature for adding comments to the "Booked Hours" cells if you want to. Both allow you to bypass entering comments and both allow multiple comments to be added to each booking.

With "Green Monday Mouse Enter" (you will see the day Monday Highlighted in green to help you keep them straight in your mind) you use the mouse and the 'crtl' key to select the hour/s you want to add a comment. (If just one comment you don't need the ctrl key, just click the cell).

So really, all you do is select a booking and click on a name button, that name will be centered on selection, and you will get a prompt window asking if you want to add a comment/s Yes or No. If No clicked that is all. If Yes is clicked you will get another window, read the prompt and continue as advised. You can select say three cells and you will see the addresses in the window and when you click OK, you will be asked for an author for the 1st comment, select one or select the blank at the bottom of the list, then OK and you will be asked to enter the text for the comment. When done with the first comment > OK and you get the same drill for all the rest that you selected.

When you clear a booking you will be asked if you want to clear comments also, you can leave them and clear the color coding or clear both.

The "Yellow Monday Key Board Enter" (A yellow Monday here) works pretty much the same except you type in the cell address R5,T5 etc. from the keyboard The cells will need to comma delimited like F5,G5,J5. No trailing comma however like this F5,G5,J5,.

Give them a go and I'll be glad to try to answer any question you may have.

Howard

 

[Jim885]

Wow! That's what I call, dedication to the point of above and beyond.

Nice work Howard & Claus.

 

[L. Howard]

Thanks, Jim885,

Actually there is one more version I intend to toss into the ring, Claus distilled a small portion of the code to do the comments.

Makes it a bit easier and is error free as near as I can tell with my tests.

Regards,
Howard

 

[L. Howard]

Hi Samantha,

You should look at this version. Cleans up some little things that I noticed were occurring at times on previous versions. Like some unwanted 'over-runs' of booked hours. You book say two hours and two hours would be shaded, but the name was centered on more than the two shaded hours.

The Y column expansion is eliminated but you can still see the wide entries in the drop down.

The colors have been changed to a single method of .ColorIndex = 33. If you want to change the color assigned to the person, you can consult the color index list on Sheet 1 and make your pick and change only the number. (Two places in the code for each person)

A major change is that if you select to book just a half hour, then the name of the booker does not fit the selection. (Older versions)
This version will enter only the first initial of the booker if there is a half hour or an hour booked. Any larger selection will produce the full name.

In this new version you can only have one comment per selection. So if David wants to book three hours and have a comment relating to each hour, then he would have to select each hour separately and enter a comment for each.

https://www.dropbox.com/s/y7odmcdkv615cdr/DeskBookings with Merge Drop Box.xlsm

Hope it works well for you.

Regards,
Howard

 

[SamanthaP]

Hi Howard

Sorry - I haven't replied to this... didn't get any email notifications for the thread for some reason!?

I've been fiddling with the original one you sent me and finally have it pretty much ready to go (will make other, less-important tweaks later) but I've shared the workbook and NOTHING WORKS!?

I don't know what I've done wrong... I thought you could still run macros in shared workbooks as long as they don't violate the shared workbook limitations and whilst there is one macro at present that unprotects a worksheet (I can sort that), none of them will work!

Do you have any ideas please? I have to share the workbook so others in the office can edit at the same time... https://www.dropbox.com/home/SamanthaP#!/home/SamanthaP

Samantha

PS: will also have a look at this new fandangled one you have done... thank you

 

[SamanthaP]

Hi HOward

I've pinpointed the code that's stopping my macros from running in shared mode (.MergeCells = False) and removed this but now I'm getting the overrun problem that you described... I've fixed up all the comments as we need them so was wondering if you could tell me which bit of the code is the part I need to use to stop the overrun problem and also the Y column expansion... this is all I need now I think and it will be perfect!


Samantha

 

[L. Howard]

I tried to look at the version you posted a link to in post #27 but it was asking me to open an account or log in...?

If you have not looked and tried the latest version I sent (see the link in my post #26) I would suggest you do so.

If we are going to make modifications it should be to the same version and this last version seems to have addressed the quirks that were showing up in earlier versions.

As I recall, the merged cells within the code (not the formatting ON the sheet) stopped the overrun problem.

The column Y expansion is solved with a on the sheet merging of columns Y and Z.

I know very little about sharing workbooks. I'll see what I can learn about sharing workbooks and the limitations.

Howard

 

[L. Howard]

A revised version will be on its way soon. Looks like the overrun problem can be solved with a couple of lines of code but very elusive ones at that.

I understand you can have merged cells on a workbook and share it but you cannot merge or unmerge cell with a shared workbook. So to say, if they are there and are going to stay merged its okay.

The new version has merged cells to accommodate the drop down in Y, Z and AA. That should not be a problem.

The old version was merging and unmerging celsl by code so that was a no go situation.

Regards,
Howard