Populating Text Box in Userform with Label Value

[mayerc]

Hi. Afraid my coding ability is useless and would appreciate some asistance.

I have created a multi page form as a wizard for managers to fill out a questionairre about their employees. Each page contains certain "attributes" which the manager has to score the employee against with a score of 1 or 2 (radio buttons). For example, one attribute may be 'time keeping' and they are either "1" - bad or "2" - good

Becasue each attribute is specific I have inlcuded Guidance for each one to help the manager decide which is more appropriate. I have added a command button against eact attribute that the user can click on.

When a command button is pressed a pop up appears with a text box.

What I need to happen is when a parituclar command button is pressed that the text box in the pop up form is populated with the name of the attribute the user wants guidance on. I have attempted to give the textbox the value of the 'label' on the main userform that corresponds to the attribute but it has not worked.

It seems really straight forward as I thought it would be similar to populating a textbox with data from a cell but I can't get it to work, any help is greatly appreciated.

Many Thanks

Chris

[RonMcK]

mayerc,

Can you possibly upload a copy of your workbook (remove any proprietary data that would id the company or employees, substituting generic data)? This would be very helpful for us to see what you've tried and how you've structured you project.

Thanks,

[mayerc]

Hi

Thanks so much for helping. I have attached the file I am having issues with. For your information the attributes are actually engineering 'skills' (I said timekeeping for ease of understanding).

When you open there is a pop up with instructions, and then a information gathering form about names etc. Once through these the mulitipage comes up. Have only fully populated the first page so far.

The issue: If you look at the top attribute. "VRV Units", the manager has to pick 1 of 3 skill levels (the radio buttons). If he is not sure of what to pick he can click on one of 2 command buttons on the right hand side. If you click on "Skill 1" (only button I have put code behind) then a 'GuidanceOne' userform pops up.

This form has 3 text boxes which are currently empty. The two bigger boxes will be populated from data in the spreadsheet. However the top text box ("Asset") needs to be populated from the previous 'SkillsForm' userform with the words "VRV Units" as this is the attribute they want guidance on.

What I attempted at first was to populate the textbox with the label value but nothing happened.

I hope this makes sense!

Thanks again and Happy New Year.

Chris

[lucas]

In the userform GuidanceOne you need to add an initialize statement so the textbox can be loaded when the form loads. Use the caption of the label to populate the textbox:

[VBA]
Private Sub UserForm_Initialize()
Me.Asset_Name.Value = SkillForm.AC_Label_1.Caption
End Sub

[/VBA]

There will be a lot of repitition of userforms, etc. in your layout.

[mayerc]

Steve,

That is fantastic, thank you so much, works perfectly. Now I hate to be really cheeky but can you help me with the next step too?

Ideally I don't want to have to create seperate userforms for each Guidance for different attributes, would be better just to have one.

Is it possible to code GuidanceOne to populate the text box Asset_One with a label value that corresponds with the command button pressed?

That is at the moment the code works when I click on button CmdAC1_1 but is there code that will populate the text box with "AC Chiller" if I click on CmdAC1_2? I guess it will be some sort of If statement but not sure if that can work?

That way I only need one guiaceOne which can be bespoked depending on which button I press to activate it.

I hope that makes sense?!

[lucas]

In the code for the skillform userform....I did the first three Skill 1 buttons for you. I also changed your textbox on userform GuidanceOne to a label for a cleaner appearance:

[VBA]Private Sub CmdAC1_1_Click()
GuidanceOne.Label4.Caption = SkillForm.AC_Label_1.Caption
GuidanceOne.Show
End Sub
Private Sub CmdAC1_2_Click()
GuidanceOne.Label4.Caption = SkillForm.Label122.Caption
GuidanceOne.Show
End Sub
Private Sub CmdAC1_3_Click()
GuidanceOne.Label4.Caption = SkillForm.Label121.Caption
GuidanceOne.Show
End Sub
[/VBA]

[mayerc]

Superb!!

Thanks ever so much, my form is really starting to take shape now and I'm slowly learning (I'm not the quickest unfortunately!)

I have made a couple of changes to the forms but all seems to be working well. I have added some more code to GuidanceOne so that it calls data from the worksheet ("Matrix") and that seems to work ok. However, if there is an easier way to do it then I would appreciate any guidance. In the end there will be 170ish assets to give guidance on so I'm worried my codes is going to look horrible and long.

The only other question I have on this particular section regards active worksheets. When I am calling the userform GuidanceOne, becasue I have to get data from a worksheet I thought I should make "Matrix" active first. This seems to work but what I don't like is that the "Matrix" sheet appears in the background whereas I would prefer that it didn't and the "Overview" sheet still showed. Is there anyway to do this?

