Solved

Excel Userform Active Cell from Listbox Selections

Posted on 2011-03-22
14
754 Views
Last Modified: 2012-05-11
Hello
Have the standard excel dbase to jazz up our asset register.  All working well, however I cannot for the life of me get the active cell selected from my listbox control.

I have a find button which shows more than one record, however when a line is selected I need it to actively select that row on the worksheet so that I can delete that line if necessary.  I know I need to set something as the active row and defined this as c at the start, but when the original find is done, it goes to the first active cell only.  I need to be able to select a row from the listbox and have the active cell selected.

Excel 2010, Windows 7
Thanks in advance
Private Sub ListBox1_Click()
    If Me.ListBox1.ListIndex = -1 Then    'not selected
        MsgBox " No selection made"
    ElseIf Me.ListBox1.ListIndex >= 1 Then    'User has selected
        r = Me.ListBox1.ListIndex
        
        With Me
            .puntxt.Value = ListBox1.List(r, 0)
            .wkstntxt.Value = ListBox1.List(r, 2)
            .dpttxt.Value = ListBox1.List(r, 3)
            .unmtxt.Value = ListBox1.List(r, 4)
            .proftxt.Value = ListBox1.List(r, 5)
            .emailtxt.Value = ListBox1.List(r, 6)
            .pwrdtxt.Value = ListBox1.List(r, 7)
            .cmbAmend.Enabled = True      'allow amendment or
            .cmbDelete.Enabled = True     'allow record deletion
            If ListBox1.List(r, 8) = "Yes" Then
                .optYes = True
            ElseIf ListBox1.List(r, 8) = "No" Then
                .optNo = True
            End If
        End With
    End If
              End Sub

Open in new window

0
Comment
Question by:mjbdbase
  • 7
  • 6
14 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35195582
You can find the item by using the listbox.selected property

if listbox1.listcount<> 0 then
  for i = 0 to listbox1.listcount-1
    if listbox1.selected(i) = true then exit for
  next i
 
  'do something with the selected row, which is i
end if

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35195931
>>>I need it to actively select that row on the worksheet so that I can delete that line if necessary.

mjbdbase: Few questions

1) Is the listbox in the spreadsheet or a userform?
2) Is the listbox a direct representation of the spreadsheet data?
3) May I see a sample file?

Sid
0
 

Author Comment

by:mjbdbase
ID: 35196042
Hi Sid
Would rather not show a sample file as it is loaded with company information. The listbox is a control on a userform and the listbox IS a direct representation of the spreadsheet data.

A find button is pressed (after typing into the form). The form displays the first record on the spreadsheet matching, a message box pops up and says there is x amount of matching records and shows all matching records in a listbox on the bottom of the form.  

Behind the scenes, the first matched record shows as active, which is what I want. However, if the person clicks on another line in the listbox (i.e another matching record), then I need that cell to become active.  The information from the clicked line is showing up in the form itself, its just that the active record is not changing at all.

I have a delete button on this form and even though you can click a line in the list box and all the information shows correctly, when delete is clicked, the first matched record gets deleted not the active one.

Here is the delete code so you can see it is ok.

Hope this provides a bit more information.
Private Sub cmbDelete_Click()
    Dim msgResponse As String    'confirm delete
    Application.ScreenUpdating = False
    'get user confirmation
    msgResponse = MsgBox("Do you really want to delete this record?", _
                         vbCritical + vbYesNo, "Delete Entry")
    Select Case msgResponse    'action dependent on response
        Case vbYes
            'c has been selected by Find button
             Set c = Range(ActiveCell, ActiveCell.Offset(0, 8))
            c.Delete Shift:=xlUp 'remove entry by clearing row and shifting cells up
            'restore form settings
            With Me
                .cmbAmend.Enabled = False    
                .cmbDelete.Enabled = False    
                'clear form
                ClearControls
            End With
        Case vbNo
            Exit Sub    'cancelled
    End Select
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35196088
>>>Would rather not show a sample file as it is loaded with company information.

