Solved

VBA - Excel 2007 - List Boxes

Posted on 2012-04-01
15
302 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 41

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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now