LinkBack URL
About LinkBacks
Hi guys, have 2 lists and only want users to be able to select from one of these ranges to paste in to another.
Not wanting to use drop down lists but would prefer it done via some vba if possible but not sure what to do.
Can anyone advise ??
Hi guys, have 2 lists and only want users to be able to select from one of these ranges to paste in to another.
Not wanting to use drop down lists but would prefer it done via some vba if possible but not sure what to do.
Can anyone advise ??
Hello wanty,
the quickest non VBA solution would be to simply unlock the cells that can be copied and then protect the worksheet.
If you want or need a VBA solution, you should post your workbook.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks m8, have posted my book as well as the private sub code within the sheet.
In short there are areas marked in purple at the top which hold names which via the private sub code allow me to copy the names.
Currently I'm able to paste the names to both the areas marked as "Teams and "Rectification"" which is great but I'm also able to paste these names
to the areas marked as "Tasks" which is not what I want.
.
I'd prefer the name's to only be pastable to the area's marked as "Teams" and "Rectification".
At the bottom, I also have a 2 rows of cells with which I'd like the user to be able to paste these values to "ONLY" the areas marked under "Tasks"
Currently, either the values at the bottom "xxx's" or names at the top can be pastable to either tasks or "Teams" which I don't want.
I need the user to prevented from pasting values in the wrong aras if possible...
Cheer's also to the great bloke who helped me with the priv sub below, it works terrific also.
Here's the priv sub:
Please Login or Register to view this content.
Last edited by wanty; 07-03-2013 at 11:02 PM.
Hello wanty,
Do you want these same rules to apply if the user types information into the cell?
Yes Leith, I don't want people to type in the cells at all and prefer them only to use the mouse to select a name from the top or a value from the bottom.
Names at the top pastable only to "Teams and Rectification" areas.
values at the bottom marked with xxx pastable ONLY to the areas marked as "Tasks"
In both cases, the user is warned about pasting selections in the wrong areas and prevented from doing so either via the sub exiting but preferrably from both a message and the sub exiting.
Cheers for your assistance,
W
Anyone got an idea on how I can achieve what I've asked here ??
Hello wanty,
After working on this for 2 days, I would employ a UserForm to control the cell input. There are too many that ccan not be controlled in the original approach.
Wow, thank you so much for your efforts m8, had little idea you were doing so so again thank you for your efforts.
Looked at a userform option but not exactly sure how to apply this in said sheet.
Think I may simply stick with conditional formatting to completely black the cell if a value is placed in an inappropriate area so the user will be forced to delete said value and try again.
Not sure if it's possible or how to have 2 "choice" ranges and 2 "paste" ranges in the private sub so that if the user attempts to paste a value in the wrong are the code simply exits sub
Again, thank you for the efforts.
Hello wanty,
I can put together a workbook with the UserForm. It really isn't much more work than what I have done already.
That would be awesome m8, even better would be a useful link if you know of for me to read and learn from so I don't always have to ask more learned people.
Being self sufficient using vba and excel is my ultimate aim,
W
Hello wanty,
I didn't get much time on the computer this weekend. My wife had several projects for me. Here is the completed workbook with the UserForm.
The teams and tasks can now only be edited when the user clicks the button on the sheet. This brings up the form and unprotects the worksheet. When the form is closed, the worksheet protection is activated again.
Currently, the password is 1234. You can change this to what you want to use. It is in the UserForm code. The names and task choices can not be edited by the user. Only the person with the password can edit these. The user can only add or delete names on a team or add or delete task choices for a team.
The attached workbook contains all the changes and the UserForm.
Not sure what if anything that I'm doing wrong Leith but for the life of me I can't get it to work.
It won't allow me to select team members to the teams area and nor will it allow me to add tasks to teams.
Have tried it in excel 2003 2007 and 2010 and still the same result ??
Last edited by wanty; 07-10-2013 at 07:56 PM.
Hello wanty,
Post a copy of your workbook so I can check it on my system. The copy I posted was working fine for me. Maybe some setting in your workbook is different.
Ok, think I worked it out, in order to allocate a team member I'm having to first select inside the teams area on the right and then use the arrows to move a team member to the box on the right which poulates the individual's name in the correct area on my sheet. Works the same with the task allocation.
Not in any way trying to sound ungrateful as it's a lot better than anything I could have built but is there a way to adjust the code so one simply selects a team or task on the left and then arrow select or deselect them to the fields on the right without having to click inside the allocation areas on the right to get it to work ??
I've worked it out but need to build this for the lowest common denominator operator if you catch my drift.
Hello wanty,
Not sure I follow you on the arrow select/deselect. I understood you wanted the user to make choices and have them copied to the correct section of the worksheet. Can you provide me with an example of how the input selection should work?
Ok and please don't get me wrong as I am very grateful for your help.
When I first select the edit button which opens the userform you built, I see a selection of teams at the top with 4 boxes below these selections.
The first box top left is the manpower choices, to the right of that box I see another box with arrows between the two to transfer names from the left box to the right box.
In order for me to transfer a name from the left box to the right, I firstly choose a team at the top. Next if I was to click on a name in the top left box and then use the arrow to transfer the name across to the right it won't transfer, but it does if I left click my mouse firstly in the place where I want the name to be transferred to and then clicking the name on the left again followed by the right arrow between the two boxes. I find that in order to transfer another name from left to right, I again have to select a new location on the right box below the first name already in the box otherwise making a 2nd selection simply overwrites the first name already there.
Was kind of hoping I needed not select the right box first before transferring from left to right and was hoping a 2nd selection didn't overwrite someone's name already in the box but rather a 2nd selection or transfer if you will just added to the box on the right rather than replacing the first name already in the box.
Again, very grateful so please don't take this the wrong way,
W
Hello wanty,
I wasn't sure if you were allowing the users to edit choices and team names. The lists on the left are static. the one on the right the user can edit. That is why the transfer works the way it does.
Development is a process and interpreting what someone needs can be tricky. I offer solutions based on my experience and fine tune them from your feedback, so no offense taken.
I will make some changes to the selection process. Hopefully, we can get this working the way you want.
Your a champ m8. Ever so grateful.
Hello wanty,
Thanks for the bump. It has benn as busy day. I will have time this evening to make the changes.
aLL GOOD M8, THANKS AGAIN
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks