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.

 

[L. Howard]

Quick question.

How do you physically make the light vertical dotted lines that divide the hour?

Howard

 

[SamanthaP]

I've used a different line style instead of the standard solid line.

Samantha

 

[L. Howard]

Okay, I found it under More Borders.

Howard

 

[SamanthaP]

From the Borders menu on the Home Tab.

 

[L. Howard]

Have made some progress. Having trouble making the code ignore the cells that have color in them (Booked Times).

It wants to format right on over the highlighted cell and that is what I am trying to solve.

As is, do this.

Note the button near cell W1 named Reform. Also in X1 and Y1 are drop downs. You select a Day from X1 and a Desk number from Y1 and then run the code by clicking the Reform button.

I believe it is now set up to run on Monday, Desk 9. Just click the button and note the results.

I did remove some merged cells, the large bold weekdays names are now centered across selection and the Desk column are in a single column.

Merged cells are a huge menace to work with using code or worksheet functions, and troube-shooting can become a nightmare.

Comments or questions??

https://www.dropbox.com/s/phs2j66w3iq41sh/AA Copy of DeskBookings Drop Box.xlsm

Howard

 

[L. Howard]

Well, it took some time, sorry bout that, but here is a "completed" version.

https://www.dropbox.com/s/z2mnc5jycvpcbfy/DeskBookings Reformatting Drop Box.xlsm

You will need to play with it to see if it does the things you want. Maybe some things you do not want it to do.

In cell X1 is a drop down from which you can pick the Day, Monday thru Saturday.
In cell Y1 is a drop down from which you can pick the Desk 1 thru 14 or "SkillTec Room", "Delegate Area" on down to "Out of Office 2".

Once a Day and a Desk is selected then click the smallish "Reform" button and that row on that Day will be reformatted.
Any "Reserved Times" (colored cells) are ignored.

So when you delete a "reservation" you now have a group of cells with no border formatting. You would select from the drop downs, the Day and the Desk, and click the "Reform" button.

You may notice that when you select the Y1 drop down cell that the column is automatically widened to give a full views of the longer items, "SkillTec Room", "Delegate Area" on down to "Out of Office 2".

Once you click outside the Y1 cell it reverts to normal width.

There is also code to "Center on Screen" the current Day you are working on while the cursor is returned to cell X2.

Please let me know how it goes.

Regards,
Howard

 

[SamanthaP]

Howard, you are a GENUIS!

Thank you so much! This is just what is needed... I'm really so grateful for this and thank you for your time and effort!

I really thought it might be impossible but you clearly know your stuff!!

Thank you again!

Samantha

 

[SamanthaP]

not sure what a Genuis is? You're a genius! (hate spelling mistakes!)

Samantha

 

[L. Howard]

Hi Samantha,

Glad it works for you, you may see something you need to tweak as you use it more.

The code is written largely by a true genius named Claus who is a dominate contributor along with a few others in the MS Public Excel forums.

I will take full credit for being the messenger here.

You can do this to clean up the workbook.

In Module 1 find the code that is titled as this:

Sub ReFormTheRowxxxx()
'/ by Claus

and delete it. It does nothing now and was an earlier version which has been replaced with the code headed as this:

'/ **** This Code Is In Use ****\'
'/ Assigned to the button "Reform"

Sub DayFormater()


Regards,
Howard

 

[L. Howard]

Update alert.

Will have a slightly different version for you to look at soon.

Howard