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 ??
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?
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
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.
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.
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:
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
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.
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
Thanks Roy, tried that but nothing at all happened - maybe I put it in the wrong place. I have attached the excel file - the password if you need it is 1280. I have tried to keep it as simple as possible. Thanks very much for your help. [ATTACH]n1223999[/ATTACH]
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.
Private Sub cbAdd_Click() UserForm1.Show Unload Me End Sub Private Sub cbExit_Click() ThisWorkbook.Save Application.Quit End Sub Private Sub cboName_Change() TextBox5.Text = Application.VLookup(cboName.Value, Worksheets("Department").Range("A1:F130"), 2, False) TextBox6.Text = Application.VLookup(cboName.Value, Worksheets("Staff").Range("A1:F130"), 3, False) TextBox7.Text = Application.VLookup(cboName.Value, Worksheets("Staff").Range("A1:F130"), 4, False) Textbox8.Text = Application.VLookup(cboName.Value, Worksheets("Staff").Range("A1:F130"), 5, False) TextBox9.Text = Application.VLookup(cboName.Value, Worksheets("Staff").Range("A1:F130"), 6, False) End Sub Private Sub cbClear_Click() Dim ctl For Each ctl In Me.Controls If TypeOf ctl Is MSForms.TextBox Then ctl.Text = "" End If Next ctl End Sub Private Sub ListBuilding_Click() Dim X As Integer X = ListBuilding.ListIndex Select Case X Case Is = 0 ListStaff.RowSource = "staff1" Case Is = 1 ListStaff.RowSource = "staff3" Case Is = 2 ListStaff.RowSource = "staff6" Case Is = 3 ListStaff.RowSource = "staff11" Case Is = 4 ListStaff.RowSource = "staff19" Case Is = 5 ListStaff.RowSource = "staff21" Case Is = 6 ListStaff.RowSource = "staff22" Case Is = 7 ListStaff.RowSource = "staff24" Case Is = 8 ListStaff.RowSource = "staff51" Case Is = 9 ListStaff.RowSource = "staff80" Case Is = 10 ListStaff.RowSource = "staff82" End Select End Sub Private Sub ListStaff_Click() TextBox1.Text = Application.VLookup(ListStaff.Value, Worksheets("staff").Range("A1:F130"), 4, False) TextBox2.Text = Application.VLookup(ListStaff.Value, Worksheets("staff").Range("A1:F130"), 5, False) TextBox3.Text = Application.VLookup(ListStaff.Value, Worksheets("staff").Range("A1:F130"), 6, False) TextBox4.Text = Application.VLookup(ListStaff.Value, Worksheets("staff").Range("A1:F130"), 3, False) End Sub Private Sub UserForm_Initialize() ListBuilding.List = Worksheets("Staff").Range("A2:F130").Value ListBuilding.RowSource = "Building" End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True MsgBox "Please use the Quit PhoneBook button to close the Phone Book", vbOKOnly End If End Sub