Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA - Excel 2007 - List Boxes

Posted on 2012-04-01
15
305 Views
Last Modified: 2012-08-14
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.
EE-Template-NewV3.xlsm
0
Comment
Question by:elwayisgod
  • 8
  • 6
15 Comments
 

Author Comment

by:elwayisgod
ID: 37793808
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'.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37793823
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.
EE-Template-NewV4.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37794251
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
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

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

Author Comment

by:elwayisgod
ID: 37796957
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
0
 
LVL 33

Expert Comment

by:Norie
ID: 37797172
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?
0
 

Author Comment

by:elwayisgod
ID: 37797368
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?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37797748
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.
0
 

Author Comment

by:elwayisgod
ID: 37797749
`I'm not trying to be a PIA either.  Just feedback I got.  I think whats done so far is phenomenal, believe me.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37797784
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.
0
 

Author Comment

by:elwayisgod
ID: 37797948
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.
0
 

Author Comment

by:elwayisgod
ID: 37797977
0
 
LVL 33

Expert Comment

by:Norie
ID: 37797999
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?
0
 

Author Comment

by:elwayisgod
ID: 37798024
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 37798061
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question