I work for a state road authority and have been developing a spreadsheet for responding to technical inquiries from consultants/other govt agencies/general public (ie it’s a not for profit venture). I did manage to convince my manager to shell out for some funds to get a consultant in but they have since been and gone and some new issues have arisen that leave me reluctant to ask for more funds (they aren’t really there). As such I have been winging it myself scanning the web for snippets that might help me.
Anyway I’m having problems relating to lists associated with combo boxes and some other aspects that I’d really love some help with (I’ve tried Mr Excel etc but sometimes they run hot and cold with responses). I’ll try and explain the last few things I’m trying to get done.
A series of option buttons are associated with who the inquirer is (ie OB1 for internal, OB2 for consultant/contractor, OB3 for other govt and OB4 for general public). OB1 and OB4 are all sorted. For OB2 and 3 clicking on either first brings up an input box asking to narrow it down (ie OB2 click brings “Consultant or Contractor?”). For each of these two option buttons I have two combo boxes. The first asks for the company/govt dept name. THis this should refine the selection in CB2 for staff name. For example I have separate sheets for each type of enquirer. Each sheets contains employer name in ColA, Staff name in COl B, staff email and phone in Col’s C and D respectively. Selecting the company name in CB1 means that it should then go to that sheet, do a match to count how many examples of that employer are there and then only return staff names (in CB2) that are associated with that employer. Selecting the staff name would then do lookup functions and dump their email and phone in text boxes on the main sheet. I can get it to work out how many examples of each emplyer there are but for some reason it won’t display the staff names in CB2, it keeps referring to the corresponding cells on the first sheet (ie instead of placing range B1:B6 from Sheet 2 it writes B1:B6 from sheet 1 - the sheet where the code is written).
Additionally I was trying to get it such that if that staff member does not yet exist in the database then manually writing it into the combo boxes and clicking a Command Button (update database) would then go to the relevant sheet (Consultant/Govt Dept etc), add the Company/Staff Name/Email/Phone onto the next available row and then do an alphabetical sort on ColA then Col B for future use.
For people that know their way around I think this is probably pretty basic but I’m learning (not really an IT type) and as such don’t know the correct way (tricks etc) of writing the code to make it work properly (or more efficient ways of doing it).
Any assistance would be great as I’m stressing out big time - and won’t to focus on my IM race in 6 weeks!!!
CHeers
MIke