ListBox - Need lots of help!

[mtdewrocks]

I need some help with a listbox. I have gotten the very basics where I have the listbox showing up and I have the choices showing up. However, right now if I click on any of the potential options, nothing happens.

What I have my code doing is looping through survey responses - then if a region is blank on the survey, it pops up a msgbox and tells them the region is empty and that they need to input a region. Once they click okay, the userform shows up with the appropriate choices they can choose from.

I understand I need an event procedure for when they double click, but I am not sure what the code should be to recognize that they double clicked, and allow what they selected to be returned and entered into another variable.

I.e. Survey 1's region is blank. Please click okay and select the appropriate region.
' User selects "CHI"
' Then I want CHI to be returned and entered into my Region variable which is then put into the appropriate region cell based on what row the loop is on.

Any help?!

 

[RickXL]

Hi,

This could be the beginnings of such a program. It loops over the data in column A of Sheet1 and if one is blank it pops up a userform with a listbox. The userform could have messages on it explaining the problem. It could have a "Submit" button as well but I just the fact that a line was selected to fill in the empty cell. Then it moves on to the next empty cell.

This code needs to be placed in a standard code Module:

Sub Test()
    Dim i  As Long
    With ThisWorkbook.Worksheets("Sheet1")
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If .Cells(i, "A") = "" Then
                UserForm1.Show
                .Cells(i, "A") = UserForm1.ListBox1.Value
            End If
        Next
    End With
End Sub

And this need to be code in the UserForm module:

Private Sub ListBox1_Change()
    UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()
    ListBox1.RowSource = ThisWorkbook.Worksheets("Sheet1").Range("D1:D10").Address
End Sub

Note: I placed my look up table in D12:D10 of the same sheet just for testing.

Regards,

 

[mtdewrocks]

That worked great. Three follow up questions - how can I set it up for to check if the region is not = to "KC" or "DAL" or "CHI"?

I tried an If Not which I am not that familiar with and I get a type 13 mismatch error.

Second question: I have tried changing it so they need to double click and I get the following error: Compile error - Procedure declaration does not match description of event or procedure having the same name? What does this mean and how do I fix it?

Lastly, do you know of any sites (besides this one) or tutorials that can help me understand coding for userforms and list boxes. I would really like to get better with those as I have more things I want to do.

Thanks so much!

 

[05newtothis]

Hi,

I have found a youtube channel,Wise Owl, he has lots of tutorials for VBA. He explains this very well, in my opinion.

 

[mtdewrocks]

Yes I like wise owl videos as well. I'm on video 15 or 16 of the 45 in the series. I haven't gotten to the form videos yet.

 

[RickXL]

I have tried changing it so they need to double click and I get the following error: Compile error - Procedure declaration does not match description of event or procedure having the same name? What does this mean and how do I fix it?

OK, second part first.

Please explain how you expect your code to work. I know you have a list with some gaps and you want to fill them in using a ListBox on a UserForm.

I don't know where the master list will be kept or how the checking process will be triggered. You mentioned looping but you also expect something to be initiated by a Double-Click. Please just talk me through what you are expecting.

If you can include in there where you want the codes: "KC" or "DAL" or "CHI" excluding from exactly that would be beneficial. Do you want any entries with those values to be deleted or just prevent new ones being added or something else?

This is a good introduction to Excel and VBA. It is slightly out of date but it is free! https://grabebook.files.wordpress.com/2011/12/excel-2007-vba-programmers_reference.pdf

Regards,

 

[mtdewrocks]

I have a list of data and the region should be filled in - however, I have a loop running (which is running fine) to check if the field is empty. However, I want to change that in the loop so if the region is not completed with either "KC", "DAL", OR "CHI", then I want a msgbox to pop up informing them of the fact that the region is invalid for that row and after that have the userform show up to select the correct region.

The reason I want this is because I am creating pivot tables and I do not want to have both kc and KC. Also, if they type CK or KCC or any variation incorrectly, I want that to be flagged for correction which I am trying to allow them to do with the UserForm -List Box.

If the region is filled in incorrectly - i.e. it says * or CK Then I want the listBox to pop up.

Then I want the selection to be transferred to the region box AFTER the user doubleclicks rather than single click in case they inadvertently single click the wrong region.

If Worksheets("MASTER").Cells(Cert, 5) = "" Then ----I tried putting If Not "KC" or "DAL" or "CHI" Then --- however, this didn't work and I got the type mismatch error.

BankName = Cells(Cert, 4).Value
CertNumber = Cells(Cert, 3).Value
'Saves the Bank Name and Cert Number for the row with a missing Region as the variables Bank Name and CertNumber
MsgBox BankName & "'s" & " (Cert Number " & CertNumber & ")" & " region is empty. Please click OK and select appropriate region."

This is where I want the userform to pop up and then have the user select the correct region.

I believe the next line of code should be -- Cells(Cert,5) = UserForm1.SelectRegion.Value (I have renamed the listbox as SelectRegion rather than listbox1)

Lastly, what do I put in the private sub to force the user to doubleclick to make the selection rather than just a single click so I do not get the compile error I mentioned in my previous post?

Thanks so much for your help!