0

So I want to launch a userform if a person has selected a cell that has listbox associated with it how can I detect it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
           'Check if Target cell has listbox?
End Sub
Community
  • 1
  • 1
Rohan
  • 319
  • 1
  • 5
  • 18
  • 2
    What do you mean by `ListBox`? Auto-complete ListBox? Data validation ListBox? Or are you thinking about a form control ListBox which is currently covering up a cell? Or maybe there is an ActiveX control ListBox which has (currently) the size of a cell and covers one or more cells? If you are talking about the latter then you should go through the `ListBoxes` collection and see which one has your cell in the `.TopLeft` corner: https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.listbox.topleftcell.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 – Ralph Mar 18 '17 at 16:06
  • Data Vallidation listbox – Rohan Mar 18 '17 at 16:10
  • With this post you can verify if there is a data validation in place for your cell: http://stackoverflow.com/questions/18642930/determine-if-cell-contains-data-validation AND with this post you can read the data validation rule / list: http://stackoverflow.com/questions/18893436/excel-vba-read-validation-list-into-variable – Ralph Mar 18 '17 at 16:16

1 Answers1

1

you could use this function:

Function HasValidation(rng As Range) As Boolean
    Dim validationType As Long

    validationType = -1
    On Error Resume Next
    validationType = rng.Validation.Type
    HasValidation = validationType >= 0
End Function

and exploit it in your event handler:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If HasValidation(Target) Then
        ... your code
    End If
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • What happens when Target is more than a single cell? Why pass `rng` into the function if you retrieve the `ActiveCell.Validation.Type` ? Isn't the active cell typically one row below target on a Worksheet_Change? –  Mar 18 '17 at 16:56
  • @Jeeped, you are right about multicell target issue, but I'll leave it to the OP to find the proper handling. `ActiveCell` is a typo from my Immediate Window testing... I'll edit the code to remove it. The event handler is a `SelectionChange` though ...Thanks – user3598756 Mar 18 '17 at 17:03
  • 1
    You're right of course - It is Worksheet_SelectionChange, not Worksheet_Change. In that case, Target.Cells(1, 1) should handle any multiple selection. –  Mar 18 '17 at 17:05