VBA Change variables by macro

[Senate1987]

Hello
I am using a Word worksheet to keep track of my coworkers, if they were present and on which workstation they were.
To do that I've created a userform that takes the names of the employees out of an array. So far so good!

Now I'm getting more and more new employees, so that I need to change this array. But I'm the only one who knows how to do that, if I'm off work noone can handle this! Now I'd like to add to my worksheet a function to change the array but I don't really know how to do that...

Could you please help me?

Senate1987

[ranman256]

This is really an Excel job to track things. Word is for writing letters.

[Senate1987]

I have to work with what my company provides me, so that is a Word document thats used in the entire entreprise, but they use in most services simple Drop-Down lists, as they have a smaller amount of employees. This owrked fine for me to as long as it were less than 25, cause obviously this is the limit of elements in a Drop-Down list.
Having atteimpt this amount of people I had to change the way it worked so I decided to use a macro with a userform as there is no such limit.
So if I want it or not, I have to work with this sheet...

[gmayor]

You could store the employees list in an Excel worksheet and use the macro at the end of the thread - http://www.msofficeforums.com/word-vba/23144-populate-word-drop-down-list-excel-column.html - to fill the list box of your userform from the worksheet. No-one needs to understand anything about arrays. They only need to know how to add a row to a worksheet.

[snb]

If you use a Worddocument you can store the names of all employees in a document variable.
When opening the userform the combobox can be populated with those names.
In a combobox a name can be added/removed.
When closing the userform the names in the combobox can be stored in the documentvariable.


Sub M_snb()
thisdocument.Variables("employees")="name 01,name 02,name 03"
End Sub

Private Sub Userform_initialize()
combobox1.list=split(Thisdocument.variables("employees"),",")
End Sub

Private Sub Userform_QueryClose()
if combobox1.listcount>0 then thisdocument.variables("employees") = join(combobox1.list,",")
end Sub

[macropod]

I have to work with what my company provides me
What, so they provide you with Word but not Excel? Somehow I don't think so. And, since as you say:

Now I'm getting more and more new employees, so that I need to change this array. But I'm the only one who knows how to do that, if I'm off work noone can handle this!
you're sounding rather like this is something you've developed and inflicted in everyone else.

The easiest way to manage this is with an Excel list that anyone can update. The code posted by snb is not conducive to maintaining such a list (even in Word). And, if you can maintain the list in Excel, then why not use Excel for the whole task? Aside from anything else, Excel gives your company far richer tools for analysing the data at a later stage. Having the data there will make that task far simpler.

[Geoff]

I am sure that your employer would supply Excel which is the best way to go, but if you must use Word you could always list the employees in a simple plain text file created with Notepad (or with Word provided you remember to save it as plain text). It would be something like this:

"Fred Brown"
"Jane Grey"
"Susan Smith"
"Bill White"

If it is going to be a long list, reverse the names - "Brown, Fred", "Grey, Susan" to make it easier to sort.

You can let your colleagues amend the list.

Then just read the list into your combobox (let's call it cbxEmployees). There are plenty of people on here who can do a better job of this bit than I, but this is how I do it:

Public Sub LoadEmployeesList()

Dim limit As Integer
Dim employee As String
Dim file As Integer
Dim Employees() as string

file = FreeFile
Open PutYourFileNameHere For Input As file
limit = -1

ReDim Employees(20)

Do While Not EOF(file)
Input #file, employee

employee= Trim(employee)

If employee<>vbnullstring Then

limit = limit + 1

If limit > UBound(Employees) Then

ReDim Preserve Employees(limit + 20)
End If

Employees(limit) = employee

End If
Loop

Close file

ReDim Preserve Employees(limit)

WordBasic.SORTARRAY Employees() ' Puts them in alphabetical order


cbxEmployees()=Employees()

Exit Sub

But I am no expert!

Geoff