Dropdown list values - how to maintain the original format

[dwhowell]

Using Excel 2002. I have a dropdown box that allows me to select values from a list on a different worksheet. Is there any way to format the destination cell so that it will display the selected result in the format shown in the original list? Each value in the list has a different format (font and/or text color).

 

[Damon Ostrander]

Re: Dropdown list values - how to maintain the original form

Hi dw,

Just put this code into the event code module associated with the dropdown (I'm assuming it is a combobox from the Controls Toolbox toolbar).

Private Sub ComboBox1_Change()
Dim Lcell As Range
Dim Rcell As Range
With ComboBox1
Set Lcell = Evaluate(.ListFillRange).Cells(.ListIndex + 1)
Set Rcell = Evaluate(.LinkedCell)
End With
Lcell.Copy
Rcell.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

To put this in the worksheet's event code module, right-click on the worksheet's tab, select View Code, and paste the code into the VBE Code pane.

 

[dwhowell]

Re: Dropdown list values - how to maintain the original form

Damon:

Thanks for the quick response! I'm not using a combobox though, I'm using the Validation/Allow List option.

Thanks for your help,
Dennis

 

[Damon Ostrander]

Re: Dropdown list values - how to maintain the original form

Hi again Dennis,

Here's a similar solution that works with a validation list. I couldn't test it with the list on a different worksheet because my Excel 2000 doesn't support lists on a different sheet. But it definitely works with the list is on the same sheet.

As before, this code should go into the event code module of the worksheet on which the validated cell resides. This code works for every list-validated cell on that worksheet, but if you want to restrict it to just certain cells, this is easy to do with just one additional line of code. Let me know if you need that.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lcell As Range
On Error GoTo Fagedaboutit
If Target.Validation.Type = xlValidateList Then
With Target.Validation
If .Value Then
Application.EnableEvents = False
Set Lcell = Evaluate(Mid(.Formula1, 2)).Find(Target, lookat:=xlWhole)
Lcell.Copy
Target.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End With
End If
Fagedaboutit:
End Sub

 

[niecy123]

Re: Dropdown list values - how to maintain the original form

hi
Damon,
thanks for the code. it works fine. i might be asking too much here but how can i retain individual word formatting. So if i had a line say " His name is Joel" in the drop down list. then how can i just get the word Joel bolded and the highlighting remains the same for the rest of the line.

Joel