double click on listbox

[saban]

[VBA]Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(ListBox1.Text), True
End Sub[/VBA]

This code is for: when you double click in cell address which appears in list box it selects the row in which is the record, but when record is on other sheet it does not show that sheet neither does select the row how do i do that

[Ken Puls]

Hi saban,

I haven't tested this at all, but how about:
[vba]Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Range(ListBox1.Text).Parent.Activate
Application.Goto Range(ListBox1.Text), True
End Sub[/vba]

[saban]

no it doesnt work it is still the same

[Ken Puls]

Saban,

What is the value of Listbox1.Text when it won't work? Is it a named range, a cell address ($A$1), a fully qualified address (Sheet2!$A$1)...

[saban]

just $A$1 I guess it should be fully qualified

Thnx for your help

[Ken Puls]

Yes, will definately need to be fully qualified. It sounds like it's doing what you told it to and not what you want it to. LOL!

If you need any more assistance with it, don't heistate to post back.

[Zack Barresse]

From what I understand, Listbox1.Text will not return you the doubleclicked item. You would need to loop through all the items and get the selected item. This works for me ..

[vba]Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long, Pos As Long
Dim strSheet As String, strAddy As String
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then Pos = i: Exit For
Next i
If i = .ListCount Then Exit Sub
strSheet = Left(.List(i), InStr(1, .List(i), "!") - 1)
strAddy = Right(.List(i), Len(.List(i)) - Len(strSheet) - 1)
Sheets(strSheet).Activate
Range(strAddy).Activate
End With
Unload Me
End Sub[/vba]

I had my listbox populated as such ..

[vba]Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Worksheets.Count
With Me.ListBox1
.AddItem Sheets(i).Name & "!A1"
End With
Next i
Me.ListBox1.ListIndex = 0
End Sub[/vba]

HTH

[saban]

you are right i hate this to

One stupid question how do i fully qualify it

Thnx for all your time and patience
saban

[saban]

i get error invalid outside call or procedure when inserting your code

[saban]

ups sory i didnt populate list like you
I will try
thnx

[saban]

I need first to do the serach and then populate listbox does this change anything
because when i put your code in I already have list box filled before i do the search

[Zack Barresse]

It depends on how you populate the listbox. I just populated it with values such as...

Sheet1!A1
Sheet2!A1
Sheet3!A1
...

I used the ! character as a seperator to parse out the sheet and the address.

[Norie]

Zack

The double click will return the value from the listbox.

This worked for me.
[vba]Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim addy As String
addy = ListBox1.Text
Unload Me
Application.Goto Range(addy)
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Worksheets.Count
With Me.ListBox1
.AddItem Sheets(i).Name & "!A1"
End With
Next i
Me.ListBox1.ListIndex = 0
End Sub[/vba]

[saban]

thnx guys for all your help i will try and let you know

saban

[saban]

[VBA] Application.Goto Range(addy) [/VBA] i get this error global failed here
any ideas why

[Norie]

What value does addy have when you get the error?

Can you attach a sample workbook?

[johnske]

[VBA]Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Check for range addresses
If ListBox1.ListCount = 0 Then Exit Sub
'GoTo doubled clicked address
Application.Goto Range(ListBox1.Text), True
End Sub[/VBA]

This code is for: when you double click in cell address which appears in list box it selects the row in which is the record, but when record is on other sheet it does not show that sheet neither does select the row how do i do that

[vba]Option Explicit

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.Goto Range(ListBox1).EntireRow
Unload Me
End Sub

Private Sub UserForm_Activate()
Dim N As Long
For N = 1 To Worksheets.Count
ListBox1.AddItem Sheets(N).Name & "!A1"
Next
End Sub[/vba]Should work ok...

[Andy Pope]

Another usual suspect for an error is space in the sheet name.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Application.Goto Range(ListBox1.List(ListBox1.ListIndex))
    
End Sub
Private Sub UserForm_Initialize()
    Dim shttemp As Worksheet
    
    For Each shttemp In ActiveWorkbook.Worksheets
        ListBox1.AddItem "'" & shttemp.Name & "'!A1"
    Next
End Sub

[saban]

guys i need to double click in listbox when search is done not before
when i double click when userform initialize it works fine but it is not to much help for me first i need to do the search then i get results together with cell address and then i must double click on cell address and it should activate sheet that this address is in

any ideas

[Norie]

What search are you referring to?

I don't see any searching going on in your posted code.