Consulting

Results 1 to 7 of 7

Thread: VBA Change variables by macro

  1. #1

    VBA Change variables by macro

    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

  2. #2
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    This is really an Excel job to track things. Word is for writing letters.

  3. #3
    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...

  4. #4
    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-v...el-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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Senate1987 View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    Apr 2010
    Posts
    28
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •