PDA

View Full Version : double click on listbox



saban
02-01-2006, 06:38 AM
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

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
02-01-2006, 08:23 AM
Hi saban,

I haven't tested this at all, but how about:
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

saban
02-01-2006, 08:28 AM
no it doesnt work it is still the same

Ken Puls
02-01-2006, 08:38 AM
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
02-01-2006, 08:58 AM
just $A$1 I guess it should be fully qualified

Thnx for your help

Ken Puls
02-01-2006, 09:06 AM
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
02-01-2006, 09:12 AM
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 ..

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

I had my listbox populated as such ..

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

HTH

saban
02-01-2006, 09:13 AM
:) 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
02-01-2006, 09:16 AM
i get error invalid outside call or procedure when inserting your code

saban
02-01-2006, 09:18 AM
ups sory i didnt populate list like you
I will try
thnx

saban
02-01-2006, 09:22 AM
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
02-01-2006, 09:38 AM
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
02-01-2006, 10:02 AM
Zack

The double click will return the value from the listbox.

This worked for me.
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

saban
02-03-2006, 09:04 AM
thnx guys for all your help i will try and let you know

saban

saban
02-03-2006, 09:19 AM
Application.Goto Range(addy) i get this error global failed here
any ideas why

Norie
02-04-2006, 05:59 AM
What value does addy have when you get the error?

Can you attach a sample workbook?

johnske
02-04-2006, 06:41 AM
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

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 thatOption 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 SubShould work ok...

Andy Pope
02-04-2006, 01:17 PM
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
02-05-2006, 08:35 AM
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
02-05-2006, 09:23 AM
What search are you referring to?

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

saban
02-05-2006, 01:54 PM
POption Explicit

'Module Level Variables
Dim addy As Range

Dim rrange1 As Range
Dim Ws As Worksheet
Dim rRange As Range
Dim strFind1 As String
Dim strFind2 As String
Dim strFind3 As String



Private Sub CheckBox1_Click()
ListBox1.MultiSelect = fmMultiSelectMulti
If CheckBox1 = False Then
ListBox1.MultiSelect = fmMultiSelectSingle
End If
End Sub

Private Sub ComboBox1_Change()
'Pass chosen value to String variable strFind1
strFind1 = ComboBox1
'Enable ComboBox2 only if value is chosen
ComboBox2.Enabled = Not strFind1 = vbNullString
End Sub
Private Sub ComboBox2_Change()
'Pass chosen value to String variable strFind1
strFind2 = ComboBox2
'Enable ComboBox3 only if value is chosen
ComboBox3.Enabled = Not strFind2 = vbNullString
End Sub
Private Sub ComboBox3_Change()
'Pass chosen value to String variable strFind1
strFind3 = ComboBox3
End Sub

Private Sub CommandButton1_Click()
'Procedure level variables
Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range
Dim rCell2 As Range
Dim rCell3 As Range
Dim bFound As Boolean
Dim sestej As Long
Dim osheet As Object
Dim rrange1 As Range
Dim Ws As Object
'Clear any old entries
ListBox1.Clear

'At least one value, from ComboBox1 must be chosen
If strFind1 & strFind2 & strFind3 = vbNullString Then
MsgBox "Izbran ni bil noben kriterij", vbCritical
Exit Sub 'Go no further
ElseIf strFind1 = vbNullString Then
MsgBox "A value from " & Label1.Caption _
& " must be chosen", vbCritical
Exit Sub 'Go no further
End If


On Error Resume Next

On Error GoTo 0

'If String variable are empty pass the wildcard character
If strFind2 = vbNullString Then strFind2 = "*"
If strFind3 = vbNullString Then strFind3 = "*"

'Set range variable to first cell in table.
Set rCell = rRange.Cells(1, 1)
'Pass the number of times strFind1 occurs
lOccur = WorksheetFunction.CountIf(rRange.Worksheet.Columns(1), strFind1)


For Each Ws In ActiveWorkbook.Worksheets
Set rrange1 = Ws.Range("A1:A1000")
'Loop only as many times as strFind1 occurs
For lCount = 1 To lOccur


'Set the range variable to the found cell. This is then also _
used To start the Next Find from (After:=rCell)
If strFind1 <> "" Or Not strFind1 = False Then

With Ws
Set rCell = rrange1.Columns(1).Find(What:=strFind1, After:=rCell, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'Check each find to see if strFind2 and strFind3 occur _
on the same row.
If rCell(1, 2) Like strFind2 And rCell(1, 4) Like strFind3 Then
bFound = True 'Used to not show message box for no value found.
'Add the address of the found cell and the cell on the _
same row but 2 columns To the right.

ListBox1.AddItem rCell.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = rCell.Offset(0, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 2) = rCell.Offset(0, 2).Value
ListBox1.List(ListBox1.ListCount - 1, 3) = rCell.Offset(0, 3).Value
ListBox1.List(ListBox1.ListCount - 1, 4) = rCell.Offset(0, 4).Value
ListBox1.AddItem rCell.Address & ":" & rCell(1, 3).Address
ListBox1.AddItem "To je v worksheetu:" & Ws.Name
ListBox1.AddItem "___________________________________________________________________________ ____________________________"
ListBox1.ControlTipText = "Dvoklikni na naslov celice in ne na besedilo (naslov :$A$2:$A$2)"
End If

If rCell(1, 2) Like strFind2 And rCell(1, 4) Like strFind3 Then
sestej = WorksheetFunction.Sum(rCell(1, 3)) + sestej
TextBox1.Text = sestej
End If



End With

End If



Next lCount
Next Ws


If bFound = False Then 'No match
MsgBox "Zapis s tem kriterijem ne obstaja", vbOKOnly
End If

End Sub

Private Sub CommandButton2_Click()
'Close UserForm

Unload Me
End Sub


Private Sub CommandButton3_Click()
Dim iListCount As Integer, iColCount As Integer
Dim iRow As Integer
Dim rStartCell As Range


'Set a range variable to the first cell to recieve our data
'Using "End(xlUp).Offset(1, 0)" _
will give us the cell below the last entry
Set rStartCell = Sheets("Izpisi").Range("A65536").End(xlUp).Offset(1, 0)

'Loop as many times (less one) as there are entries in our list.
'We must start from zero to use this in the Selected Property.
For iListCount = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iListCount) = True Then 'User has selected
ListBox1.Selected(iListCount) = False 'Deselect it
iRow = iRow + 1
'Now loop as many times as there are columns in MyRange
For iColCount = 0 To Range("$A$1:$E$1").Columns.Count - 1
'place the selected data into the table, starting from _
range Ax and moving across as many columns as there are _
in the range MyRange.
rStartCell.Cells(iRow, iColCount + 1).Value = _
ListBox1.List(iListCount, iColCount)

Next iColCount
End If
Next iListCount
MsgBox "izbrano besedilo skopirano v Izpisi", vbInformation
Set rStartCell = Nothing

End Sub

Private Sub CommandButton4_Click()
frmTomarDatos.Show
End Sub




Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.Goto Range(ListBox1.List(ListBox1.ListIndex))


End Sub







Private Sub UserForm_Initialize()
'Procedure level module
Dim shttemp As Worksheet

For Each shttemp In ActiveWorkbook.Worksheets
ListBox1.AddItem "'" & shttemp.Name & "'!A1"
Next


Dim lRows As Long

'Set Module level range variable to CurrentRegion _
of the Selection

Set rRange = Selection.CurrentRegion
If rRange.Rows.Count < 2 Then ' Only 1 row
MsgBox "Postavi se na zacetek tabele (v celico A1)", vbCritical
Unload Me 'Close Userform
Exit Sub
Else

With rRange
'Set RowSource of ComboBoxes to the appropriate columns _
inside the table
ComboBox1.RowSource = .Columns(1).Offset(1, 0).Address
ComboBox2.RowSource = .Columns(2).Offset(1, 0).Address
ComboBox3.RowSource = .Columns(4).Offset(1, 0).Address
End With
End If
End Sub

Private Sub UserForm_Terminate()
'Destroy Module level variables
Set rRange = Nothing
strFind1 = vbNullString
strFind2 = vbNullString
strFind3 = vbNullString
End Sub


sorry guys here is the search code

johnske
02-05-2006, 10:56 PM
Hi Saban,

I'm sure this can be fixed and possibly even trimmed down considerably, but at the moment there are things like the role of variables (e.g. strFind1, 2, and 3). Just by reading the code alone it is difficult to know where/what they are referring to, as well as other issues such as - it's hard to determine what the initial worksheet layout is and what the intent of the code is without an example.

I think at this stage the best way to go would be to remove any sensitive data, zip and post the workbook so we can get a better picture and be able to work on the problem more directly.

Regards,
John :)

saban
02-06-2006, 04:09 AM
here is the sample

saban
02-06-2006, 04:11 AM
here is the sample