• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation with VBA

C

cschoenfeld

New Member
Sorry, I think I initially posted this as a reply on another post.


I have a spreadsheet where, when data is entered into column B, I want data entry to be required in columns G, H and I. G, H and I happen to be data validation dropdown lists. I would also like columns G, H and I to show a fill color (as a visual they need to be filled in) when data is entered into col B. I've been trying to do it with conditional formatting and have it working but the cell shading isn't quite right and I thought it could be fixed with VBA.

Col B = Work Order Number

Col G = Time Reporting Code

Col H = Location

Col I = Task


Any help is appreciated. Thanks!
 
Cschoenfeld


Can you post a sample file


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
So sorry about that! Try this...

https://www.dropbox.com/s/32l4ocmdx3ri0vk/Copy%20of%20GB%20Timesheet%20Template%20030113_1.xls
 
Hi Schoenfeld ,


Can you say why the shading is not correct ? What kind of colour did you want should be applied in columns G , H and I ?


Narayan
 
What I want is that when a work order number (or any text) is entered into column C (work order column) it will automatically highlight the cells in the same row in columns G, H and I (a light shade, like R 221 G 217 B196). I don't want to make G, H and I required, but I want to highlight them when a corresponding work order is entered to remind the user to fill them out. For example, if 12345 is entered into C12 (or a text example, 'Admin Time') then G12, H12 and I12 should automatically highlight with a fill color. Once the user enters text, the fill color should disappear.
 
Hi Schoenfeld ,


Is this acceptable ?


https://www.dropbox.com/s/o4ogoj93cpkl0c5/Copy%20of%20GB%20Timesheet%20Template%20030113_1.xls


Narayan
 
It's so wonderful! I was hoping to have the shading just as a visual reminder versus an actual MsgBox. Would you be able modify such that there's no MsgBox? I tried taking out both message boxes and that worked ok but then I discovered that the shading from all 3 cells, G, H, I, also disappeared at the same time when data was entered into only 1. Could the shading disappear cell by cell (G, H, I) as data is entered?


You've really made it so workable for us that we're very grateful.
 
Hi Schoenfeld ,


No problem ; only it's somewhat late tonight ; can you wait till tomorrow morning ?


I'll upload the revised file tomorrow.


Narayan
 
Hi Schoenfeld ,


Sorry for the delay ; can you check this version ?


https://www.dropbox.com/s/o4ogoj93cpkl0c5/Copy%20of%20GB%20Timesheet%20Template%20030113_1.xls


I am not very sure it's foolproof ; please check and let me if you want any changes.


Narayan
 
It seems to be behaving the same as before - MsgBox always comes up. I would still prefer to have the cell shading appear in G, H and I and data entry into C without any MsgBoxes.
 
Hi Schoenfeld ,


I had not understood your requirement the first time around ; now , I think this should be what you are looking for.


https://www.dropbox.com/s/o4ogoj93cpkl0c5/Copy%20of%20GB%20Timesheet%20Template%20030113_1.xls


Narayan
 
Well, I have a followup question. When I protected the document, I ran into some final issues. Doc is at https://www.dropbox.com/s/a75e3yw0dlaw5ni/Copy%20of%20GB%20Timesheet%20Template%20040313.xls and has the password of 1*


Once I protect the document and enter data in C, I get an error 'Run-time error 1004'. I've tried a few variations of code for protecting, clearing and re-protecting the spreadsheet but each had problems. I left the last code in there that I tried. I'm having issue with the Clear Timesheet and Clear Hours Only buttons working at the top as well once the spreadsheet is protected.


Can you provide any final assistance with this?
 
Hi Schoenfeld ,


I've just downloaded your file ; give me some time to go through it , and I'll get back to you.


Narayan
 
Hi Schoenfeld ,


Can you check your file here ?


https://www.dropbox.com/s/lulj8hoyii0eec4/Copy%20of%20GB%20Timesheet%20Template%20040313.xls


Narayan
 
You must log in or register to reply here.
Back
Top