Making an macro/button to select selected cell to input text/fill colour

Hi, following up on my previous thread - http://www.excelforum.com/excel-gene...ml#post4100769

I was wondering if its possible to make a button/macro for this, I manually got it working by writing a word which then colours x number of cells.
However, I was wondering if by coding? (rather than formula), this process can be automated. (I'm making a timetable/schedule plan)
So for example.
I can select any cell for a booking, I would input a name manually (it will be on top of the cell) please refer to example worksheet, the following cells (including the name would be coloured)

Anna has a 10:30 appointment for an M+P. M+P lasts 1 hour meaning each cell refers as 15mins.

Anna = D5 (the name will be written on the cell manually)
M&P = D6 (this will be automated below the name)
D7/D8 will also be coloured too so 4 cells, D5/D6/D7/D8 will be coloured.

Is this possible via macro or code?

Looking for response and hopefully some help! really appreciate your efforts and help.

Regards,
Liqt

EDIT - I WAS WONDERING IF THIS BUTTON COULD BE ON THE SAME PAGE AS THE TIME SCHEDULE

This is a slightly different approach you may want to try to achieve what you want. You don't need to click any buttons with this method. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "12th JUNE 2015 - TIMES" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Please Login or Register  to view this content.

Enter a name in a cell and then manually enter "B/Dry", "P", "Wax", "Acrylic" or "M+P" (without the quotation marks) in the cell below the name. The appropriate cells will be coloured. This macro assumes that M+P takes one hour and all the others take 45 minutes. If your times are different, the macro will have to be changed slightly.

Originally Posted by Mumps1

This is a slightly different approach you may want to try to achieve what you want. You don't need to click any buttons with this method. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "12th JUNE 2015 - TIMES" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Please Login or Register  to view this content.

Enter a name in a cell and then manually enter "B/Dry", "P", "Wax", "Acrylic" or "M+P" (without the quotation marks) in the cell below the name. The appropriate cells will be coloured. This macro assumes that M+P takes one hour and all the others take 45 minutes. If your times are different, the macro will have to be changed slightly.

Hi Mumps1, thanks for your reply.

I was wondering if this could be done automatically with a button though, the reason for using an macro/button is to make it more simple for people to use who aren't computer literate when it comes to typing so I want to try automate the process and they only need to write the name.

For instance, if they write their name D5 - Anna. > They click on Anna
Then they can click a button/macro which will write "M+P" under the name and highlight the appropiate number of cells (giving the duration, if M+P is 1 hour = 4 cells highlighted including the name)
Result = D5/D6/D7/D8 will be highlighted.
D5 = Name (filled with colour)
D6 = M&P (or whatever) (filled with colour)
D7 = nothing (filled with colour)
D8 = nothing (filled with colour)

Is that possible with a button? which this button would be on the same page as the time/schedule.

Looking forward for some assistance and I really appreciate your response and help!

Liqt

I have overhauled what you are trying to do. This is not precisely how you asked for it but I think it will be an improvement. This is a macro solution. Your posted file has buttons but no macros so I have no idea what your buttons are intended for but I took a different approach.

To schedule an appointment, select the cell with the start time and hit the Schedule button. You will get a form to fill in the schedule details (customer name, service).

To cancel an appointment highlight the colored cells for that appointment and Cancel Appointment.

Originally Posted by 6StringJazzer

I have overhauled what you are trying to do. This is not precisely how you asked for it but I think it will be an improvement. This is a macro solution. Your posted file has buttons but no macros so I have no idea what your buttons are intended for but I took a different approach.

To schedule an appointment, select the cell with the start time and hit the Schedule button. You will get a form to fill in the schedule details (customer name, service).

To cancel an appointment highlight the colored cells for that appointment and Cancel Appointment.

Oh my god, this is SO PERFECT. However, one question - to make it less confusing, is there any way to change the colour as M&P and B/Dry are obviously different treatments, I want to have colour code based on each thing and I would need to include more words in future, could you give me a guide line and I REALLY APPREICIATE this, thank you.

Looking forward to your reponse, and thank you for your input, I will most likely use your sample.

EDIT; Oh you just add the word and copy the formula to add words. How about changing the colour?

Originally Posted by 6StringJazzer

In this version, you can go to the "Services" tab and set the colors for each treatment to whatever you want. If you already have appointments set and then later change the colors, it will not automatically go back and change the appointment sheets.)

With this new system of different colors, you must set a color for each treatment. The code depends on the colors to know that an appointment has been blocked for a given time, so there must be a fill color for each appointment.
You can add more treatments to the list on the "Services" tab anytime you want. Just follow the same format, with name and minutes. The formula in column C will update automatically when you start typing the treatment name.

6StringJazzer, THANK YOU SO MUCH FOR THIS, this is much easier/efficient to use.

However one important question I should have asked which I feel is also a game changer, what if a client decides to have more than 1 treatment/service booked for example wanting perhaps 2-3 (ranging maybe realistic upto 6 Services?) in one appointment.

Is it possible to have more than one Service form/drop down list and how would it effect the formatting?

Ideally let's say M+P = 4 cells, B/Dry 3 cells.
ie; D5 = Amy, D6 = M+P, D7, D8, D9 = Amy, D10 = B/Dry, D11

If only one Service is needed, then everything is fine. The other services doesn't need to be filled or selected in order to proceed the booking.

I know you can just make another appointment but that would be time consuming to do this everytime so I was wondering if it could be done in under 1 form? like just adding another Service tab and making it stack below the previous Service (like my example I wrote)

Does that make sense 6StringJazzer? I hope what I said makes sense and is do-able and that's pretty everything I believe/hope I would ever ask for because I believe I'm asking too much already.

I honestly can't thank you enough for your help/kindness. This is pretty much spot on really but missing a core feature.

We are starting to get into the area of commercial consulting here. I don't mind helping with a question to help someone develop their own solution, but now this is turning into a project for a business. I'd be happy to help further if you would like to post this to our Commercial Services forum.