How To Populate A Text Box From A ComboBox In Visio

OldDogNewVisio · #1 07-20-2023, 11:39 AM

We use Visio for our SharePoint Site. I have been asked to streamline things. Instead of posting the entire org chart, I have created a ComboBox that allows visitors to our site to select the department they are looking for and I would like to have the name of the lead for that department populate in a text box next to the dropdown list. I have created the subroutine for the dropdown list that opens when the SharePoint opens. I have added the list of department names (Example) as follows:

ComboBox2.AddItem ("Accounting")
ComboBox2.AddItem ("Maintenance")
ComboBox2.AddItem ("Commissary")
ComboBox2.AddItem ("Sales")
ComboBox2.AddItem ("Field Reps")
End Sub

I have created the text box statement as a long string if statement (example) as follows:

If (ComboBox2.Value= "Accounting") Then Textbox1.Value = "Mary Smith"
ElseIf (ComboBox2.Value= "Maintenance") Then Textbox1.Value = "Fred Jones"
ElseIf (ComboBox2.Value= "Commissary") Then Textbox1.Value = "Jose Lima"
ElseIf (ComboBox2.Value= "Sales") Then Textbox1.Value = "Rick Nixon"
ElseIf (ComboBox2.Value= "Field Reps") Then Textbox1.Value = "Sandy Banning"

End If
End Sub

These are only examples. I can't post the real thing but they are close approximations of what I'm doing. How do I get the two pieces to work together? I have loaded the ComboBox statement on the "Document" page in the VB editor but where do I put the If Statement text for the TextBox?

Thanks so much!!

Guessed · #2 07-24-2023, 05:32 PM

I'm going to assume you have a vba Userform which is where the ComboBox2 and Textbox1 objects are sitting. The code should be located in that object (the userform itself).

In that userform you will need two macros that autoexecute (run when events happen that align with the name of the macro). So add this code to the userform module.

Code:

Private Sub UserForm_Initialize()
  Dim depts() As String
  depts = Split("Accounting|Maintenance|Commissary|Sales|Field Reps", "|")
  Me.ComboBox2.List = depts
End Sub

Private Sub ComboBox2_Change()
  Dim people() As String
  people = Split("Mary Smith|Fred Jones|Jose Lima|Rick Nixon|Sandy Banning", "|")
  If ComboBox2.ListIndex = -1 Then
    Me.TextBox1 = ""
  Else
    Me.TextBox1 = people(ComboBox2.ListIndex)
  End If
End Sub

OldDogNewVisio · #3 07-25-2023, 03:47 AM

Thank you so much for your response.

I'm not sure what a VBA Userform is. I searched the document and couldn't determine where I can find it. Please let me know where I need to go. I'm very new to Visio and know just enough to get into trouble.

Thank you,
ODNV

OldDogNewVisio · #4 07-25-2023, 09:10 AM

Thank you, thank you, thank you!!!! I implemented your code and it is almost perfect!!! There is one problem. In some cases, the same person is responsible for more than one department. In my list, it will give me the first selection for that person correctly, but if their name comes up again in the list, it will give me the next name in the list. How do I correct that?

Guessed · #5 07-25-2023, 03:52 PM

The code is set up so that there are two separate lists and the position in the lists is what links the department with the person. If a person runs two departments then their name has to appear twice in the people list to align with the two departments.

The strings used to populate the depts and people arrays are separated by the | character. Make sure that you have the same number of entries in depts as you do in people.

OldDogNewVisio · #6 07-26-2023, 03:20 AM

Thank you. Yes there are the same number of persons and departments in the lists and I made sure the spelling of the departments was the same between the two lists. I'm not sure if this makes any difference but there are 32 items in each list. Could that be too many for an If statement to sort through?

OldDogNewVisio · #7 07-26-2023, 03:56 AM

I found out where the issue was and I fixed it. Thank you again for your help!!!

OldDogNewVisio · #8 08-03-2023, 11:39 AM

Well, I have run into an issue. The boss didn't like the container I picked for the ComboBox so I had to create another one. Now my dropdown list doesn't work in it. I'm sure it's just an easy reference but where do I look to make the change?

Thanks so much,
ODNV

Guessed · #9 08-03-2023, 03:32 PM

In the code I provided, there are several references to the Name of the combobox. If you want the code to work on a different combobox then the name of that object will need to match the name referred to in the code.

Select the combobox and then look at the Properties section of your VBA editor to change its name. If you give it a more descriptive name then make sure that is reflected in the code

OldDogNewVisio · #10 08-09-2023, 02:59 AM

Thank you,

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to populate a userform combobox from a table in the same document	jrooney7	Word VBA	2	04-14-2019 06:30 PM
How to populate a userform combobox from an excel worksheet	jrooney7	Word VBA	14	09-16-2018 08:52 PM
Populate Combobox from Excel into a Word UserForm	ferguson4848	Word VBA	3	10-28-2016 09:05 PM
how to populate textbox based on combobox selection in word	IvanGeorgiev	Word	1	02-21-2013 07:32 PM
how to populate textbox based on combobox selection in word	IvanGeorgiev	Word VBA	1	02-21-2013 07:28 PM