Combo Box Items

BoringDavid · #1 12-11-2013, 04:37 AM

I use Word VBA to create templates for my colleagues to use for their letters, we have approx. 70 letters. So to try and standardise the letters I have created a master template with an "Address" userform that will capture information required for all letters, one of the items required is job title. I currently use a ComboBox with a list of the title to choose from.

The majority of our letters are used by our adjudicators so the master template has the following coding in module1:

Quote:

With Address
.ComboBox2.Text = "Complaints Adjudicator"
.ComboBox2.AddItem "Complaints Adjudicator"
.ComboBox2.AddItem "Complaints Signer/Authoriser"
.ComboBox2.AddItem "Complaints Support"
.ComboBox2.AddItem "Complaints Team Leader"
.ComboBox2.AddItem "Complaints Technical Adviser"
.ComboBox2.AddItem "Office of the Managing Director"
End With

However, some of our letters are either used by only by our Complaints Support team or primarily used by them. However, when I have amended the coding to this:

Quote:

With Address
.ComboBox2.Text = "Complaints Support"
.ComboBox2.AddItem "Complaints Adjudicator"
.ComboBox2.AddItem "Complaints Signer/Authoriser"
.ComboBox2.AddItem "Complaints Support"
.ComboBox2.AddItem "Complaints Team Leader"
.ComboBox2.AddItem "Complaints Technical Adviser"
.ComboBox2.AddItem "Office of the Managing Director"
End With

The UserForm still deafaults to Complaints Adjudicator (I've even deleted Complaints Adjudicator out of the AddItem list and it still appears!)... I have been unable to locate anywhere that maybe affecting this userform. Is there any advice you guys can give me?

macropod · #2 12-11-2013, 05:02 AM

It would be helpful if you attached the problem document/template to a post with the userform (delete anything sensitive), since the code on its own is unlikely to provide the answer. You do this via the paperclip symbol on the 'Go Advanced' tab.

BoringDavid · #3 12-11-2013, 06:51 AM

Quote:

Originally Posted by macropod

It would be helpful if you attached the problem document/template to a post with the userform (delete anything sensitive), since the code on its own is unlikely to provide the answer. You do this via the paperclip symbol on the 'Go Advanced' tab.

It won't allow me to upload it as a template so I saved it as a .doc (although we use 2010 at work our IT department have decided that we cannot save documents in .docx format), I have checked and the VBA appears to still be there.

macropod · #4 12-11-2013, 05:15 PM

Your autonew macro populates your userform's combobox with the reference to 'Complaints Adjudicator'. Nothing else that I can see changes what the combobox is populated with, so I can't see where the second code snippet in your first post comes into play - it's not even in the document code. You have four options for ComboBox3:
"Financial Ombudsman Service",
"Financial Services Ombudsman's Bureau",
"Pensions Ombudsman"
'Else'
Since there's nothing in the Combobox3 code for your Complaints Support team,
the 'Else' kicks in and you get 'Complaints Adjudicator'.

BoringDavid · #5 12-12-2013, 02:32 AM

Quote:

Originally Posted by macropod

Your autonew macro populates your userform's combobox with the reference to 'Complaints Adjudicator'. Nothing else that I can see changes what the combobox is populated with, so I can't see where the second code snippet in your first post comes into play - it's not even in the document code. You have four options for ComboBox3:
"Financial Ombudsman Service",
"Financial Services Ombudsman's Bureau",
"Pensions Ombudsman"
'Else'
Since there's nothing in the Combobox3 code for your Complaints Support team,
the 'Else' kicks in and you get 'Complaints Adjudicator'.

This is the master document, when I need ComboBox2 to state "Complaints Support" I amend .ComboBox2.Value = "Complaints Adjudicator" to .ComboBox2.Value = "Complaints Support".

Unless I'm missing something I feel the issue is likely to be with the module rather than the form coding, my reason for this is because when the form is launched ComboBox2 always states "Complaints Adjudicator" even if I remove "Complaints Adjudicator" from the module completely. The form code relates to when the Combo Boxes are changed, which come into play once the form has been launched.

Quote:

Originally Posted by fumei

BoringDavid, why do you have the additems in Module1. I would put it in the UserInitialize event.

I picked up the project after the person before left without finishing it, I ended up getting it because I am young and know what a mouse is... So everything is self-taught and a lot of what I am doing is based on my former colleagues work!

I hadn't thought of adding them to the UserInitialize Event (mainly because I haven't heard of a UserInitialize Event).

macropod · #6 12-12-2013, 04:08 AM

Quote:

Originally Posted by BoringDavid

when the form is launched ComboBox2 always states "Complaints Adjudicator" even if I remove "Complaints Adjudicator" from the module completely.

Not when I do so. There are four such references in your code.

BoringDavid · #7 12-12-2013, 04:50 AM

I've moved all the additems to a Private Sub UserForm_Initialize() as follows:

Code:

Private Sub UserForm_Initialize()
With ComboBox1
    .Value = "UK"
    .AddItem "UK"
    .AddItem "Ireland"
End With
With ComboBox2
    .Value = "Complaints Adjudicator"
    .AddItem "Complaints Adjudicator"
    .AddItem "Complaints Signer/Authoriser"
    .AddItem "Complaints Support"
    .AddItem "Complaints Team Leader"
    .AddItem "Complaints Technical Adviser"
    .AddItem "Office of the Managing Director"