That's ok :) I have the info that I need. If you could also paste the code that you have for find button. Let me create a sample for you :)

Sid
0
 

Author Comment

by:mjbdbase
ID: 35196105
Thanks Sid - I have posted the find and findall code.  I appreciate the assistance.

Cheers


Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = workstn.Range("b7", Range("b65536").End(xlUp))
   Dim f      As Integer
    strFind = Me.puntxt.Value    'what to look for
     With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .wkstntxt.Value = c.Offset(0, 1).Value
                .dpttxt.Value = c.Offset(0, 2).Value
                .unmtxt.Value = c.Offset(0, 3).Value
                .proftxt.Value = c.Offset(0, 4).Value
                .emailtxt.Value = c.Offset(0, 5).Value
                .pwrdtxt.Value = c.Offset(0, 6).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                If c.Offset(0, 7).Value = "Yes" Then .optYes = True
                If c.Offset(0, 7).Value = "No" Then .optYes = True
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                    Case vbOK
                        FindAll
                    Case vbCancel
                        'do nothing
                End Select
                Me.Height = frmMax

            End If
                   
        Else: MsgBox strFind & " doesn't seem to be on the list. You may have to use a different name"    'search failed
        End If
    End With
    If workstn.AutoFilterMode Then workstn.Range("B9").AutoFilter
   End Sub




--------------------------------------------------------------------------------------------------

Sub FindAll()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set rFilter = workstn.Range("B9", Range("b65536").End(xlUp))
    Set rng = workstn.Range("B8", Range("b65536").End(xlUp))
    strFind = Me.puntxt.Value
    With workstn
        If Not .AutoFilterMode Then .Range("B8").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        Me.ListBox1.Clear
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                'note that the first bracket set is the column number
                .List(.ListCount - 1, 1) = c.Offset(0, -1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 5) = c.Offset(0, 4).Value
                .List(.ListCount - 1, 6) = c.Offset(0, 5).Value
                .List(.ListCount - 1, 7) = c.Offset(0, 6).Value
                .List(.ListCount - 1, 8) = c.Offset(0, 7).Value
            End With
        Next c
    End With
End Sub

Open in new window

0
 

Author Comment

by:mjbdbase
ID: 35196107
Oh and upped the points for the extra help you have given.  Cheers
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35196117
Thanks. On to it. This will take some time as I will have to create a sample from the scratch.

Sid
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35196327
Quick question

In all the data that is similar, is there any column which is unique? i.e the data which is not repetitive and which I can use in the .Find in the listbox click event.

Sid
0
 

Author Comment

by:mjbdbase
ID: 35196359


In all the data that is similar, is there any column which is unique? i.e the data which is not repetitive and which I can use in the .Find in the listbox click event.

Yes.  You could use the puntxt - it is the username and is always unique?  I think that's what you meant?

Thanks
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 150 total points
ID: 35196369
>>>Yes.  You could use the puntxt - it is the username and is always unique?  I think that's what you meant?

Yes.

See if this is what you want?

Sid
Find.xls
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35196381
Once you confirm that the above is what you want then I will paste the code here and document it as well so that it is easy to understand.

Sid
0
 

Author Comment

by:mjbdbase
ID: 35196398
Thanks Sid
I am home from work now, but will back in the am.  Can I let you know then?  I really do appreciate the assistance.

Cheers
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35196401
Sure :)

Sid
0
 

Author Closing Comment

by:mjbdbase
ID: 35203125
Thanks Sid! I read through your code after I downloaded the sheet.  It made sense to me to just cut and paste the listbox1 code and voila! I know you aren't a point junky, so I just wanted to thank you very much for your quick and terribly clever response.  This is not what I do for a living so you have shortened my day doing this "geeky stuff" (sorry) and now I can do other "nerdy stuff".  On a serious note, just thank you, thank you thank you....I Australia we would say "your blood's worth bottlin mate!".  Cheers
M
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now