While working with PowerPivot in Excel 2010, I noticed the Ask Questions command on the Ribbon.
When you click that command, your web browser opens, and takes you to the PowerPivot Help forums on the Microsoft website.
That’s a nice feature, if you’re struggling with PowerPivot.
Where Do You Ask Excel Questions?
Why doesn’t Excel have an Ask Questions command? We have questions too! (A moment of silence for the old Excel newsgroups.)
The closest thing that I could find is a Contact Us command, which leads to the Microsoft website.
Microsoft Answers Forum
At the bottom of that page is a link that leads to the general Answers form – not to an Excel related forum. It takes a couple more clicks to get to the Excel section.
Ask Excel Questions
Maybe that Ask Questions command will be in the next version of Excel. Until then, here are links to the Excel help forums on the Microsoft site.
Answers – http://answers.microsoft.com/en-us/office/forum/excel
Technet — http://social.technet.microsoft.com/Forums/en/excel/threads
MSDN (Excel Developers) — http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads
MSDN (VBA) — http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads
There are links to other Excel forums in the following article that I wrote:
- The Excel Newsgroups Disappeared.
_________________
Option Explicit
‘ Developed by Contextures Inc.
‘ http://www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strVal As String
Dim i As Long
Dim lCount As Long
Dim Ar As Variant
On Error Resume Next
Dim lType As Long
If Target.Count > 1 Then GoTo exitHandler
lType = Target.Validation.Type
If lType = 3 Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = “” Then
‘do nothing
Else
If newVal = “” Then
‘do nothing
Else
On Error Resume Next
Ar = Split(oldVal, “, “)
strVal = “”
For i = LBound(Ar) To UBound(Ar)
Debug.Print strVal
Debug.Print CStr(Ar(i))
If newVal = CStr(Ar(i)) Then
‘do not include this item
strVal = strVal
lCount = 1
Else
strVal = strVal & CStr(Ar(i)) & “, ”
End If
Next i
If lCount > 0 Then
Target.Value = Left(strVal, Len(strVal) – 2)
Else
Target.Value = strVal & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
I used your code and removed the two areas you noted so it will work on any drop down menu in my spreadsheet. It works on column # 1, and #2. It will not work on column #17. Can you help me understand why?