End With
With ComboBox3
    .Value = "Financial Ombudsman Service"
    .AddItem "Financial Ombudsman Service"
    .AddItem "Financial Services Ombudsman's Bureau"
    .AddItem "Pensions Ombudsman"
    .AddItem "Data Protection"
End With
With ComboBox4
    .Value = "sincerely"
    .AddItem "sincerely"
    .AddItem "faithfully"
End With
    TxtClerkName.Text = Application.UserName
 
    TxtCC.Enabled = False
    TxtCC.BackColor = RGB(194, 194, 194)
End Sub

However, when I change the ComboBox2 Value to "Complaints Support", it remains "Complaints Adjudicator" on the userform!

I have attached a blank copy of the document for you to see if I am missing anything!

fumei · #8 12-11-2013, 11:26 PM

Paul where are you seeing

"Financial Ombudsman Service",
"Financial Services Ombudsman's Bureau",
"Pensions Ombudsman"
'Else'

BoringDavid, why do you have the additems in Module1. I would put it in the UserInitialize event.

macropod · #9 12-11-2013, 11:31 PM

They're in the ComboBox3_Change sub - a series of If/ElseIf/Else tests. Unfortunately, the code layout's got no structure, so it's not easy to follow.

fumei · #10 12-12-2013, 03:22 AM

Code:

 Private Sub UserForm_Initialize()

Code:

With ComboBox1
  .Value = "UK"
  .AddItem "UK"
  .AddItem "Ireland"
End With
With ComboBox2
  .Value = "Complaints Adjudicator"
  .AddItem "Complaints Adjudicator"
  .AddItem "Complaints Signer/Authoriser"
  .AddItem "Complaints Support"
  .AddItem "Complaints Team Leader"
  .AddItem "Complaints Technical Adviser"
  .AddItem "Office of the Managing Director"
End With
With ComboBox3
  .Value = "Financial Ombudsman Service"
  .AddItem "Financial Ombudsman Service"
  .AddItem "Financial Services Ombudsman's Bureau"
  .AddItem "Pensions Ombudsman"
  .AddItem "Data Protection"
End With
With ComboBox4
  .Value = "sincerely"
  .AddItem "sincerely"
  .AddItem "faithfully"
End With
TxtClerkName.Text = Application.UserName
TxtCC.Enabled = False
TxtCC.BackColor = RGB(194, 194, 194)
End Sub

So there you go, ComboBox2 has the items. You have a combobox with things the user selects. They still can. If you want to force something, then do not give them a list to choose from. What is the actual problem?

Quote:

when I need ComboBox2 to state "Complaints Support"

But why? Why do YOU need it to state Complaints Support; the combobox is there for the user to choose.

I have to strongly agree with macropod, the structure is poor. Try to use indents. There are a few other issues ( using Selection for the bookmarks, perhaps using Select Case rather than IF statements...), but I understand this is new thing for you and do not want to overly comment.

BoringDavid · #11 12-12-2013, 03:31 AM

Quote:

Originally Posted by fumei

So there you go, ComboBox2 has the items. You have a combobox with things the user selects. They still can.

Thank you!

Quote:

Originally Posted by fumei

If you want to force something, then do not give them a list to choose from. What is the actual problem?

Quote:

when I need ComboBox2 to state "Complaints Support"

Quote:

Originally Posted by fumei

But why? Why do YOU need it to state Complaints Support; the combobox is there for the user to choose.

It is for documents that are primarily used by our Complaints Support team, but could be used for others, this way it will pre-populate with the most likely choice but gives the option of using a different one if necessary.

Quote:

Originally Posted by fumei

I have to strongly agree with macropod, the structure is poor. Try to use indents. There are a few other issues, but I understand this is new thing for you and do not want to overly comment.

Thank you agin for your help!

macropod · #12 12-12-2013, 05:09 AM

So now you have -
Combobox3 : Combobox2
"Financial Ombudsman Service" : "Complaints Adjudicator"
"Financial Services Ombudsman's Bureau" : "Authorised Person"
"Pensions Ombudsman" : "Nominated Complaints Person"
"Data Protection" : "Complaints Adjudicator"

The form shows that because that's what you have coded.

BoringDavid · #13 12-12-2013, 05:14 AM

Quote:

Originally Posted by macropod

So now you have -
Combobox3 : Combobox2
"Financial Ombudsman Service" : "Complaints Adjudicator"
"Financial Services Ombudsman's Bureau" : "Authorised Person"
"Pensions Ombudsman" : "Nominated Complaints Person"
"Data Protection" : "Complaints Adjudicator"

The form shows that because that's what you have coded.

Ah, I understand now! D'oh!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Combo Box	Hinchy	Word	4	09-04-2012 05:31 AM
combo box in word	wperry	Word	1	03-22-2012 06:53 AM
Combo Box in whole column	ioustinos	Excel	1	09-01-2010 01:24 AM
Outlook 2007 Saved sent items list only holds the last ten items	david.peake	Outlook	0	06-01-2010 07:27 PM
Bold list items in a combo box	DrewB	Word VBA	0	07-17-2009 11:32 AM