Thanks again, this is a brilliant site.

Chris

[IkEcht]

Hi Chris,

didn't take a look at your form, but when it comes to activating the matrix-worksheet, you could get the data from there without activating it, or you could set application.screenupdating to false (don't forget to put it back to true later on, when you have activated the "overview" worksheet again).

[lucas]

Just qualify the if statements with a "with" statement and "end with" at the end. You have to put a dot in front of each range call this way as it is being qualified within the with statement.

I also added Option Explicit to the top of a couple of your userform modules. You really should put it at the top of every module. Thisworkbook, any standard modules, any userform modules and any class modules.......it will show any errors such as undeclared variables, etc. Just good programming practice. Search the site for more info on why this is important. VBAExpress requires it in all KB entries.
[vba]Private Sub UserForm_activate()
With Sheets("Matrix")
If GuidanceOne.Label6.Caption = SkillForm.AC_Label_1.Caption Then
Training1.Value = .Range("M2").Value
Competency1 = .Range("N2").Value
Training2 = .Range("O2").Value
Competency2 = .Range("P2").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_2.Caption Then
Training1 = .Range("M3").Value
Competency1 = .Range("N3").Value
Training2 = .Range("O3").Value
Competency2 = .Range("P3").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_3.Caption Then
Training1 = .Range("M4").Value
Competency1 = .Range("N4").Value
Training2 = .Range("O4").Value
Competency2 = .Range("P4").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_4.Caption Then
Training1 = .Range("M5").Value
Competency1 = .Range("N5").Value
Training2 = .Range("O5").Value
Competency2 = .Range("P5").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_5.Caption Then
Training1 = .Range("M6").Value
Competency1 = .Range("N6").Value
Training2 = .Range("O6").Value
Competency2 = .Range("P6").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_6.Caption Then
Training1 = .Range("M7").Value
Competency1 = .Range("N7").Value
Training2 = .Range("O7").Value
Competency2 = .Range("P7").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_7.Caption Then
Training1 = .Range("M8").Value
Competency1 = .Range("N8").Value
Training2 = .Range("O8").Value
Competency2 = .Range("P8").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_8.Caption Then
Training1 = .Range("M9").Value
Competency1 = .Range("N9").Value
Training2 = .Range("O9").Value
Competency2 = .Range("P9").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_9.Caption Then
Training1 = .Range("M10").Value
Competency1 = .Range("N10").Value
Training2 = .Range("O10").Value
Competency2 = .Range("P10").Value
ElseIf GuidanceOne.Label6.Caption = SkillForm.AC_Label_10.Caption Then
Training1 = .Range("M11").Value
Competency1 = .Range("N11").Value
Training2 = .Range("O11").Value
Competency2 = .Range("P11").Value
End If
End With
End Sub[/vba]

[lucas]

I've added option explicit to several of your userform modules. As a matter of good programming practice you should use it at the very top of all standard modules, thisworkbook module, userform modules and class modules. It is required in the VBA Express KB entries.

I would also qualify all of your textboxs too. Notice you just use training1 and I have changed it to Training1.value.

[VBA]
If GuidanceOne.Label6.Caption = SkillForm.AC_Label_1.Caption Then
Training1.Value = .Range("M2").Value
Competency1 = .Range("N2").Value
Training2 = .Range("O2").Value
Competency2 = .Range("P2").Value

[/VBA]

Last but not least. Why use textboxes since you are just returning info from a sheet that no one will act upon...ie add too or remove from.

Why not use labels, it looks much cleaner than those ugly textboxes. I have changed your first textbox to a label to show you the difference.
[VBA] If GuidanceOne.Label6.Caption = SkillForm.AC_Label_1.Caption Then
Label7.Caption = .Range("M2").Value
Competency1.Value = .Range("N2").Value
Training2.Value = .Range("O2").Value
Competency2.Value = .Range("P2").Value[/VBA]

[mayerc]

Steve, As ever thanks ever so much. I have made the changes you have suggested and you're right, it looks much better! I will certainly now go and read about Option Explicit and appreciate you pointing me in the right direction.
I will also use this thread now as the only thread for this project.

All the best

[mayerc]

Hi again.

Apologies for jumping about on this one but was hoping you could help me again.
With regards to the comments form it is imporntant that if a comment has already been made that if the asset is selected from the drop down list that any existing comment is included. I have tried the following code (based on the code you did for me to populate the matrix worksheet) but it's not working. Just when you feel you are getting to understand things!!

All I have done is swap the code about, so not sure what is wrong

[vba]''it is important that when an asset is clicked that any exisiting comments are shown
Private Sub UserForm_Activate()
With Sheets("Matrix")
If Me.Asset_List.Value = "VRV UNITS" Then
comments.Value = .Range("K2").Value
End If
If Me.Asset_List.Value = "AC CHILLER" Then
comments.Value = .Range("K3").Value
End If
If Me.Asset_List.Value = "AC FCU" Then
comments.Value = .Range("K4").Value
End If
If Me.Asset_List.Value = "AC CONDENSOR" Then
comments.Value = .Range("K5").Value
End If
If Me.Asset_List.Value = "AIR HAND UNIT" Then
comments.Value = .Range("K6").Value
End If
If Me.Asset_List.Value = "DUCTWORK" Then
comments.Value = .Range("K7").Value
End If
If Me.Asset_List.Value = "FILTERS BAG" Then
comments.Value = .Range("K8").Value
End If
If Me.Asset_List.Value = "SUPP/EXT FAN" Then
comments.Value = .Range("K9").Value
End If
If Me.Asset_List.Value = "AHP CIRC PUMP" Then
comments.Value = .Range("K10").Value
End If
If Me.Asset_List.Value = "HUMIDIFIER" Then
comments.Value = .Range("K11").Value
End If
If Me.Asset_List.Value = "FILTERS PANEL" Then
comments.Value = .Range("K12").Value
End If
If Me.Asset_List.Value = "HEATER BATTERY" Then
comments.Value = .Range("K13").Value
End If
If Me.Asset_List.Value = "CHILLER UNIT" Then
comments.Value = .Range("K14").Value
End If
If Me.Asset_List.Value = "PACKAGE CHILL" Then
comments.Value = .Range("K15").Value
End If
If Me.Asset_List.Value = "COOLING TOWER" Then
comments.Value = .Range("K16").Value
End If
If Me.Asset_List.Value = "FAN COIL UNIT" Then
comments.Value = .Range("K17").Value
End If
If Me.Asset_List.Value = "COLD DISP COUNT" Then
comments.Value = .Range("K18").Value
End If
If Me.Asset_List.Value = "FRIDGE STORE" Then
comments.Value = .Range("K19").Value
End If
If Me.Asset_List.Value = "SPLIT ACU COOL" Then
comments.Value = .Range("K20").Value
End If
If Me.Asset_List.Value = "SPLIT ACU HTPMP" Then
comments.Value = .Range("K21").Value
End If
End With
End Sub[/vba]

When I choose from the drop down list, the comments box is not being populated with what ever is in the corresponding cell.

Again, thanks again in advacnce for any advice.

Chris

[lucas]

Your code was fine, you just put it in the wrong place. Think about what you want to happen and when. Is it when the userform loads which is the procedure you put it in....

[VBA]Private Sub UserForm_Activate[/VBA]

or do you want this to happen when the combobox changes......that's where I put you code and it works just fine.

[VBA]Private Sub Asset_List_Change()
With Sheets("Matrix")
If Me.Asset_List.Value = "VRV UNITS" Then
comments.Value = .Range("K2").Value
End If
If Me.Asset_List.Value = "AC CHILLER" Then
comments.Value = .Range("K3").Value
End If
If Me.Asset_List.Value = "AC FCU" Then
comments.Value = .Range("K4").Value
End If
If Me.Asset_List.Value = "AC CONDENSOR" Then
comments.Value = .Range("K5").Value
End If
If Me.Asset_List.Value = "AIR HAND UNIT" Then
comments.Value = .Range("K6").Value
End If
If Me.Asset_List.Value = "DUCTWORK" Then
comments.Value = .Range("K7").Value
End If
If Me.Asset_List.Value = "FILTERS BAG" Then
comments.Value = .Range("K8").Value
End If
If Me.Asset_List.Value = "SUPP/EXT FAN" Then
comments.Value = .Range("K9").Value
End If
If Me.Asset_List.Value = "AHP CIRC PUMP" Then
comments.Value = .Range("K10").Value
End If
If Me.Asset_List.Value = "HUMIDIFIER" Then
comments.Value = .Range("K11").Value
End If
If Me.Asset_List.Value = "FILTERS PANEL" Then
comments.Value = .Range("K12").Value
End If
If Me.Asset_List.Value = "HEATER BATTERY" Then
comments.Value = .Range("K13").Value
End If
If Me.Asset_List.Value = "CHILLER UNIT" Then
comments.Value = .Range("K14").Value
End If
If Me.Asset_List.Value = "PACKAGE CHILL" Then
comments.Value = .Range("K15").Value
End If
If Me.Asset_List.Value = "COOLING TOWER" Then
comments.Value = .Range("K16").Value
End If
If Me.Asset_List.Value = "FAN COIL UNIT" Then
comments.Value = .Range("K17").Value
End If
If Me.Asset_List.Value = "COLD DISP COUNT" Then
comments.Value = .Range("K18").Value
End If
If Me.Asset_List.Value = "FRIDGE STORE" Then
comments.Value = .Range("K19").Value
End If
If Me.Asset_List.Value = "SPLIT ACU COOL" Then
comments.Value = .Range("K20").Value
End If
If Me.Asset_List.Value = "SPLIT ACU HTPMP" Then
comments.Value = .Range("K21").Value
End If
End With
End Sub[/VBA]

Remember that this is the example that I originally gave you only a couple of solutions for on the add comments button so you will have to finish the code for that button...ie
[VBA]Private Sub CmdClose_Click()[/VBA]
is not complete in the attached example. Only the first two items will work until you complete that code.

Additional note: You don't say whether you wish to just change the one line of information in the comments box or be able to add too it. In the example you have provided the comments box is huge but the way it is set up now does not allow for multiple lines of text.

If you wish to be able to add to the comments that exist in the comments box when you select an item then you need to look at the properties for the comments textbox.

While in the vbe(where the code is)...on the main toolbar at top go to view and select properties window.

Now go to the userform commentsAC and click on the comments textbox, the properties for that textbox will show in the properties window.

You need to experiment with at least two of these items.

Currently, The multiline property is set to false so you can't have more than one line of text in the textbox.....set it to true and you can just keep typing and the lines will wrap.

If however you wish to use enter to make a new line in the textbox you will also have to change the EnterKeyBehavior property to true.

Let me know if I have confused you or overwhelmed you with just too much info at one time.

[mayerc]

Steve, please please please keep throwing information at me!
Made perfect sense and after thinking about it I have decided to enable both multi line and key behaviour to true.

With regards to the other code I have completed it so all works well.

I am now completing all the other comments forms so far (still a long way to go) and I will upload it to show how I am getting on asap. I am sure by then I will have another issue!

[lucas]

No problem Chris, it is actually a pleasure to help someone who is actually learning....

I posted a response to your radio buttons question also.
http://www.vbaexpress.com/forum/showthread.php?t=24485

[mayerc]

Hi Steve

Yes, I've seen it thanks and had a quick look and I see what I have to do now (I think!). The only issue I have is the next/back buttons are outside the multipages. I will do a little experimentation to see if I can get it to work, otherwise I may have to put serperate buttons on each page. Will let you know how I get on tomorrow.

All the best

Chris

[lucas]

Disable the next button when that page of the multipage is activated and then enable the next button after conditions are met.

You wouldn't have to move them that way.

[lucas]

Chris I posted a response to your radio buttons question also. I attached an example of dealing with mult radio buttons on a multipage userform......

http://www.vbaexpress.com/forum/showthread.php?t=24485

[mayerc]

Steve

Apologes for the lack of reply to your help in recent days. I was tied up in meetings and hd no chance to look at this. Thank you again for your help with the radio buttons. I looked this morning and there was no attachement unfortunately.
However, I did have a go myself last week and the code is below. I haven't deactivated the button although I am sure that there may be an easier way to do it than the way I have. The thought of duplicating this code for another 16 pages makes me slightly depressed!

[VBA]''if the user clicks next page then the code below will check if they have filled it out properly. If they
''have missed to selct and answer from a group of radio buttons it will not let them continue.
Private Sub CmdNext_Click()
Select Case MultiPage1.Value
Case Is = 0
If OptionButton1.Value = False And OptionButton18.Value = False And OptionButton33.Value = False Then
MsgBox "You must choose a skill level for VRV Units"

ElseIf OptionButton16.Value = False And OptionButton31.Value = False And OptionButton46.Value = False Then
MsgBox "You must choose a skill level for AC Chiller"
ElseIf OptionButton15.Value = False And OptionButton30.Value = False And OptionButton45.Value = False Then
MsgBox "You must choose a skill level for AC FCU"
ElseIf OptionButton14.Value = False And OptionButton29.Value = False And OptionButton44.Value = False Then
MsgBox "You must choose a skill level for AC Condensor"

ElseIf OptionButton13.Value = False And OptionButton28.Value = False And OptionButton43.Value = False Then
MsgBox "You must choose a skill level for Air Hand Unit"

ElseIf OptionButton12.Value = False And OptionButton27.Value = False And OptionButton42.Value = False Then
MsgBox "You must choose a skill level for Ductwork"

ElseIf OptionButton11.Value = False And OptionButton26.Value = False And OptionButton41.Value = False Then
MsgBox "You must choose a skill level for Filters Bag"

ElseIf OptionButton10.Value = False And OptionButton25.Value = False And OptionButton40.Value = False Then
MsgBox "You must choose a skill level for Supply/Extract Fan"

ElseIf OptionButton9.Value = False And OptionButton24.Value = False And OptionButton39.Value = False Then
MsgBox "You must choose a skill level for AHP Circ Pump"

ElseIf OptionButton8.Value = False And OptionButton23.Value = False And OptionButton38.Value = False Then
MsgBox "You must choose a skill level for Humidifer"

ElseIf OptionButton1.Value = True Or OptionButton18.Value = True Or OptionButton33.Value = True And OptionButton16.Value = True Or OptionButton31.Value = True Or OptionButton46.Value = True And OptionButton15.Value = True Or OptionButton30.Value = True Or OptionButton45.Value = True And OptionButton14.Value = True Or OptionButton29.Value = True Or OptionButton44.Value = True And OptionButton13.Value = True Or OptionButton28.Value = True Or OptionButton43.Value = True And OptionButton12.Value = True Or OptionButton27.Value = True Or OptionButton42.Value = True And OptionButton11.Value = True Or OptionButton26.Value = True Or OptionButton41.Value = True And OptionButton10.Value = True Or OptionButton25.Value = True Or OptionButton40.Value = True And OptionButton9.Value = True Or OptionButton24.Value = True Or OptionButton39.Value = True And OptionButton8.Value = True Or OptionButton23.Value = True Or OptionButton38.Value = True Then
MultiPage1.Value = MultiPage1.Value + 1
End If

Case Is = 1
If OptionButton47.Value = False And OptionButton57.Value = False And OptionButton68.Value = False Then
MsgBox "You must choose a skill level for Filters Panel"

ElseIf OptionButton56.Value = False And OptionButton66.Value = False And OptionButton76.Value = False Then
MsgBox "You must choose a skill level for Heater Battery"
ElseIf OptionButton55.Value = False And OptionButton65.Value = False And OptionButton75.Value = False Then
MsgBox "You must choose a skill level for Chiller Unit"
ElseIf OptionButton54.Value = False And OptionButton64.Value = False And OptionButton74.Value = False Then
MsgBox "You must choose a skill level for Package Chill"

ElseIf OptionButton53.Value = False And OptionButton63.Value = False And OptionButton73.Value = False Then
MsgBox "You must choose a skill level for Cooling Tower"

ElseIf OptionButton52.Value = False And OptionButton62.Value = False And OptionButton72.Value = False Then
MsgBox "You must choose a skill level for Fan Coil Unit"

ElseIf OptionButton51.Value = False And OptionButton61.Value = False And OptionButton71.Value = False Then
MsgBox "You must choose a skill level for Cold Disp Counter"

ElseIf OptionButton50.Value = False And OptionButton60.Value = False And OptionButton70.Value = False Then
MsgBox "You must choose a skill level for Fridge Store"

ElseIf OptionButton49.Value = False And OptionButton59.Value = False And OptionButton69.Value = False Then
MsgBox "You must choose a skill level for Split ACU Cool"

ElseIf OptionButton48.Value = False And OptionButton58.Value = False And OptionButton68.Value = False Then
MsgBox "You must choose a skill level for Split ACU HTPMP"

ElseIf OptionButton47.Value = True Or OptionButton57.Value = True Or OptionButton68.Value = True And OptionButton56.Value = True Or OptionButton66.Value = True Or OptionButton76.Value = True And OptionButton55.Value = True Or OptionButton65.Value = True Or OptionButton75.Value = True And OptionButton54.Value = True Or OptionButton64.Value = True Or OptionButton74.Value = True And OptionButton53.Value = True Or OptionButton63.Value = True Or OptionButton73.Value = True And OptionButton52.Value = True Or OptionButton62.Value = True Or OptionButton72.Value = True And OptionButton51.Value = True Or OptionButton61.Value = True Or OptionButton71.Value = True And OptionButton50.Value = True Or OptionButton60.Value = True Or OptionButton70.Value = True And OptionButton49.Value = True Or OptionButton59.Value = True Or OptionButton69.Value = True And OptionButton48.Value = True Or OptionButton58.Value = True Or OptionButton68.Value = True Then
MultiPage1.Value = MultiPage1.Value + 1
End If

End Select
End Sub[/VBA]

Of course this isn' clever and simply used the code you gave me before to take it an extra step but it did give me a little satisfaction!

All the best

Chris

[lucas]

The attachment for radio buttons:

http://www.vbaexpress.com/forum/showthread.php?t=24485

post #5