VBA UserForm wishlist

[Ser101]

Hi all, I'm relatively new to VBA and in need of some advice... I'm not even sure if what I want to do is possible?

I have 6 Workbooks - with multiple sheets in - that are all set up the same across each workbook, but the information within each is relevant to each account holder.

Sheet 1 is a welcome/instruction/useful links etc
Sheet 2 is a log of half the accounts he/she manages (alpha order)
Sheet 3 is a log of the other half of their accounts (alpha order)
Sheet 4 is a list of both sheets 2 & 3 together... in date order of when next to visit the individual clients (dates calculated in the original sheets... just viewing them both easier at once in order of next visit... rather than alpha client)

All the workbooks are the same, just personalised to each manager so they can handle their own client records
All the workbooks contain Macros (so have to be enabled when opening)

To work out which manager to asign to a new client... I currently have to manually check Sheet 4 in all six workbooks, and see who has the most availability. Then I have to go back into the chosen manager's workbook and insert a new client on Sheet 2/3 and book the induction visit on the agreed available date/time (this will then flag for the manager independantly).

What I want (http://www.mrexcel.com/forum/images/smilies/icon_eek.gif) is to have:

*ONE workbook that I use where I can open a userform (that pulls info from the other six workbooks)
*Have a list box in the userform that displays ALL the available visit dates/times from Sheet 4 in all the workbooks (and to tell me which workbook they're from)
- this would probably have to be displayed across two columns in the listbox (one for date, the other for time)
- and not display any rows in Sheet 4 that already have a visit scheduled
*And with some combo boxes: chose Account type // geography to narrow down which manager to allocate it to
(if i still have to choose by going through a smaller list of dates/time that's fine and probably preferable so I can offer variety to potential client)
*ONCE date/time is agreed: I want to be able to click the slot in the userform listbox and have it open the corresponding manager's workbook (for me - lazy I know)

As an absolute perfect task: I really want it to open the correct manager's workbook, and auto-load the create-client userform I have in there... with the PRE-POPULATED induction date/time that I chose a moment ago - so all I have to do is record the client information and hit save.

Is Any, or Half, or All of this at least possible?

thanks guys/gals!

 

[XLfriend777]

Hi Ser101 - As nobody has jumped in yet I thought I would help get things started. To begin with, if I understand your tasks correctly, I believe they are all doable. Have you thought of just having one workbook with separate sheets color-coded for each manager? With names something like Manager1.1, Manager1.2, Manager1.3 and so on for each manager. That way you would only have to open one workbook and things may be easier to program. Hope this helps get the discussion started.

 

[Ser101]

Hi ya,

Yes I have, Originally that was the set up - but with the need for macros and constant access/updating, it became to task orientated with calls of "Are you in it? how long till I can update my bit? etc" -- the VBAs negate the possiblity to share the workbook, so dividing the manager's up was the best solution from there.

*a counter of some description would be nice too?