PDA

View Full Version : Populating Text Box in Userform with Label Value



mayerc
12-29-2008, 06:52 AM
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. :banghead:

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
12-29-2008, 08:39 AM
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
12-30-2008, 02:20 AM
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
12-30-2008, 10:28 AM
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:


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



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

mayerc
12-31-2008, 04:58 AM
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
12-31-2008, 11:39 AM
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:

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

mayerc
01-02-2009, 04:50 AM
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
01-02-2009, 07:01 AM
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
01-02-2009, 09:45 AM
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.
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

lucas
01-02-2009, 10:07 AM
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.


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



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.
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

mayerc
01-05-2009, 02:27 AM
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
01-05-2009, 07:46 AM
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

''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

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
01-05-2009, 10:35 AM
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....

Private Sub UserForm_Activate

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

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

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
Private Sub CmdClose_Click()
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
01-06-2009, 08:28 AM
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
01-06-2009, 08:40 AM
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
01-06-2009, 08:43 AM
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
01-06-2009, 07:55 PM
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
01-07-2009, 10:13 AM
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
01-12-2009, 02:00 AM
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!

''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

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
01-12-2009, 08:26 AM
The attachment for radio buttons:

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

post #5

mayerc
01-12-2009, 10:10 AM
Steve,

Thank you, managed to see it this time, I don't think I was logged on before, stupid me.

I really like the code you have given. The only thing I am little uneasy bout is that as you get more option buttons (I generally have 10 per page) the code will increase exponentially and I fear I will end up with tons of the stuff.

The more I think about it, the user shouldn't need to be told that he has not answered a particular question, just not be allowed to continue until he has answered all of them. I can put this in the instructions to avoid confusion.

Is it not possible that the code, because it can tell which multipage we are on only enables the next button when a certain condition is reached, without needing to click on a button?

That is, on any page, as soon as the condition where all questions have been answered is true, the Next button is enabled.

This would mean far less code as the message boxes are not needed. Although the user, if he has not read the instructions may get confused I think it is a far less intensive solution. The only problem is I don't know the code that does something without you doing something explicitly.

lucas
01-12-2009, 10:58 AM
You could have a continue button on the page and it would be enabled at all times. when they click it field validation code could run and if all checks are in place it could enable the next button which is not on the page........

just thinking out loud.

lucas
01-12-2009, 11:07 AM
I really like the code you have given. The only thing I am little uneasy bout is that as you get more option buttons (I generally have 10 per page) the code will increase exponentially and I fear I will end up with tons of the stuff.


I wouldn't worry about that too much, it doesn't bloat the size of the workbook and if it's working properly when design and testing is finished I am happy with plenty of code if necessary.

mayerc
01-16-2009, 02:08 AM
Steve.

Thanks for putting my mind at ease. I will continue to code the rest of the project and put it back on for you to have a look at. I am sure there are other things that I will be stumped on. Until then, all the best

mayerc
01-22-2009, 08:32 AM
Hi Again,

I've been working away on my project and although going slowly it is getting there I think. I have left the code for enabling a user to move onto the next page until the end because it brings me to tears even thinking about it! However, I moved onto another aspect and for some reason I am having issues I can't fathom....

I added a new form called Step_2 which is a simple form asking the user to fill out some option buttons. I wanted to make sure that if they started again or opened a form already filled in that the option buttons reflected what had already been inputted. I used the userform_activate event to do this and it works ok.

I put the same method to use on the main multi-page SkillsForm, and when I tested it in the VBA environment it worked perfectly. However, when I saved, shut down and then reopened the file, the code didn't work at all except for one option button on the first mulitipage.

I simply can't understand why it doesn't work?

Any help is greatly appreciated as normal. I have attached the file again.

All the best

Chris

lucas
01-22-2009, 08:55 AM
Chris, it's been a day or two so could you walk me through the steps you are taking that causes the error or problem? Also what is happening or not happening and at what point.

mayerc
01-22-2009, 09:30 AM
Hi Steve,

No worries.

Since last time I have tried to add code that ensure that, if th userforms are called more than once that they reflect data that has already been entered. That is, on the Skills userform, if they have scored every question, then if they open the form again, the correct option buttons are set to "True" based on their previous answers.

To do this I did simple code that looked at the relevant cell and depending on what was in that cell, it set the relevant option button to true or false.This is at the top of the code for that userform.

When I wrote this for the Step_Two form it worked fine so I used the same priniple for the Skills Form also. When I was in the VBA script and pressed 'run' it worked the first time.

