Solved Paste cell from Drop Down Menu

September 12, 2016 at 13:01:00
Specs: Windows 7
Hello,

I am trying to make a macro and failing so bad.

I want to be able to select an item from a drop down list and it paste exactly how it is formatted from the other sheet.

Any help is greatly appreciated.


See More: Paste cell from Drop Down Menu

Reply ↓  Report •


✔ Best Answer
September 13, 2016 at 06:41:10
The following code seems to work for the workbook I set up. The following conditions need to be met for it to work. You will need to modify it to fit your exact situation.

1 - The Drop Down Validation list is in A1 of whatever sheet you choose.
2 - The source list for Drop Down is in Sheet2!Column B
3 - The source list range has been named myValList. This was done to allow you to change the size if the source list range within Excel without having to edit the macro.
(More on this later)
4 - The code including below needs to be stored in the Sheet Module for the sheet that contains the Drop Down. i.e. If your Drop Down is in Sheet1!A1, then the code must be stored in the Sheet1 Module.

The way the code works is as follows:

1 - The code monitors A1 of the sheet that contains the macro.
2 - When a change is made to A1, the code stores the location of the source list in a variable called valList. This needs to be done because the Copy/Paste that will occur later will clear the validation.
3 - The code then searches the named range myValList for the chosen value and does a Copy/Paste into A1.
4 - The last thing the co+de does is reapply the Data Validation.

An important note regarding the Named Range myValList:

The reason I used a Named Range is so that you can alter the size of the source list without having to edit the macro to reflect the new range. However, you must make sure that the Range Name always references the correct range. e.g. If the original range is Sheet2!B1:B10 and you want to extend it to B1:B15, you'll need to ensure that myValList Refers To the larger range. That can be done manually through the Name Manager or you can create a Dynamic Named Range as explained at the link below. A Dynamic Named Range will automatically change its "length" to however many values are in the Range.

https://support.microsoft.com/en-us...

Here's the code. Let me know if you have any questions.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if cell with drop down has changed
  If Target.Address = "$A$1" Then
    Application.EnableEvents = False
'Store validation list range
    valList = Target.Validation.Formula1
'Search validation list range for value chosen
'Copy it to cell with drop down
      With Sheets(2).Range("myValList")
        Set c = .Find(Target, lookat:=xlWhole)
          c.Copy Range(Target.Address)
       End With
   End If
'Reapply Data Validation
       With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=valList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 12, 2016 at 13:08:02
Please keep in mind that we can't see your spreadsheet from where we are sitting. You are going to have to provide a lot more detail if you want us to help you.

re: "I want to be able to select an item from a drop down list"

That seems pretty straight forward.

re: "it paste exactly how it is formatted from the other sheet."

I don't know what you mean by that. "It paste" to where? From what "other sheet"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
September 12, 2016 at 13:13:00
Hey sorry about that.

I made the list to select from in one sheet 1 and then put the dropdown in sheet 2.

The text has formatting such as bolding and different colors. When I do a data validation to create a drop down list it will copy all the text but none of the formatting.

I was wondering is there a way when I select from the list for it to be exactly as I have it with all the formatting or will it always just be the text?


Reply ↓  Report •

#3
September 12, 2016 at 17:11:06
Without knowing how many words are in your list, a non Macro solution
would be to set up Conditional Formatting Rules that will Italicize, Color or Bold
the word when you select it.

So all the CF rules would apply to only the cell that has your dropdown.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
September 12, 2016 at 17:20:06
By default, the drop down will only place the value in the cell with the drop down.

If you want the formatting, I think you will need to use a macro to search for the selected text in the range that populates the list and then copy the text, along with it's formatting, into the cell with the drop down.

Is a macro something that you would be interested in?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#5
September 12, 2016 at 20:52:52
Yes Derbydad03 that sounds exactly like what i need.

Reply ↓  Report •

#6
September 13, 2016 at 06:41:10
✔ Best Answer
The following code seems to work for the workbook I set up. The following conditions need to be met for it to work. You will need to modify it to fit your exact situation.

