Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2014, 08:52 AM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default This is a Userform LIstbox queston: A variable does not set to the value of a listbox

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.
Attached Files
File Type: xlsm PBM_Help v3.xlsm (87.7 KB, 13 views)
Reply With Quote
  #2  
Old 08-08-2014, 09:42 AM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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
 .
 .
Be aware:
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.
Reply With Quote
  #3  
Old 08-08-2014, 11:04 AM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 08-08-2014, 11:45 AM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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.
Attached Files
File Type: xlsm PBM_Help v4.xlsm (87.9 KB, 9 views)
Reply With Quote
  #5  
Old 08-08-2014, 11:50 AM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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:
...I have appended ".value" to each of the code lines above and that worked pefectly
The default property of a ListBox is .value, so you haven't actually changed anything. As you can see in my last posting, I tried this as well, but the problem remained therefore I tried the .List-property. What I want to say: Your new code will probably have some trouble soon again

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
... that also makes clear - when reading the script - that a TextBox contains text though the contains might be numeric. An important thing to keep in mind when using contains of a TextBox.

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...
Reply With Quote
  #6  
Old 08-08-2014, 12:00 PM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 08-08-2014, 12:11 PM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 08-08-2014, 12:15 PM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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?
Reply With Quote
  #9  
Old 08-08-2014, 12:38 PM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 08-08-2014, 04:06 PM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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()
Attached Files
File Type: xlsm PBM Help_v4_mod.xlsm (80.7 KB, 14 views)
Reply With Quote
  #11  
Old 08-09-2014, 03:09 AM
CatMan CatMan is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 32bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Intermediate
This is a Userform LIstbox queston: A variable does not set to the value of a listbox
 
Join Date: Apr 2012
Posts: 39
CatMan is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 08-18-2014, 06:53 AM
CoolBlue's Avatar
CoolBlue CoolBlue is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default

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...
Attached Files
File Type: xlsm Book1.xlsm (25.9 KB, 10 views)
Reply With Quote
  #13  
Old 08-18-2014, 02:48 PM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
  #14  
Old 08-18-2014, 04:23 PM
CoolBlue's Avatar
CoolBlue CoolBlue is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default

Quote:
Originally Posted by whatsup View Post
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.

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?
Reply With Quote
  #15  
Old 08-18-2014, 08:14 PM
whatsup whatsup is offline This is a Userform LIstbox queston: A variable does not set to the value of a listbox Windows 7 64bit This is a Userform LIstbox queston: A variable does not set to the value of a listbox Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
This is a Userform LIstbox queston: A variable does not set to the value of a listbox Moving Selected Items from a Multiselect Listbox on a userform to a bookmark in Word marksm33 Word VBA 3 01-15-2015 07:55 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox This is a Userform LIstbox queston: A variable does not set to the value of a listbox 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
This is a Userform LIstbox queston: A variable does not set to the value of a listbox Multi-select listbox help 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft