Clear a UserForm

    Hi all, I am a real newbie to vba, however I am developing a userform to show staff names (populated in a combobox through row source) and contact numbers on the top portion, and the bottom of the form contains buildings (in the first list box) that show occupants (in the second list box) that will then show contact numbers. All contact numbers are displayed in text boxes. When I hit the Clear button, only the text boxes clear, and no matter what I try, the combobox and the listbox stay populated. Also, is there any way of stopping an error (debug) from occurring when a name is misspelt or you backspace to a blank in the combobox. I've tried adding in a blank row to no avail - and I wouldn't have a clue how to add a wildcard entry if that would work. Thanks in advance for any help. Everything else works fine - I'm pretty chuffed with what I have achieved thus far - Code is attached - if you need the complete file - let me know.


    Code



    Code/

    Place this code in a Standard Module





    What i

    Code
    Option Explicit


Private Sub cmdClear_Click()
ClearAll Me
End Sub



    This will clear all TextBoxes, reset CheckBoxes and OptionButtons to False and also clear selection in ComboBOxes and ListBoxes. It can be called by any UserForm in the workbook.


    If you attach an example of the workbook I will look at the code, which can probably be improved.


    For example, the code below makes no sense. You are using two methods to load the ListBox, the second will overwrite the first. Using the ListBox's List Property is generally the best way to load a ListBox or ComboBox


    Code
    ListBuilding.List = Worksheets("Staff").Range("A2:F130").Value ListBuilding.RowSource = "Building"

    Thanks Roy - like I said - I'm really new to writing code etc and there is no doubt in my mind that I did put it in the wrong place. The only listbox I don't want to clear is the building listbox - you'll understand once you see the form. Cheers

    I have added my code to clear the controls.


    I have also made some changes to make the code more efficient.


    The Names that refer to the Staff are dynamic because I have used Tables. This will allow you to add or delete names in the lists and be reflected in the code and reduce the lines of code when loading the ListBox. I have also changed the names so that I could add a different method of loading the required list into the ListBox.


    Check it out and post back with specific questions.

    Hi Roy - what you have done is great, however when you select a name from the combobox it adds the incorrect data into the textboxes. It adds the next line down - not the current line. Also, the building listbox now contains all the staff names, not the building numbers. It was from this listbox I could see what staff were in each building by clicking on the building number, select there name from the adjacent staff listbox and then have it display their position, ext etc in the below textboxes. A little convoluted I know but easy for me to find people I need to find [if that makes sense].
    Really love the fact that you have kept the code simple as well, makes it easier to read. I also saw where you had put the code you originally sent me, and I did have it in the right spot, so I'm pretty stumped. Thanks once more, legend.

    Why do you load the ListBuilding twice? I can see why I was mistaken, I thought you were populating it with the first line below


    Code
    ListBuilding.List = Worksheets("Staff").Range("A2:F130").Value
ListBuilding.RowSource = "Building"

    Try this


    Staff List only has one name, will this always be the case because I will need to edit the code to allow for this.

    Hi Roy, yes Staff List will always only have that one name - this is working great except, and I really am sorry to say that, I get a run-time error '381' when I clear the form - "Could not get the List property. Invalid property array index.


    It is referring I believe to this:


    Code
    Me("TextBox" & iX + 4).Value = Me.cboName.List(Me.cboName.ListIndex, iX)


    As far as the below code is concerned, bad choice of words on my behalf - that is simply to prompt me to click there to get to the spreadsheet to either add or edit :)


    Code
    Private Sub cbAdd_Click()
''/// this wont add anything
    UserForm1.Show
    Unload Me
End Sub


    Cheers mate

    Ok Roy, been working on this all day and have solved a few things - only two left. 1. Can't seem to figure out that run time error, and it only occurs if there are entries in the cboName. 2. Try as I might, and now brain numb, where or how do I populate the staff list box associated with the building listbox - remembering the staff list box is populated by clicking on a building in the preloaded building list box - I need to match them up properly.


    Thanking you immensely for all your hard work and effort thus far - it is appreciated.

    I've fixed the error and amended the code in ListBuilding_Click to allow for Staff 2 on;y having one entry


    When you click on the ListBuildings it populates the names associated with that building, is that not correct?

    Hey Roy, have everything running a dream, many thanks to you. One very last question - you wrote in the code "''/// there are more than 10 names in the ListBuilding, should there be another list?" - is that important? Reason I ask is that the very last building - Building 82 - won't/can't load the names associated with it where all the others do perfectly. Is that because it is the 11th building in the list, or shouldn't it matter.


    Hope you have a great weekend champ - no need for a quick reply, enjoy your time off.

    Hey Roy - all good, I have fixed it - everything is now working exactly as I had hoped. Thank you ever so much for your kind assistance, especially trying to mentor/show/put up with an old man trying to learn new tricks :)


    Have a great day


    PS How to I show this as solved

    Sorry for the delay in getting back to you Roy, been away and ultimately shelved this for a week or so. Fix - nothing you did, working perfectly so to speak, I had to fix my end to make it correlate with the code you gave me. All good. Have one last problem to solve - don't know if you're interested in helping - trying to keep the userform open all the time, workbook (test.xlms) hidden all the time, until an editor calls for it via the edit/password options I have. At the moment, as soon as I open another workbook it also opens the test.xlms workbook, and similarly when I close another workbook, it either closes or makes visible test.xmls. Is there anyway at all that test.xmls can be made to behave as a stand alone?

    Hi Roy, the file will be put on a generic server, so anyone will have access to open it from the server. What are your thoughts - addin ??

    Sharing files in Excel is generally problematic. If you create an addin then that can be stored on the server but would need to be installed on each user's computer.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login