![]() |
|
![]() |
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
![]()
Hello forum and thank you for taking time to read this. I am completely stuck on what I think is a simple problem but I have invested several hours so far an cannot crack this one. Maybe one of you will be able to identify a fix. I have included an attachement that is a watered down verison of the big program and makes it fast and easy to see what I am stuck on.
In a nut shell, the statement S11 = Uf_CertPlan_Owner.Lb_TeamPerformance S11 is suppose to set to the value of a listbox but it remains blank. S11 is declared as a string and the value in the list box is "High" but after running this line of code the variable S11 remains blank. But S12 and S13, set in a similar manner do set correctly so why does S11 refuse to set to high? Please open the excel workbook and double click any cell to launch the "menu of services" userform, then follow the process and you will be prompted to control-break and read some remarks in the code that will show you exactly where the variable is being set but if you hover your mouse over the variable you will see it is blank. |
#2
|
|||
|
|||
![]()
Hi CatMan
Hm, quick and dirty you can obtain the value from the Listindex: Code:
. . Dim strS11 As String, strS12 As String, strS13 As String 'strS11 = Uf_CertPlan_Owner.Lb_TeamPerformance.Value 'strS12 = Uf_CertPlan_Owner.Lb_ProgramPlan.Value 'strS13 = Uf_CertPlan_Owner.Lb_SowMaturity.Value With Uf_CertPlan_Owner With .Lb_TeamPerformance strS11 = .List(.ListIndex, 0) End With With .Lb_ProgramPlan strS12 = .List(.ListIndex, 0) End With With .Lb_SowMaturity strS13 = .List(.ListIndex, 0) End With End With . . It's not a proper solution, since it doesn't solve the problem at the root. It's just another way to obtain the desired data. Where the problem comes from is difficult to say, you are using to many shortcuts in your script, e.g. there is no refering to a explicit property of a control, instead you merely refer to the default property which makes it difficult to read your code. Your project seems too advanced to work it over completely so I won't say too much about it. But one thing I recommend strongly to change: Never use names for variables (neither for functions or subs) which are already in use by excel itself. For instance: S11 refers to the cell "S11", is therefore in use of excel, and such things cause problems all the time. If the particular problem is a result of this or something else I can't say, but it's the first thing you will have to change, otherwise you never will be sure if your scripts will work correctly. |
#3
|
|||
|
|||
![]()
PROBLEM NOT SOLVED, SEE NEXT THREAD...
Hi Whatsup, Ok, I inserted your code suggestion using the "with" method and it worked for two and failed for one (Run-time error 381, Could not get the list property. Invalid property array index.) which makes perfect sense because as I look at the userform I can clearly see that one of the three listboxes has not set at all. So that means the problem is that I am not setting the listboxes robustly in the first place, which is why I cannot scrape out the listbox value and set the variable to it. The problem is not at the back end, it's at the front end. Her is how I am setting the variables now: Code:
Uf_CertPlan_Owner.Tb_TotalQTY = Sheet18.Cells(C1, 2) Uf_CertPlan_Owner.Lb_TeamPerformance = Sheet18.Cells(C1, 3) Uf_CertPlan_Owner.Lb_ProgramPlan = Sheet18.Cells(C1, 4) Uf_CertPlan_Owner.Lb_SowMaturity = Sheet18.Cells(C1, 5) So using your suggestion to seek out another method of referencing the listbox I have appended ".value" to each of the code lines above and that worked pefectly. I will also rewrite this code to use the with method. Many many thanks for helping me out of this hole. And I will heed you advice on not using reserved words for variable names. Thanks again Whatsup. Here is the code that solved the problem: Code:
With Uf_CertPlan_Owner With .Lb_TeamPerformance strS11 = .Value End With With .Lb_ProgramPlan strS12 = .Value End With With .Lb_SowMaturity strS13 = .Value End With End With |
#4
|
|||
|
|||
![]()
Looks like the problem has not been solved. It worked on my previous post one time but now that I have run it a couple of more times, the problem continues. I have posted a new copy of the workbook that incorporates the suggestions made earlier. And I have moved the prompt to control-break to the the code that sets the listbox from data saved on a worksheet. The problem is clearly that I am unable to set all 3 listboxes per data on a worksheet robustly. 2 of the 3 listboxes seem to set, and not always the same 2 but one is always not setting. I think if this is fixed then I can set variables later in the code.
|
#5
|
|||
|
|||
![]()
In your example my code as posted worked fine, but in the UserForm were shown the set values. You see from this, your code walks on thin ice.
Quote:
![]() It is generally adviseable to use the especific property: Code:
Uf_CertPlan_Owner.Tb_TotalQTY.Text = Sheet18.Cells(C1, 2).Value Uf_CertPlan_Owner.Lb_TeamPerformance.Value = Sheet18.Cells(C1, 3).Value Uf_CertPlan_Owner.Lb_ProgramPlan.Value = Sheet18.Cells(C1, 4).Value Uf_CertPlan_Owner.Lb_SowMaturity.Value = Sheet18.Cells(C1, 5).Value Edit: Haven't yet seen your new post, you will have to change "Sub ReloadCertPlanOwner()" and probably sa well the calls to Show the next UserForm... |
#6
|
|||
|
|||
![]()
Hi Whatsup, ok, I tried your latest suggestion but no improvement. I checked that the worksheet cell includes the text string "High" and it does so I don't think that is the problem.
|
#7
|
|||
|
|||
![]()
As I said in "Edit" you probably will have to change the calls to display the Uf_CertPlan_Owner,
A question: Do you use the Sub ReloadCertPlanOwner() on other occasions as well, or is it only used from Uf_AirplaneCertSubCat? |
#8
|
|||
|
|||
![]()
Hi Whatsup, it seems the SowMaturity is not working, I deleted the contorl and built it again by copying the middle listbox control but that did not help.
|
#9
|
|||
|
|||
![]()
Sub ReloadCertPlanOwner() is only used to reload Uf_CertPlan_Owner (not Uf_AirplaneCertSubCat). However I have other modules that do the same thing (with minor tweaks) for other userforms. Currently the code is setting the 3 listboxes while the userform has not yet been shown (it is not in hide, it has not yet been lauched). Do you think that is the problem? But how do I set the 3 listboxes once the userform is shown? Am I not correct to say that code exicution stops once you issue Uf_AirplaneCertSubCat.show?
Ok I just changed the code to launch Uf_CertPlan_Owner.SHOW prior to setting the 3 listboxes, code exicution does stops and the userform appears incomplete (because code exicution stopped prematurely). I just changed the code to launch Uf_CertPlan_Owner.HIDE prior to setting the 3 listboxes, code exicution does not stops and the SowMaturity listbox does not set (the other two listboxes set ok). Last edited by CatMan; 08-08-2014 at 01:08 PM. Reason: changed load to set,added another paragraph |
#10
|
|||
|
|||
![]()
Now, a few hours later I'm afraid you will have to rebuild your file. Somehow the ListBoxes don't allow to use their .value property, what isn't a good sign at all
![]() The main problems are: - Missing of declaration of variables and thereof leaving too much to vba interpreting what you want - Names used by excel are used as well for variables On top try to: - avoid too many jumpmarks within the code - avoid Using ":" - instead use a new line in the code - be as especifically as you can, to determine the type of value the variable shall have thus using the correct type in decalaration. - do declare all variables, using "Option Explicit" on top of each module will help to discover undeclared variables. - use With-blocs to help easy reading of code (on top it will speed up codes) In the attachment you will find an example how it works filling and obtaining the values of the ListBoxes. I changed: - Uf_CertPlanSubCat (for showing the next UserForm) - Uf_CertPlan_Owner (the Initialize-event calls now for "ReloadCertPlanOwner") - Sub ReloadCertPlanOwner() (in addition the Function Get_Index()) - Uf_CertPlan_Owner the Private Sub Cb_Back_Click() |
#11
|
|||
|
|||
![]()
Hi Whatsup, I agree, I think the file is corrupt. And thank you for submitting your version of ReloladCertPlan_Owner(), it works great. Thank you for your work on this problem, I can move forward now.
|
#12
|
||||
|
||||
![]()
Just for interest, I can confirm the exact same behaviour on a very simple form. I don't think your Form is corrupt, it looks to me like the .Value property for ListBox is broken. ComboBox works fine using the .Value to load and either .Value of .ListIndex to output. ListBox works reliably loading and dumping using .ListIndex but .Value... not so much. No ammount of "DoEvent"-ing seems to make any difference...
|
#13
|
|||
|
|||
![]()
Hi CoolBlue
I haven't checked if the file is corrupt neither where the bug sits. Myself I'm hardly using the rowsource-property and never the value-property (on ListBoxes !!!). Therefore my advice generally is - unless you intend using the listbox for reading purposes only -, don't go with the rowsource but use the list-property. Using ListIndex in reading from the Listbox and marking a "value" makes both much easier as bothering with value. |
#14
|
||||
|
||||
![]() Quote:
Hi Whatsup ![]() Based on the research I did, I think your original post was the best solution. My feeling is that the root cause is just a bug in VBA. I haven't seen problems with .RowSource although, you do need to be careful to fully qualify the Address of the range; I use .Address(External:=True) to take care of that. Have you noticed specific problems with it when using ListBox, or do you just generally avoid it because of the buggy .Value behaviour? |
#15
|
|||
|
|||
![]()
Never got that far looking for bugs concerning RowSource. There's only one advantage of that property: It allows Columnheads.
If you don't need them, you're much better off using the list-property. It allows you either fill it by arrays or refering to ranges, you can remove, add, modify, ... whatever you want. If you need headers you might be interested in the ListView-Control, that one even provides additional options - though more difficult to handle than the common ListBox. |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
marksm33 | Word VBA | 3 | 01-15-2015 07:55 PM |
![]() |
CatMan | Excel | 1 | 08-08-2014 09:41 AM |
Checkboxes are unchecked in Listbox that were checked off before.How can I stop this? | Rochelle711 | Excel Programming | 0 | 06-22-2014 06:16 PM |
![]() |
gvibe@hotmail.com | Word VBA | 1 | 07-19-2013 10:54 AM |
as the return value of another column, using Listbox? | marreco | Excel Programming | 1 | 03-27-2012 12:48 PM |