VBA - Excel 2007 - List Boxes

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'.

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.

Cheers,

Dave

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

Hi,

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.
EE-Template-NewV9-Stripped.xlsm

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?

OK.

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?

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.

`I'm not trying to be a PIA either.  Just feedback I got.  I think whats done so far is phenomenal, believe me.

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.

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.

New queston posted.

https://www.experts-exchange.com/questions/27659257/Excel-2007-VBA-Help.html

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?

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.

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.