Combobox value / Text Box value

Question

Good day

I was wondering if anyone could help me with this query.  I have a user form which has a combo box that has been populated with values via additem command.  i want the selected entry in the combobox to relate to a worksheet which has the corresponding data in it and extract certain data from this to populate the textbox in the user form with it.  eg the combo box data will pick up the "Area Office X" and look for this in the worksheet row that has the various area offices.  once it has identified the relevant area office, i want it to identify the area manager whichmay be 4 cells below in the same column and place this into the textbox of the same user form as that of the combobox.

I have attached a link to show the layout of the worksheet in question

https://www.dropbox.com/s/cow4i7yk89l4dhc/Book1.xlsx

Thanks in advance

Colin

Answer 1

Hi,

 

I don't see any userform or code in the workbook you linked to. Have a look at this version of your workbook on my SkyDrive, it's call book1 mike.xlsm.

 

It has the code to populate a combox on the userform I've added and add the manager name to a text box.

 

 

https://skydrive.live.com/?cid=66a66ea84229b01b&sa=680348902#cid=66A66EA84229B01B&id=66A66EA84229B01B%21108

Thanks Mike this worked upto a point, The combo box populated ok, when selecting the entry for the combo box it didn't seem to want to put the contents of the cell for the second part, ie areas managers name,  into the text box in the user form  I tried putting the second part under a procedure section for the combo box change

 

Any suggestions?

 

Colin

 

Hi,

I don't understand that. The code I put in the workbook is below and for me when the userform initialises the combobox is populated and when I make a selection in the combobox the manager name is populated into the textbox on the userform.

Did you alter the workbook I created?

Private Sub ComboBox1_Change()

Dim c As Range

With Sheets("sheet1").Range("B5:K5")

 Set c = .Find(ComboBox1.Text, LookIn:=xlValues, LookAt:=xlWhole)

End With

TextBox1.Text = c.Offset(4).Value

End Sub

Private Sub UserForm_Initialize()

With Sheets("Sheet1")

For x = 2 To 11

    ComboBox1.AddItem .Cells(5, x).Value

Next

End With

End Sub

Answer 2

Hi,

 

I don't see any userform or code in the workbook you linked to. Have a look at this version of your workbook on my SkyDrive, it's call book1 mike.xlsm.

 

It has the code to populate a combox on the userform I've added and add the manager name to a text box.

 

 

https://skydrive.live.com/?cid=66a66ea84229b01b&sa=680348902#cid=66A66EA84229B01B&id=66A66EA84229B01B%21108

Thanks Mike this worked upto a point, The combo box populated ok, when selecting the entry for the combo box it didn't seem to want to put the contents of the cell for the second part, ie areas managers name,  into the text box in the user form  I tried putting the second part under a procedure section for the combo box change

Any suggestions?

Colin

Answer 3

Thanks Mike

Unfortunately the network won't allow me access to the file so i'll have to try accessing it fromhome, i think you have the gist of what iswas saying, the user form is working with another worksheet, it is addressing the one shown in the link and on selecting a certin area office it will select the cell with the appropriate area manager, insert this into the text box of the user form and on execution of the user form ok button it will take all the data and insert it  into a specific worksheet.

Thanks

Colin

Answer 4

Mike, not surprising - the file is a .xlsx file, and no pieces of the VBA Project are saved with .xlsx - not VBA Code nor other parts of the project, such as a UserForm.  The OP needs to save as .xlsm!

Answer 5

Hi,

I don't see any userform or code in the workbook you linked to. Have a look at this version of your workbook on my SkyDrive, it's call book1 mike.xlsm.

It has the code to populate a combox on the userform I've added and add the manager name to a text box.

https://skydrive.live.com/?cid=66a66ea84229b01b&sa=680348902#cid=66A66EA84229B01B&id=66A66EA84229B01B%21108