How do I have a cell value displayed on my user form?

[Davespil]

Hello, I'm trying to get the data in cell B23 to be displayed in my userform. B23 will change depending on what information is selected from combo "PartNumberComboBox" through vlookup. I want the value for B23 displayed as soon as a selection is made from the combo box. The user can make several selections from the combo box so the B23 will change a lot. I would prefer to display B23 as a Label, but a text box will work just as well. Here is the code I have but it doesn't work:

Private Sub NomenclatureLabel_Activate ()
NomenclatureLabel.Caption = Range ("B23").Value
End Sub

Thank you in advance.

[offthelip]

If you use a text box, then select the properties of the text box and set the control source to =B23, the text box will display what is in B23 all the time. I think you will have a problem putting the Vlookup values straight into B23 , so I suggest you set up the Vlookup elsewhere on the worksheet and then use the worksheet change event to copy the lookup value to B23.

[SamT]

Private Sub PartNumberComboBox_Change()
Vlookup code here
'
'
'
Application.Calculate or Sheets("Sheet1").Calculate as indicated
NomenclatureLabel.Caption = Range ("B23").Value
End Sub

[mdmackillop]

Use a combobox event, Click, Change or some other that suits

Private Sub ComboBox1_Click()
Label1.Caption = Range("B23")
End Sub

[Davespil]

Thank you for your responses but I am still unable to get it to work correctly. The problem is definitely on my side. I am not a programmer.

mdmackillop, I was unable to get anything to display in the combo box.

offthelip, Your suggestion worked the first time I selected something. But, if I selected anything else the nomenclature label would not change. It was also overwriting the vlookup formula in B23.

SamT, The vlookup formula is in cell B23. But I used the rest of your code and nothing happened:
Private Sub PartNumberComboBox_Change()
NomenclatureLabel.Caption = Range ("B23").Value
End Sub

So, I tried a few different things. When I changed “Change” to “Click” the nomenclate would be displayed and would change when I clicked on the label. Close, but I need it to change automatically. When I changed “Change” to “Activate” nothing happened.
I am sorry to keep bothering you but thank you in advance for all of your help.

[mdmackillop]

Can you post a workbook with sample data?

[offthelip]

offthelip, Your suggestion worked the first time I selected something. But, if I selected anything else the nomenclature label would not change. It was also overwriting the vlookup formula in B23.

This was why I was suggesting copying the value to B23 using the worksheet change event. If you want to keep the vlookup in B23, an alternative way of doing this is to set the control source to some other cell eg B24 and then copy the value of B23 to B24 on the worksheet change event. Note you must turn events on and off while you do the copy:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(24, 2).Value = Cells(23, 2).Value
Application.EnableEvents = True
End Sub

[SamT]

but I need it to change automatically.

B23 has a formula in it. What change is made to the worksheet that causes its Value to change.

PLease post all the code from the User Form.

[offthelip]

B23 has a formula in it. What change is made to the worksheet that causes its Value to change.

PLease post all the code from the User Form.

The OP stated "The user can make several selections from the combo box so the B23 will change a lot."
B23 is changed by the selection made on the combobox. The text box on the userform doesn't have any code associated with it, just set the control source to B24, and then it will reflect what is in B24 all the time ( including overwriting it). When B23 is changed by changing the selection on the combo box, the worksheet change event is triggered and so the copy from b23 to b24 is made.
An alternative would be to put the copy into the worksheet "calculate" event, I think either would work. Or even both!!

[SamT]

@ offthelip,

Are you 100% sure of that? It is the assumption that all suggestions have been made on and it is not working yet.

Are you certain that the OP has perfect knowledge of VBA and Excel and is a perfect communicator?

It's not working, so, at this point we need to throw out assumptions and get more data.

I have been doing this for a couple of weeks now, I think know what I am doing.

[offthelip]

Rather than just making a suggestion, I thought I would try this out: the basic idea of setting the control source of a text box on the userform to B24 does work, the text box updates to the value in B24 all the time. My assumption that the vlookup changing would trigger the worksheet change event was incorrect, it doesn't seem to do it. However I have got my method to work by putting the copy from B23 to B24 into the combo box change event so the code is:

Private Sub ComboBox1_Change()
    Application.EnableEvents = False
    Cells(21, 2).Value = ComboBox1.Value
    Cells(24, 2).Value = Cells(23, 2).Value
    Application.EnableEvents = True
End Sub

Hopefully I have managed to attach the demo worksheet that I have generated which shows this working.

[SamT]

Since a label doesn't have a ControlSource, and since I prefer to use Labels for display-only data,

Private Sub PartNumberComboBox_Change() 
'any other combo box code here
    Application.Calculate
DoEvents
    Me.NomenclatureLabel.Caption = Range ("B23").Value 
End Sub

[Davespil]

Since a label doesn't have a ControlSource, and since I prefer to use Labels for display-only data,

Private Sub PartNumberComboBox_Change() 
'any other combo box code here
    Application.Calculate
DoEvents
    Me.NomenclatureLabel.Caption = Range ("B23").Value 
End Sub

Thank you! It worked perfectly! I'm sorry I haven't been able to post my code but my company restricts what content I can display.