UDF - goto cell based on dropdown list value

[Daktari]

Hello,

I'm trying to get cell from a dropdownlist to act as a shortcut to a cell on another worksheet.
My dropdownlist is in cell c8, and consists of cell text which are mirrored on sheet2 in column B.
I named that column intList, and so far the UDF only takes my to cell b1 on sheet2.

How do I tweak my UDF to jump to the cell which is named the same as my dropdownlist choice?
ie if I choose "apples" in my list, and "apples" is in cell b200 on sheet2, how do I make it go there?
I'm willing to hand out golden man babies (yes, that old beated down phrase).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim intList As String
If Not Application.Intersect(Range("c8"), Target) Is Nothing Then
Set rng1 = Sheets("sheet2").Range("b1:b1000").Find(intList)
        
        If Not rng1 Is Nothing Then
            Application.Goto rng1, True
        End If
End If
End Sub

 

[Firefly2012]

You need to assign a value to intList which in the above code you don't appear to be doing. Perhaps:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim intList As String
If Not Application.Intersect(Range("c8"), Target) Is Nothing Then
    intList = Range("C8").Value    
    Set rng1 = Sheets("sheet2").Range("b1:b1000").Find(intList)
        
        If Not rng1 Is Nothing Then
            Application.Goto rng1, True
        End If
End If
End Sub

 

[Daktari]

Thanks for answering.

I just tried to set a value to my c8 as per your suggestion, and put it in .find()
The UDF is still unresponsive. I've no clue how to proceed. I thought I was close when I got it to change sheet.

 

[Firefly2012]

Have you tried stepping through the code - eg place a breakpoint on the opening If line and then go and change C8 in the sheet. See what values are returned as you step thru the code using F8.

This isn't a UDF by the way - it is a sub using the sheet events. You may confuse people by referring to it as a UDF.

 

[Daktari]

Your answer helped, thanks. I had some issues with break text and merge cells which caused it not to recognize the cell. I'm a monkey with a computer.

However, I got a new issue:
I have dependent dropdown lists in c7, c10 and c13 (the new c8)
Whenever I chose something from that list in c7 and c10, it automaticly jumps to sheet2. Is there an easy fix to delay the jump to sheet until I've choses a value in c13?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("c7")) Is Nothing Then
        Range("c10,c13").Value = ""
    End If
    If Not Intersect(Target, Range("c10")) Is Nothing Then
        Range("c13").Value = ""
    End If

Dim rng1 As Range
Dim intList As String
If Not Application.Intersect(Range("c13"), Target) Is Nothing Then
    intList = Range("c13").Value
    Set rng1 = Sheets("sheet2").Range("b1:b1000").Find(intList)
        
        If Not rng1 Is Nothing Then
            Application.Goto rng1, True
        End If
End If
    
End Sub

 

[Firefly2012]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("c7")) Is Nothing Then
        Range("c10,c13").Value = ""
    End If
    If Not Intersect(Target, Range("c10")) Is Nothing Then
        Range("c13").Value = ""
    End If
Application.EnableEvents = True
Dim rng1 As Range
Dim intList As String
If Not Application.Intersect(Range("c13"), Target) Is Nothing Then
    intList = Range("c13").Value
    Set rng1 = Sheets("sheet2").Range("b1:b1000").Find(intList)
        
        If Not rng1 Is Nothing Then
            Application.Goto rng1, True
        End If
End If
    
End Sub

 

[Daktari]

You can use enableevents for that? Regardless, it did the trick. You've made my day some shades brighter!

There isnt any way for users to edit the tread title to remove the UDF part and change it to solved is there?

 

[Firefly2012]

No I'm afraid not - moderators can do that however (remove the UDFbit) but I wouldn't worry overly much about it.

This forum doesn't mark its threads as SOLVED (unlike some others) on the gorunds that that might dissuade others from contributing their potentially better solutions.