Posted to microsoft.public.excel.misc
|
|
Excell Dropdown List. Display alternate text than found in li
I don't think that Data|validation is that customizable.
Shawnn wrote:
OK thank you sooo much that worked great! Last issue.. the dropdown list is
only as wide as the cell. Is there a way to keep the cell size but force the
dropdown list to be as wide as the text inside?
"Shawnn" wrote:
sorry i did not see that post.... I am working on it now. Thanks Bro.
"Dave Peterson" wrote:
Only what I suggested before.
Shawnn wrote:
First, thanks for all the help! Next, I got the multiple cells to work as
long as they are using the same list. I am having a problem getting other
cells to do it for different lists.
Here is my code:::
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
If Target.Cells.Count 1 Then
Exit Sub
End If
If Intersect(Target, Me.Range("N7, AE7")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("Weekday"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("Weekday").Offset(0, 1)(res).Value
End If
End With
Application.EnableEvents = True
End Sub
Private Sub object_Change()
Dim res As Variant
If Target.Cells.Count 1 Then
Exit Sub
End If
If Intersect(Target, Me.Range("AI9")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("Security"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("Security").Offset(0, 1)(res).Value
End If
End With
Application.EnableEvents = True
End Sub
As you can see, I want AI9 to read from the list called security, not
weekday. I will need to repeat this for another 20 or so cells... any advise?
"Dave Peterson" wrote:
I'd try changing this line:
If Intersect(Target, Me.Range("N8")) Is Nothing Then
to something like:
If Intersect(Target, Me.Range("N8:N18,L22:m25,x15")) Is Nothing Then
Shawnn wrote:
Ok I will try that. In the mean time... how do i do this for multiple cells
and lists? Lets just say I have 10 different areas i need to use this code.
How would I set that up?
"Dave Peterson" wrote:
First, merged cells cause nothing but grief. I do my best to avoid them.
But I merged N8:P8 and this seemed to work ok for me. I also named the range
List (not DID). In xl2007, DID will look like a column, so I wouldn't use that.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
If Target.Cells.Count 1 Then
Exit Sub
End If
If Intersect(Target, Me.Range("N8")) Is Nothing Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Worksheets("Sheet2")
res = Application.Match(Target.Value, .Range("List"), 0)
If IsError(res) Then
MsgBox "Design error!" 'this shouldn't happen
Else
Target.Value = .Range("list").Offset(0, 1)(res).Value
End If
End With
Application.EnableEvents = True
End Sub
Shawnn wrote:
I have several areas that require a code similar to a state abbreviation.
For example the word Statutory Burglary mast be recorded as SB. So I have
set up multiple colums on Sheet2. A1:A10 are titles, B1:B10 are the
abreaviations. The next is C1:C10 are titles and D1:D10 are abbreviations
and so on.
I used the DV dropdown in my document cell N8 (which is a merged cell) to
show the list from A1:A10. Once the ull name is selected I need it to show
the abbreviation in the form. I need this for many areas in my report.
I used the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 14 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Sheet2").Range("B1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Sheet2").Range("DID"), 0), 0)
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub
I would be happy to send you a copy if it will help.
"Dave Peterson" wrote:
Nope.
Maybe you could share some details.
The address of the range that holds the names and the id's.
The cell that's getting the data|validation.
And the code that you tried.
Shawnn wrote:
I would use an adjacent cell to make this project much easier on me.
Unfortunately I have to follow strict state reporting guidelines. With that
being said, I have tried for several hours to duplicate the example in the
download you provided but I keep getting errors. The example is exactly what
I want to do though. Do you know of a step by step guide?
"Dave Peterson" wrote:
You could use some code to replace the name with the id number.
But I would would use an adjacent cell so that I could display both.
If A1 contained the name (a cell with data|validation???), then in B1:
=if(a1="","",vlookup(a1,sheet2!a:b,2,false)
Where Sheet2 contained the list of names in column A and the ID's in column B.
But if you want, Debra Dalgleish has a sample workbook he
http://contextures.com/excelfiles.html#DataVal
Look for:
DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.
DataValCode.zip 8 kb
Shawnn wrote:
I have a form that uses a dropdown list to show employee names. What I want
to do is allow the names to be selected in the dropdown list but the employee
number to actually be inserted into the form. Is this even an option?
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|