Solved

Excel Userform Active Cell from Listbox Selections

Posted on 2011-03-22
14
878 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 42

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 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