However, now, when I open the file and get to the Skills Userform 99% of the option buttons do not show any as true, let alone the correct option buttons as true.

The data is stored in the Matrix worksheet in columns G, H and I.

Does this make sense?

lucas
01-22-2009, 10:15 AM
Chris, Looks like you may have to add a command button to close the skills form and instead of unloading it you will need to hide it and disable the users ability to close the userform using the X in the upper right hand corner.

added to the skillform:
Private Sub CommandButton1_Click()
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub

See if this seems to do what you need and let me know.

mayerc
01-23-2009, 02:31 AM
Hi Steve

Thanks for the suggestion, really useful. If I am understanding you right all I would b doing would be hiding the form so that if the user wanted to see it again, then I would unhide it and the option buttons would still be filled in?

The only problem with this is that the file is going to be opened many times over time and scores changed as the engineer up or down skills.

That means that the userform needs to reflect the current scores so that the entire scoring doesn't have to be started from scratch.

I works fine in Step_two userform, it looks at the value in a range of cells and if, for example a cell has "yes" in it, the Yes Option button is put to True.

I don't understand why the same isn't possible for the Skillsform Userform? The ony difference is that there are mutilple pages and more option buttons.

It is so frustrating becasue at one point it worked!

mayerc
02-03-2009, 02:57 AM
Hi Steve

Just wanted to send a quick message to say I'm pretty much done now. I am sure there are lots of developments I could make to improve it but I am fairly happy so far and thought it only fair to share what you have helped me build. Well actually you have pretty much built it for me so thanks a million!

The problems I was having with the results populating the form have been fixed. Rookie error surrounding the With Statement, I forgot the . before Range so it wasn't looking in the right place. 462 .'s later all fixed!

The only thing left to do now is I want to hide the tabs on the multipage so the user cannot flick between them. Any ideas? I thought there would bea Style property or something but I can't find it.

Thanks again for all your help. I will soon be adding a new thread, related to this project but so different I think it needs a new section.

Thanks again and all the best,

Chris

Cosmo
02-03-2009, 02:58 PM
There is a style property for MultiPages, set it to 2-fmTabStyleNone

Just make sure you actually have the MultiPage itself selected, and not one of the individual pages.

lucas
02-03-2009, 03:43 PM
Also if you want to make sure your multipage form always loads on page one you could use the following in your userform initialize procedure:

MultiPage1.Value = 0

mayerc
02-04-2009, 03:04 AM
HI Cosmo, thanks for the advice but I have searched and searched and it is just not there. I have mde sure I have selected the user form and not just one of the tabs.

I am using VB 6.3, this wouldn't make a difference would it?

mayerc
02-04-2009, 03:05 AM
Thanks Steve, I have done that already on your previous advice.

lucas
02-04-2009, 06:53 AM
Chris, per Cosmo's advice:

You need to select the multipage, not the userform. When you select the multipage it should say something like "Multipage1" in the name box of the properties window. If it says page1 or page2 then you have a page selected and not the multipage control.....

mayerc
02-06-2009, 08:41 AM
Steve, Cosmo

Found it! Was hidden behind a drop down, but all fixed now. Thanks, I really am a numpty....

Now, after some really positive feedback from colleagues, they have asked me to make it better, bless them!

The multipage has 19 pages and to fill them all out takes time.

Often, it won't be necessary to fill out all the pages becasue they won't be relevant. What I would like is for the user to be able to choose which pages they want to fill out. Those that are not selected are automatically filled with a score of 0.

I guess I will have a page prior to the multipge where I list the groups (15 of them but some go over two pages) and the user can tick check boxes for the ones they want to fill in?

However, the more I think about it the more complicated I think the code will be because wouldn't I have to explicitly code all possible permutations of choices that the user could make?

I hope what I mean makes sense and if there is an easy way to do this then your guidance would be grately appreciated.

Many Thanks

Chris

mayerc
02-06-2009, 09:01 AM
Hold on, think I may have it and it wouldn't be complicated. Can you let me know If I am going down the right track here?

If I have a page where the user can tick Yes/No against each group then, if they click no, I simply disable the page or pages that are relevant and set the 'scores' to 0.

If they click yes, I enable the page.

I then need to ensure that the first enabled page is in focus when the multipage is initalised.

When the user clicks the next button, I am hoping that it will then skip to the next enabled page and misses out any disabled pages?

Am I dreaming thinking it is as simple as that!? :-)

lucas
02-06-2009, 09:07 AM
Another consideration would be visibility.......