2 VBA Text Box issue

[Ivor]

Good afternoon friends

Please accept my apologies if I am misusing this thread for two issues. I can separate into two threads if required.

Issue 1

I have an excel sheet which contains a mixture of List and Text boxes. If I have a Microsoft Date Box showing one date, and also have a field on my sheet showing today's date, is there a way of programing a text box to show the number of days between today's date and the date displayed in the Microsoft Date Box? Maybe a text box is not the right thing to show this?

Issue 2

I have searched the forum (and web) for many answers to this question, however they all seem slightly left of centre and sending me slowly mad. So here goes....

I have used the validation function to show a drop down list in a cell. What I need is for the cell background colour (or adjacent cell if it's easier) to change, depending on what is displayed within the cell: i.e.

Priority 1 = Red
Priority 2 = Amber
Priority 3 = Blue
Deferred = Purple
Closed = Yellow

I have tried to do this in a list box, but learnt the hard way that you cannot change the background colour of a list box per item selected. Therefore, please could somebody assist me with the some code, that will allow me to do the above.

Once again I apologies if I've misused the forum here, but thank you in advance for any assistance.

Thanks
Ivor

[abousetta]

Hi Ivor,

Could you post a sample workbook containing both problems? This is easier for us to deal with than having to make up a mock one.

abousetta

[royUK]

What kind of controls are you using? Why textboxes, it's easier to work with cells than textboxes

[Ivor]

Hello Abousetta

thank you so much for your quick reply.I wasn't expecting any help so quickly and so my actual spreadsheet (which is in excel 2007) is at work. But I have managed to knock together a quick demo of the issue using 2003. You will see the Microsoft date picker, todays date and then the empty text box where I need to display the amount of days between the date raised and today's date. Then after that I have the priority validated cell (with the list of options showing from cell F15. What I need is for the cell colour from F15 onward to be visible when selected in A15. Currently only the value (i.e Priority 1) is visible.

However I can send you my actual sheet tomorrow if the attached is no good.

Thanks again

Ivor

[Ivor]

Hello again Roy

Thank you for your reply. I have just sent a mock up example of what I need. The reason for using text/list/combo boxes is because of requirements from work. There's a process which is pretty manual and uses multiple excel spreadsheets. To help automate the process, having one single excel page with text boxes, list boxes..... looks neater and centralises everything. Only problem is that with list/text boxes comes the issue of programming and limitations....but the bosses like it ha haha.

Hope you can see my moc up sheet as sent.

Thanks Roy

Ivor

[royUK]

Try to learn the correct names - it's a datepicker control, t has a Change event which is triggered when you select a date. In the Developer Tab click the Design Mode Icon, then double click the DatePicker to open the VB editor. Place this code in the event module that opens

Please Login or Register  to view this content.

[Ivor]

Hello Roy Sir

YOU ARE A MAGICIAN!!! Thanks a million for your help, it worked perfectly as expected. I cannot thank you enough as I was going around in circles wasting my weekend trying to sort out myself....I guess I still have such a long way to go. Apologies for using the incorrect terminology for the datepicker. Note to self for future use.

Roy, I apologise if I seem a bit greedy here, but as you sorted this issue out so quickly (which was the biggest issue for me), would you have any time to help me with my other issue? Basically as it is not possible to change the background colour of a list box per item selected, I was thinking of substituting a list box for a standard excel validation drop down list. I know that a simple conditional formatting would usually suffice, but as I have more than 3 conditions, is there a way to change the background colour of a validation cell, depending on what is selected from the drop down list?

Totally understand if you do not have time as other people need your assistance, so am thankning you for what you have done for me either way.

Thanks again Roy

Cheers
Ivor

[royUK]

I have an example, probably posted here, of changing a combobox on selection. I'll have a copy at home

[royUK]

This will change the Data Validation cell's colour

Please Login or Register  to view this content.

Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

Copy the code
Select the worksheet in which you the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste

[Ivor]

Good morning Roy

Wow thanks for your quick reply and for helping me. Unfortunately the code does not run for some reason. Now as I am at work I am able to use my actual sheet. The validation cell coordinates are L5, which I am changing on your code. However when I attempt to run the code, I get the macro pop up box asking me to create the code (so VBA is not recognising the code in it's form). So I select 'create' on the macro pop up and paste in your code, however I keep getting an error message 'Run time error 424 - object required'. The code stops at the first line 'If Target.Address <> "$A$15" Then Exit Sub'. I appreciate that I may not be making sense, so have attached some screen shots to show you the actual error happening in the case it will help my explanation.

Thanks again Roy

Ivor

[royUK]

You need to put the code into the worksheet module not a standard module. See the instructions in the previous post

[Ivor]

Hello Roy

I did follow your instructions step by step and noticed the cursor was flashing in a Combo Box module, so I selected 'worksheet' from the module drop down and still had the same event failure. I have even tried opening a new module on a this and a new excel worksheet (as a test) but still get the same errors. The error is obviously to do with what I am doing and not your code, so rather than waste your time I'll have to play around until I can figure out what is wrong. The fact you have provided some code proves I can achieve what I require, just need to use my brain and get over this barrier.

Thanks loads again for your help here, will make things a lot easier once complete.

Cheers
Ivor

[royUK]

I've put the code into your example workbook

[Ivor]

Thank you Roy. I do not know what was happening so I did what I should have done in the first place, simply restart the system...and.....BINGO...it's working.

It must be so irritating when newbies like me just don't get it, so thank you for persavering.

This will help me loads.

Thanks again and enjoy the rest of your day.

Cheers
Ivor

[royUK]

No problem, at least you try to understand what is happening. Glad we got there in the end,