Combo Box additions not showing until after Userform closes and is reopened.

**eemiller1997** · 12-14-2012, 03:09 PM

I have a multi-page UserForm in Excel and after I entered a name into a ComboBox and it's related fields, I then press a button to add/update that information to an underlying worksheet called 'CandidateData', next I proceed with entering the next name into the ComboBox and so on. But, I found as I tested this process that I cannot find the names of those candidates I have just entered within the combobox drop down arrow - the names are not there. But, when I closed the UserForm to see if the candidates were in the CandidateData worksheet, they were there.

When I reopened the UserForm, I could then see the names of all three candidates entered in the ComboBox drop down. Then, when I add yet another name or two those new names are still not shown in the drop down of the Combobox, while the ones previously entered are seen. The new one's won't be seen until I close the UserForm and re-open it.

How can I fix this so they will be seen without having to close the UserForm?

**Norie** · 12-14-2012, 03:32 PM

Why would the names be there?

They aren't getting added to the combobox, they are getting added to the sheet.

If you want you could update the combobox with the code used to populate it in the listbox.

Just copy the 3 lines that do that and paste them at the end of the code to add a new candidate.

**eemiller1997** · 12-14-2012, 03:56 PM

When they are added to the sheet, the user is supposed to be able to use the drop down arrow of the combobox to re-select their desired candidates if needed.

I'm looking for the code that populates it into the listbox, but where is it? I'm assuming its under the frmPrintCand.... is it this code?

Please Login or Register  to view this content.

**Norie** · 12-14-2012, 04:05 PM

The code is in the userform Initialize event, that code has nothing to do with it - it doesnt' even refer to the combobox.

Off the top of my head it looks something like this.

Please Login or Register  to view this content.

**eemiller1997** · 12-18-2012, 11:33 AM

Alright. I have added these three lines, having copied them from elsewhere, to the bottom of the code for the Add/Update command button.... just before End Sub. When I tested it I got an error: Runt-Time Error '424': Object required, and it pointed me to this line of code:

Please Login or Register  to view this content.

**Norie** · 12-18-2012, 11:36 AM

Isn't this line of code from the print form?

Please Login or Register  to view this content.

**eemiller1997** · 12-18-2012, 11:56 AM

Yes, that is what you had put up above, the 3 lines of code to copy over. The three lines of code that pupulat the listbox.

**Norie** · 12-18-2012, 12:01 PM

That is not one of the lines I meant.

lstCandidates is a control on the print form, not the main form.

Code that refers to that control should not be in the main form.

**eemiller1997** · 12-18-2012, 12:19 PM

You had said to

[quote]
If you want you could update the combobox with the code used to populate it in the listbox
[/quote}

What other code populates this information in the listbox?

When I search in the IntvwWorksheet I find the following that mentions cboCandidate:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

And under the Add/Update I find:

Please Login or Register  to view this content.

But, I don't think that one is it.

**Norie** · 12-18-2012, 12:31 PM

That was a typo.

What you need to do is add the code to populate the combobox at the end of the code that add's a new candidate to the worksheet.

These are the lines of code that populate the combobox.

Please Login or Register  to view this content.

The If was added to take care of there only being one canidate.

What I suggest you do is this.

Create a new sub called PopCandidateCombo in the userform module.

Paste the above code into the sub.

Please Login or Register  to view this content.

Now change the userform Initialize event code to this which calls the new sub.

Please Login or Register  to view this content.

At the end of the code to add a new candidate add this.

Please Login or Register  to view this content.

Now when you add a new candidate the combobox should get updated.

**eemiller1997** · 12-18-2012, 12:38 PM

Yay! I think it worked just fine. I'll test it a few more times. I want to clarify, I added 'Call PopCandidateCombo' to the end of the userform Initialize event code. Thank you once again for your help!

**eemiller1997** · 12-18-2012, 12:40 PM

Yes! It's working. Thank you

**Norie** · 12-18-2012, 12:42 PM

No you don't add it to the end of the code, you replace the exisiting code with it.

If you just add it at the end of the existing code you'll be populating the combobox twice.

**eemiller1997** · 12-18-2012, 12:44 PM

Good to know. Thank you. I will do that. And, it seems to be working just find, too. Thanks again

**eemiller1997** · 12-19-2012, 04:33 PM

I just realized that by removing this code from the UserForm_Initialize ()

Please Login or Register  to view this content.

That, it reverted me back to this problem with the Run-Time Error '381'.

**Norie** · 12-19-2012, 07:44 PM

I'm not sure I understand. Why did you remove that code?

You need that code to populate the candidate combobox.

**eemiller1997** · 12-20-2012, 09:19 AM

Originally Posted by Norie

No you don't add it to the end of the code, you replace the exisiting code with it.

If you just add it at the end of the existing code you'll be populating the combobox twice.

That is why I replaced it, but the code I removed was intended to allow only one candidate to be entered and still work when printed. However, re-adding that code into the Initalize event isn't solving the problem. My debugging is highlighting the Private Sub UserForm_Initialize (). Here is the entire code:

Please Login or Register  to view this content.

What has gone wrong?

**eemiller1997** · 12-20-2012, 09:23 AM

Okay, it looks like I had too many End Subs with that particular code. But, I still cannot print when there is only one single candidate. This time I was given the Run-Time Error '381': Could not set the List Property. Invalid Property Array Index. Debugging hilighted "lstCandidates.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value" under the following code found in the print userform:

Please Login or Register  to view this content.

But, this is what happened last time and we had had it fixed - see thread: http://www.excelforum.com/excel-prog...ray-index.html

**eemiller1997** · 12-20-2012, 09:24 AM

I'm wondering if that code needs to go into that intialize event under the print userform that I just provided in the last post?

**Norie** · 12-20-2012, 01:35 PM

First you need to delete this code from the main form's Initialize event.

Please Login or Register  to view this content.

This calls the sub PopCandidateCombo which populates the combobox.

Please Login or Register  to view this content.

All you need in the main form's Initialize event is that line of code and the code to populate the first two pages.

By the way, this isn't quite right:

'placed here so ComboBox additions will show without having to close Userform.

The combobox is updated when a new candidate is added by calling PopCandidateCombo again.

Also, this code has nothing to do with printing or the print form.

The problem with the print form when there is only one candidate is the same as there was with the combobox on the main form.

To fix the print form we can use similar code to that we used for the main form.

**eemiller1997** · 12-20-2012, 02:59 PM

Great! This is what I have for the frmPrintCand Initialize event:

Please Login or Register  to view this content.

**Norie** · 12-20-2012, 03:04 PM

That looks about right, does it work?

**eemiller1997** · 12-20-2012, 03:18 PM

Yes, it appears to be working

Combo Box additions not showing until after Userform closes and is reopened.

LinkBack

Thread Tools

Rate This Thread

Display

Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Re: Combo Box additions not showing until after Userform closes and is reopened.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions