ComboBox update help and Textbox population help

Hi all,

(NOTE: this is now a seperate query to my previous thread)

I am (still) trying to create a userform that looks a the selection made by a user from combobox, and then references cells on a data sheet in 8 separate textboxes on the userform.

So for example: The user selects Project A from the combobox. This selection comes from a range on worksheet A (Live Tracker) called projectTitles. This selection is also mapped to a cell on worksheet B (Data) via the "ControlSource" property of the combobox in VBA (I did this as I saw this method used by someone else and it seemed to work well).

On the Data worksheet, below the ControlSource mapped cell, are eight cells setup with VLOOKUP to map each development stage that the project has gone through from the Live Tracker worksheet.

These cells look at the ControlSource value (which reflects the project titles from the combobox on the userform, that range is from the Live Tracker worksheet), and moves across from the project column to each QA (development) stage cell on the Live Tracker worksheet. See below for an example:

The highlighted column below has the following formulae:

=VLOOKUP(J1,'Live Tracker'!B9:M182,5,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,6,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,7,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,8,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,9,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,10,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,11,FALSE)
=VLOOKUP(J1,'Live Tracker'!B9:M182,12,FALSE)

Project A
POD QA >>N/A <<
Playlist QA >>N/A<<
1st Full QA >>N/A<<
Fixes 1 >>Complete<<
Pre-Master >>N/A<<
Fixes 2 >>N/A<<
Shipping >>Complete<<
Test Disc >>TBC<<

Back on the userform, the eight textboxes located below the combo box will reflect each of these stages populated by the VLOOKUP, using the following (dodgy) code:

 Private Sub titleComboBox1_Change()

Me.podQATextBox = Worksheets("Data").Range("J2").Value
   Me.playlistQATextBox = Worksheets("Data").Range("J3").Value
      Me.firstQATextBox = Worksheets("Data").Range("J4").Value
         Me.fixes1TextBox = Worksheets("Data").Range("J5").Value
            Me.premasterTextBox = Worksheets("Data").Range("J6").Value
               Me.fixes2TextBox = Worksheets("Data").Range("J7").Value
                  Me.shippingTextBox = Worksheets("Data").Range("J8").Value
                     Me.testDiscTextBox = Worksheets("Data").Range("J9").Value


End Sub

The problem I’m experiencing with this userform is that the text boxes do not instantly reflect the selection the user has made from the drop down. What normally happens is the user needs to select the title from the drop down, and then press enter, and then the textboxes will populate. However, if the user then selects another title, presses enter, the textboxes don’t always update correctly.

I would really like two things to sort this problem out. Firstly, on selection of the title from the drop down, I’d like the selection to be made without the need for the user to press enter or select the next cell. Secondly, I’d like the textboxes to reflect the user’s selection from the combobox instantly; i.e. with every choice made in the drop down, the textboxes should instantly reflect this QA stages. This will probably be dependent on whether or not the first solution can be made.

If anyone can help me with these problems, I’d be eternally grateful!!

Kind regards,

A Grace