1 - The Drop Down Validation list is in A1 of whatever sheet you choose.
2 - The source list for Drop Down is in Sheet2!Column B
3 - The source list range has been named myValList. This was done to allow you to change the size if the source list range within Excel without having to edit the macro.
(More on this later)
4 - The code including below needs to be stored in the Sheet Module for the sheet that contains the Drop Down. i.e. If your Drop Down is in Sheet1!A1, then the code must be stored in the Sheet1 Module.

The way the code works is as follows:

1 - The code monitors A1 of the sheet that contains the macro.
2 - When a change is made to A1, the code stores the location of the source list in a variable called valList. This needs to be done because the Copy/Paste that will occur later will clear the validation.
3 - The code then searches the named range myValList for the chosen value and does a Copy/Paste into A1.
4 - The last thing the co+de does is reapply the Data Validation.

An important note regarding the Named Range myValList:

The reason I used a Named Range is so that you can alter the size of the source list without having to edit the macro to reflect the new range. However, you must make sure that the Range Name always references the correct range. e.g. If the original range is Sheet2!B1:B10 and you want to extend it to B1:B15, you'll need to ensure that myValList Refers To the larger range. That can be done manually through the Name Manager or you can create a Dynamic Named Range as explained at the link below. A Dynamic Named Range will automatically change its "length" to however many values are in the Range.

https://support.microsoft.com/en-us...

Here's the code. Let me know if you have any questions.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if cell with drop down has changed
  If Target.Address = "$A$1" Then
    Application.EnableEvents = False
'Store validation list range
    valList = Target.Validation.Formula1
'Search validation list range for value chosen
'Copy it to cell with drop down
      With Sheets(2).Range("myValList")
        Set c = .Find(Target, lookat:=xlWhole)
          c.Copy Range(Target.Address)
       End With
   End If
'Reapply Data Validation
       With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=valList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
September 13, 2016 at 14:35:35
Hey Derbydad I tried following your instructions and it did not work. I tried in my original sheet and then I made just a test with A and B and followed the instructions but it gave an error on line Set c = .Find(Target, lookat:=xlWhole).

I think it may just be a lost cause perhaps the text is too long im not sure.

Thanks for the help though :)


Reply ↓  Report •

#8
September 13, 2016 at 16:24:48
Without knowing what you mean by "perhaps the text is too long", there is no way for me to try and offer a fix. As I very strongly hinted at in my first response, we can't help you if you don't provide any details.

Maybe, just maybe, a detailed description of how your workbook is set up and a few examples of the data in your drop down list will help us help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#9
September 14, 2016 at 11:48:55
Hey sorry about that.

I will try my best to describe it in great detail. Thanks for being so patient with me.

So the first sheet named "Agenda" is where the drop down list is. It starts in cell B3 and every cell in the B column after B3 has the drop down.

Starting In C3 it automatically fills in the recommended attendees based on what was selected in the B Column. I used data validation for this.

On Sheet 3 which I named "Items" , which will be hidden eventually, I put all the lists I needed. Starting in A1 is the list named "Description" this is what is used for the dropdown in column B of "Agenda". This is the items I want copied with formatting when it is selected from the drop down.

I hope that makes it clearer please let me know if that all made sense. Thanks again for working with me and being so patient with a newbie like myself.


Reply ↓  Report •

#10
September 14, 2016 at 12:46:43
I still don't know what you meant by "perhaps the text is too long".

Let's try it this way...I'll assume what you have, you tell me what I have wrong.

On the sheet named Items, you have something like this:

             A
1      Description
2         House
3         Boat
4         Car

Items!A2:A4 is used as the Data Validation list for the Drops Downs that start in Agenda!B3

Items!A2:A4 are formatted in some manner, perhaps fill colors, font colors, bold, etc.

When you make a choice from a Drop Down on the Agenda sheet, you not only want the value from the list in Items!A2:A4 to appear in the cell, but also the formatting.

e.g. If the cell of the Items sheet that contains House is filled with Yellow and the text is underlined, you want the Drop Down to "return" a cell filled with Yellow and the word House to be underlined.

Is that correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#11
September 14, 2016 at 12:58:00
Yes that is absolutely correct. By text too long I meant it's not a single word it's like a parasail in each cell.


Reply ↓  Report •

#12
September 14, 2016 at 14:05:30
It would probably help if you gave some examples,
instead of us guessing at what's in the cells
and in what manner they are formatted..

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#13
September 14, 2016 at 14:45:24
re: "it's like a parasail in each cell."

What?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#14
September 14, 2016 at 14:55:08
Sorry autocorrect paragraph

Reply ↓  Report •

#15
September 14, 2016 at 15:49:16
Read Response #12. No more help will be offered until you fulfill that request.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#16
September 26, 2016 at 05:27:35
Incident Logging Training (Session II)
How to log MOD reports, comment card comments, GSS Verbatim, adjustments, and guest letters. How to follow up on open Incidents.

This is an example. I am unsure how this helps because if the copy function is invoked it should be able to paste with formatting right?


Reply ↓  Report •

#17
September 26, 2016 at 07:21:23
re: " I am unsure how this helps because if the copy function is invoked it should be able to paste with formatting right?"

It helps because we were guessing at what you were copying, how it was formatted, etc.

For example, I had no clue that part of the test was formatted differently than other parts, that the cell had multiple lines, etc. I've been doing this for a long time, so please trust me when I say that the more details that we have, the easier it is for us to test our suggestions. Now that I know exactly what you are working with, I can test my code against a real live piece of your data. If I get the same error, then I know that the suggested code doesn't work with your data. If I can't duplicate the error, that we need to start looking elsewhere.

That said, I am having no problem with the code I posted when tested against various versions of your example. By "versions" I mean that I made 5 copies of your example, changed one or 2 letters so that each entry was unique, and then formatted each version differently.

I changed font colors of some of the words in a given cell, I filled different cells with different colors, I doubled underlined or italicized or bolded different words in different cells. When a value was chosen from the drop down, all formatting was copied exactly as shown in the source list.

I then tried to duplicate your error and was not able to do it. I did notice that the workbook I set up had the Data Validation Source List in Sheet 2 and the drop down in Sheet1 - which is the reverse of what you described, but I don't think that that would cause an error on the line you mentioned. I then set up a workbook as you described and did not get any error. I did have to change the following instruction from this:

With Sheets(2).Range("myValList")

to this:

With Sheets(1).Range("myValList")

Frankly, I can't find a way to get the code to fail at

Set c = .Find(Target, lookat:=xlWhole)

I don't think it's because "the text is too long" unless there is something different about the other items in your list. The example you posted certainly didn't cause a failure, even after some fairly intensive formatting options. There must something else going on that we don't about.

BTW...there is one more thing that you should be aware of:

If the code makes it past the Application.EnableEvents = False instruction and fails before the Application.EnableEvents = True instruction, the Worksheet_Change macro will not run again until EnableEvents is set back to True.

That can be done with this short piece of code:

Sub ResetEvents()
 Application.EnableEvents = True
End Sub

Since EnableEvents is an Application level instruction, it's remains set at its last state until specifically told to change. Simply "quitting" a failed macro will not turn it back on.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#18
October 2, 2016 at 10:56:00
Found the error.

With Sheets(2).Range("myValList")

I had to change it to

With Sheets("Sheet2").Range("myValList")

Not sure why but now it works. Thanks so much I know my lack of knowledge was probably trying.

Thanks Again!


Reply ↓  Report •

#19
October 2, 2016 at 12:57:07
Sheets(2) refers to the sheet in the 2nd position of the workbook, regardless of the name on the sheet tab.

Sheets("Sheet2") refers to the sheet with the name Sheet2 on the sheet tab, regardless of its position in the workbook.

Anything in quotes is considered a text string in Excel. "Sheet2" tells Excel to look for a sheet named Sheet2. Just FYI, if you used Sheets("2"), Excel would look for a sheet named 2, not the sheet in the second position of the workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •


Ask Question