VBA - Excel 2007 - List Boxes

I have a file attached.  I need A15 to A3014 to be a drop down box.  The two choices on Drop Down need to be 'Copy' or 'Don't Copy'.

Then I need a Button that will change A15 to A3014 to 'Copy'.  I need a button that will change A15 to A34 to 'Don't Copy'.

Kinda a way for users to reset their choices.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

elwayisgodAuthor Commented:
Also, when 'Copy' is in Column A for a Row, I need it to shade the row from Column B to Column U in that Light Blue, if possible.  That way users can tell what rows they have selected to 'Copy'.
NorieAnalyst Assistant Commented:
Here you are.

I've used Data Validation for the dropdowns - having 3000 controls might not be a good idea.:)

The formatting is done with simple Conditional formatting and the buttons have subs assigned to them to change all to Copy or all to Don't Copy.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Looks like you made the transformation to DV as discussed - great.  Glad imnorie was there as I was totally out of it - stayed up way too late last night.


Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

elwayisgodAuthor Commented:
Its all good.  I learned a boatload.   Not completely done yet :)   On plane now so will update tomorrow :)
elwayisgodAuthor Commented:

got a bit of feedback from Customer.  

1 - On cells c15 to K3014, they will be copying and pasting values in there.  It seems to be wrecking the line formatting.  Is there a way that no matter what copy and paste method they perform, it will always only be 'values'?  Or a way to keep the formatting no matter what?

2 - Column C15 to C3014 are zip codes.  However when they copy and paste into there, Excel recognizes it as a Number.  Essbase chokes on that.  Thus on the 'GetData' button and underlying VBA, is there a way to convert whatever they paste in those cells to TEXT prior to it actually trying to perform the RETRIEVE Essbase function? Needs the ' before it automatically???

3 - The 'Copy' and 'Don't Copy' part in Column A.  Is there a way that it if they click anywhere in a Row it will change it to Copy or Don't Copy depending on where it's currently is?   So if it's 'Copy' and they click on that Row anywhere it changes to 'Don't Copy' and if it's 'Don't Copy' it changes it to Copy?  Rather than having to select in Column A?

Latest file is attached.

Can repost this as new Question if you like.
NorieAnalyst Assistant Commented:
Little confused.

1 Not sure what you mean, what 'line formatting'? How are they currently copying and pasting?

2 In the file/code I posted/attached the values in column C are still text when they get copied over.

3 So you don't want the data validation, or any type of dropdowns?
elwayisgodAuthor Commented:

1 - Not sure what you mean, what 'line formatting'? How are they currently copying and pasting?  Users will copy and paste from other files they have to populate columns C to K on Retrieval tab.  When they do that all those vertical lines I created via Format cells disappear etc..  Don't want any formatting to change on either Retrieval or Master tabs.

2 - In the file/code I posted/attached the values in column C are still text when they get copied over.  Yes.  I copies correctly from Retrieval to Master.  Thats not what I'm referring to.

3 - So you don't want the data validation, or any type of dropdowns - This one I'm not sure yet.  Just thinking it would be easier if they clicked on any member on that Row, it would 'mark it' to Copy or 'un-mark' it????  Can that be done?
NorieAnalyst Assistant Commented:
2 What are you referring to?

3 Yes, but using a double click instead of a click - there is no click event for a worksheet.

If 1 & 2 are just to do with the formatting try protecting the worksheet, setting it to allow the user to paste/enter etc but not change he format.
elwayisgodAuthor Commented:
`I'm not trying to be a PIA either.  Just feedback I got.  I think whats done so far is phenomenal, believe me.
NorieAnalyst Assistant Commented:
Sorry, I don't understand - I'm just trying to get a handle on what's going on.

I've basically done 3, but only for column A - so it needs a little tweak to work for the entire row.

As for 1 & 2 I'm still not sure what's going on, part of me is thinking this is a user problem as it's them pasting the data.

Apart from protecting the worksheet the only other thing I could think of was to have another sheet where the user can paste the data they want to add.

This sheet would have a button that when pressed would copy the data 'properly' to the Retrieval sheet where they can review it, select what to copy etc.

I don't know it that would work, the users might start thinking the workbook was a PITA to use.
elwayisgodAuthor Commented:
OK. forget those three requests.  They just threw me a monkey wrench big time.

1 - 'Copy' now becomes 'Cut'.  When they 'Copy/Cut' selected rows to the 'master' tab, I can't have it clear first as they want to append to 'Master' tab.  They will be moving rows back and forth between the two tabs, I just found out.  So then 'Master' tab has Column A like 'Retrieve' tab.  Choices would be 'Move to Retrival' and 'Keep'.  Then a button that 'Moves' selected rows back.  Can they be put back in correct order by Unique ID too?  And then a Button that would clear the 'Master' range, if they click it.  They may want to discard everything on 'Master' tab at one point or another thus that button would be good.

I'll post this request, with new updated file as new question worth 500 pts.
NorieAnalyst Assistant Commented:
Can't you just throw it back when nobodies looking?

Only joking, that could be sore.

I think I understand the append part and that can easily be fixed as the original code didn't clear Master.

The Cut part though, it doesn't seem like anything is being cut - it's more like 'transferring'.

Suppose it's the same thing in the end.

Now the sorting thing, do you mean just keep both sets of data sorted by ID.

PS What's to happen with the original question?
elwayisgodAuthor Commented:
I just awarded you 500pts for it.  Your answering everything.  I don't mind posting alot of questions worth 500 pts ea.  I appreciate the help greatly.

Transfer or 'Cut' same thing :)  I just used Excel verbage :)

Sorting.  Yes always have it sort by Unique ID once things are moved between tabs.
NorieAnalyst Assistant Commented:
Actually just been thinking about it and sorting might actually be a help.

For example after moving rows from one sheet to the other, we then clear the original rows.

That would leave gaps, but if we sort the gaps will disappear.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.