Populate userform label from cell

[tbuilt77]

I have a vlookup cell in a spread sheet. I want a userform label to display the value from the vlookup in a note on the userform. I can't figure out how to link a cell reference to a userform label. Any suggestions?

The label on the userform would say "The primary production line for this product is ??VLOOKUPVALUE?? Which production line would you like to schedule?"

The user then selects either the primary production line or another production line from a combo box drop down list.

 

[My Aswer Is This]

Put this script in your UserForm:
This script assumes your VLookUpvalue is in Range("B2")
This script assumes you label is named Label1 which is a default name
This just does not do this:

The user then selects either the primary production line or another production line from a combo box drop down list.

Private Sub UserForm_Initialize()
'Modified  11/15/2018  3:57:00 AM  EST
Dim ans As String
ans = Range("B2").Value
Label1.Caption = "The primary production line for this product is  " & ans & "  Which production line would you like to schedule?"
End Sub

​

 

[tbuilt77]

Thanks for he help. I tried it with no success. Does it matter that I am opening the user form during another macro with the userform.show command? Not sure if the userform_initialize () sub is connected to the .show???

Thanks again for any assistance.

 

[Fluff]

Which sheet & which cell has the vlookup?

 

[tbuilt77]

Sheet name is OrderCard, Cell where vlookup is located is "Y5".

 

[Fluff]

Try

Private Sub UserForm_Initialize()
Dim Msg As String
Msg = Sheets("OrderCard").Range("Y5").Value
Me.Label1.Caption = "The primary production line for this product is  " & Msg & "  Which production line would you like to schedule?"
End Sub

 

[tbuilt77]

Still unable to get it to work. It seams so simple but I must be missing something

Here is my simple macro for printing. It calls up the User form UFFactoryComboBox using UFFactoryComboxBox.Show. I change the value in the combo box, I hit a submit button on the user form and it updates the spreadsheet with the combo box selection and then prints a card from a desired range.

Sub print1()
' print1 Macro

UFFactoryComboBox.Show

' Range("d19:g46").Select
' Application.CutCopyMode = False
'Selection.PrintOut From:=1, To:=5, Copies:=1, Collate:=True
' Range("P15").Select

'Range("d291:g319").Select
'Application.CutCopyMode = False
'Selection.PrintOut From:=1, To:=5, Copies:=1, Collate:=True
'Range("P15").Select
End Sub


And here is the code attached to the user form



Private Sub UFFactoryComboBox_Initialize()
Dim PrefFact As String
PrefFact = Sheets("OrderCard").Range("Y5").Value
Me.Label2.Caption = PrefFact
End Sub


Private Sub CBSubmitFactory_Click()


Dim ChosenFactory As String
ChosenFactory = ComboBoxSelectFactory.Value
ActiveSheet.Range("Y6").Value = ChosenFactory
Unload Me
End Sub

 

[Fluff]

Do not change the name of the Initialise event.
It should be as both MAIT & I showed.

 

[tbuilt77]

Thanks again for your input, it still is not working how I expect it to. If you have additional input, I would love to figure it out so I can learn what I am doing wrong for future endeavors. I have changed my approach and added a text box that lets me use cell Y5 as the ControlSource without coding it in the the user form. Should have tried this earlier but I am trying to expand my skill set.


When you say, "Do not change the name of the Initialize event." Are you suggesting that my code should read exactly the same as you have shared regardless of what I have named my user form? Private Sub UserForm_Initialize()
My user form is named UFFactoryComboBox that is why I changed the initialize event name. If I leave the name UserForm then I get a Run-time error '9'. If I change the name in the code to the name of my user form it completes the macro but the caption on the label never changes.

 

[Fluff]

When you say, "Do not change the name of the Initialize event." Are you suggesting that my code should read exactly the same as you have shared regardless of what I have named my user form? Private Sub UserForm_Initialize()

Yes that's exactly what I'm saying

If you change the UserForm to the actual name of the form, it will never run. Hence the label doesn't change.
When you get the error which line is highlighted?