+ Reply to Thread
Results 1 to 17 of 17

VBA UserForm.Value Check If User Form Buttons checked Not Working. Check Button on Open.

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    VBA UserForm.Value Check If User Form Buttons checked Not Working. Check Button on Open.

    VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    If UserForm1.Value = True
    Not working !

    _1) VBA to Check If User Form Buttons are checked
    and
    _2) Check a Button with a code on Worksheets Open ( or with any Code )
    _......

    (_.....EDIT : Thursday 11th May 2016: Summary of problem and solution at this Post:
    http://www.excelforum.com/excel-prog...ml#post4384440

    _.....)



    Hi
    I am getting OK now with normal VBA thanks to Forum participation. . But I have not much experience with User Forms. Another Member kindly added a User Form for me in my File. Sadly he is no Longer with us.... ; )
    It is the only User Form in my File.

    I added myself two Option Buttons and a Check Button. ....

    _1 ) Question 1)
    I wish to check at a code line If those Buttons are checked. ( That check needs to be done in a code in a Normal Module, or a Worksheets Code Module )

    According to my Googling it should be dead easy.

    So I did a quick code to check if I could check if the to be checked things are checked.

    Please Login or Register  to view this content.
    I noticed that should I use lower case in those code lines for either Button Name, such
    statusbarnormal
    Then the VB corrects it to
    StatusBarNormal

    So it does appear to recognise those “things” ( Objects? )

    Also code lines of that form do work within the Codes
    Private Sub StatusBarNormal_Click()
    Private Sub OptionButton2_Click()
    Private Sub Refresh_Click()

    ( These codes are located in the User Form somehow.. )

    However, running my Demo Code from a Normal Code Module or Worksheet Module it errors. It highlights any of those three , such as StatusBarNormal.. and says that the variable has not been Defined

    Can anyone see if I am doing anything obviously wrong due to my ignorance in this area ?
    _.....................................

    BTW. I do have a workaround. ( I share it here.. )
    My workaround ( for the Refresh Check box ), ( which works ), is as follows.

    In a Normal Module ( Module Globies ) I Have this

    Please Login or Register  to view this content.
    Then in my code, ( in Sheet1 Code Module )
    Private Sub Worksheet_Change(ByVal Target As Range)
    I have this where I want to check if the Option Button “Refresh” is checked
    Please Login or Register  to view this content.
    The corresponding User Form Code looks like this:
    Please Login or Register  to view this content.
    _.........................

    The workaround for my two Option Buttons ( which also works ) is that they turn the Status Bar ( that thing down there at the left ) on and off thus
    Please Login or Register  to view this content.
    Then in the code
    Private Sub Worksheet_Change(ByVal Target As Range)
    I have this line to check that state..
    Please Login or Register  to view this content.
    So I have a solution., But I am still googling that I can directly check if my Option Buttons and Check boxes in my UserForm In a code like my first given above. ( Or does those codes only work in the User Form. I change those 3 Privates to Public and that had no effect )

    What am I doing wrong?

    _......................
    _2 )Question 2)
    The second question is if I can check one of those Buttons with a code line? ( Ideally in my This Private Sub Workbook_Open() code so that i can set the User Forms Check boxes as i wasn’t on opening )
    This second question is not too important as I guess it may be a bit difficult, as the Code lines which somehow “make” the User Form are in my
    Private Sub Worksheet_Change(ByVal Target As Range)
    Please Login or Register  to view this content.
    _......
    I could not find by googling at all, how to check a Button from a code.

    _............................................................

    Thanks for any help
    Alan


    _.................................
    P.s.
    If it helps,
    Here is my File. ( “ProAktuellex8600x2.xlsm" ) ( It is a Daily Nutrition Consumption Protocol ! )
    I Apologise that I have not reduced it to the minimum, but this is very bit difficult as many things like Ranges are hard coded. So I will get in a real mess if I try chopping rows out etc..
    On opening you will be asked if you want to Initialise. It is OK to hit OK to that! ( Puts the Daily Coffee Intake the Daily Nutrition Consumption Protocol ! )
    https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0
    I thank you kindly, once again,..... and thanks for reading ! ....
    Last edited by Doc.AElstein; 05-11-2016 at 06:01 PM. Reason: Slightly better Title after Problem Solved, to aid in a Goofle search to reflect Thred contents and Knowledge within

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Hi,

    There's a lot to comprehend there. You'd be better advised to upload the workbook so that we can see this in context.
    Clearly explain where you want to check for the condition of your option buttons.

    However try something like
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Hi Richard
    Quote Originally Posted by Richard Buttrey View Post
    .... You'd be better advised to upload the workbook so that we can see this in context.
    Clearly explain where you want to check for the condition of your option buttons.

    However try something like....
    Thanks for the quick reply.
    The File I gave a link to at the end of Post #1
    ( Sorry it is a bit too big to upload, and reducing the data is difficult as I explained. )

    Also I showed where I Have the two required If 's
    Sorry there was a lot there.
    I was also sharing my Workaround, and trying to be as concise as possible.

    Thanks very much for your code I will try it out
    Alan

    EDIT:
    P.s.
    Richard: I ammended the title after this was solved, as your answer was excactly what many people thought and indeed what I still Google. I know why. This will catch a lot of people out so I will do a last follow up Post to Explain
    Alan
    Last edited by Doc.AElstein; 05-11-2016 at 01:36 AM. Reason: Edit fro Richard Comment at end.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  4. #4
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Hi Richard,

    Ok I tried the demo code kike this following your suggestion,

    Please Login or Register  to view this content.
    That does not error. . - I guess I should have twigged to that. I never stop telling OP’s to fully reference things like Workbooks and Worksheets thus:
    WB.
    And
    Ws.
    Etc....

    Logical that the same applies to User Form things
    UserForm.
    And that explains why of course it worked within the User Form ( ufResults ) codes!

    _..................

    But in the demo code, ( which I have in a normal module ) it gives False for
    v
    even if I check the Button StatusBarNormal


    I also put this line,
    Dim v: v = ufResults.StatusBarNormal.Value
    in the
    Private Sub Worksheet_Change(ByVal Target As Range)
    In Sheet 1 ,
    then
    put a stop on it,
    then
    checked Button StatusBarNormal
    then
    initiated running of
    Private Sub Worksheet_Change(ByVal Target As Range)
    ( by putting a number in any column C column where there is a Food in column A )

    Then I Debug Mode F8 to go past
    v
    Then I hover to see what is in v , and it is still False, despite Button
    StatusBarNormal
    Being checked.

    So I am not quite there yet....

    I will keep at it

    Thanks again for the reply

    Alan
    Last edited by Doc.AElstein; 05-07-2016 at 03:37 PM.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    HOw/when are you loading the form? If it's not still loaded when you run that code, you will get False.

  6. #6
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Hi Rory,
    Morning. Thanks fort he reply.
    Quote Originally Posted by rorya View Post
    HOw/when are you loading the form? If it's not still loaded when you run that code, you will get False.
    I have been having a play after what you said...googling etc on Loads etc.
    Sorry I am still totally ignorant with user Forms. I still have not been able to understand your question.
    _.............

    These bits are towards the start of the Worksheet_Change code:
    Please Login or Register  to view this content.
    They seem to make the user Form Pop Up…. From then on it always there to “see.”
    If I check maybe the first option button “StatusBarNormal”. Then initiate a run of the Worksheet_Change code a couple of times ( any entry alongside a Food in column C ),
    I see this and all is well
    Attachment 459606
    _..................................

    I then check the second two boxes as shown below
    Attachment 459605

    After that my two workarounds work, ( detailed in post #1 - For example you see in the second Image my Global variable set in the Refresh Check Button code has changed to True ). So somehow those Button check boxes must have been “read”. But I cannot seem to put any code line anywhere ( in this code or elsewhere ) that returns me anything other than False for
    ufResults.Refresh.Value
    or
    ufResults.OptionButton2.Value
    ( checking the value of my Global variable workaround does as I expect. ( I have to recheck that Refresh Button to get it True, but that is how I wrote the code ) )

    Sorry it is very difficult to follow this Thread. One case may be where a file is useful! My File is still linked in Post#1 if you had time to check it.
    I just added a couple of Debug Lines. They are responsible for the results in the immediate window on those two screen shots
    Please Login or Register  to view this content.
    And
    Please Login or Register  to view this content.
    Those are at the two points where I want to check to see if the Buttons are checked. That is where I do not understand that I cannot get a True result ever.
    ( I just reloaded my File with those new Debug lines in, same link as in Post #1 _..
    https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0
    _.. )

    This is an awkward one to follow, sorry.
    Alan

    P.s. I have discovered a ( new ) Phenomena, just now! ....
    This
    Please Login or Register  to view this content.
    Does not work like this:
    Please Login or Register  to view this content.
    In the first case the second condition is not checked if the first is not met – it ignores the : thing which should tell it there is the next code line!!
    Interesting ( But I modified my code to the second case, and it has no effect with my current problem )







    _
    _Edit.. On Editiing I can no longer see those atttatchments, how poo!
    bloody EF Software!!
    Last edited by Doc.AElstein; 05-08-2016 at 01:10 PM.

  7. #7
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Still struggling here.

    Can anyone help


    _......




    Possible a User Form expert may see that I am missing something fundamental ? .

    I am still not able to get anything other than False when using debug lines ( which I have at the points where I want to use such a check )

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The full story is given, and the File is linked, in the previous posts

    Here again I give the relavent parts of the Worksheet_change code
    ( Apologies again that I can not simplify this code and test data. As mentioned before this is particularly difficult in this case to do )

    Towards the start I have this, which appears to make the User Form come up

    Please Login or Register  to view this content.
    Then a bit further down I have this DeBug Line

    Please Login or Register  to view this content.
    Towards the end of the code I have a line which puts the contents of an Array into a list in the User Form
    Please Login or Register  to view this content.
    Shortly after that I have my second Debuger line

    Please Login or Register  to view this content.
    Thank you
    Alan


    P.s. The File Link again, details about it, how to use it etc.. in first post

    https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    You need to be referring to fm.OptionButton2 not ufResults.OptionButton2

    If you need access to these from other routines, you should move the declaration of fm appropriately.

  9. #9
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Hallo !
    Thanks for the replies

    Quote Originally Posted by rorya View Post
    You need to be referring to fm.OptionButton2 not ufResults.OptionButton.....
    So I tried
    v = fm.OptionButton2.Value
    instead in the Worksheet_change Code

    It works !!! – gives True or False appropriately !
    Thanks!!


    Here goes the Nut rambling again, sorry about that..
    Clearly I have not a clue about user Forms and sadly running out of time to learn.
    All I see is in the VB Project Window is ufResults, so I thought that “is” the UserForm ??
    That tied up with what Richard suggested. He suggested
    UserForm1.
    Now... if I insert a new Userform, it gets the name
    UserForm1.
    The one you did for me has this name
    ufResults.
    So clearly There seems some logic to what I tried to do?
    And the lines with that in do not error, ( just always give False )

    _..........................
    Quote Originally Posted by rorya View Post
    If you need access to these from other routines, you should move the declaration of fm appropriately.
    Sorry i do not ( did not ) get it as usual, I guess it means to do with to get the last Three lines to work in this code in a normal module, may be ?
    Please Login or Register  to view this content.

    I spent some considerable time being very precise about referring to Ranges correctly but clearly I have no idea what / where the parallel is with User Forms...

    So, can I ask
    _ What is fm ?
    And
    _ What is ufResults?
    _ How come I can access ( I mean it does not error in those lines above ) ufResults from elsewhere,
    but not fm from other than where it is...

    Or is this parallel to Code Modules, like this: If I Declared it in a normal module, then would I be able to access it elsewhere. – ( Sorry with this one I hesitate to experiment. – I do not want to create UserForms all over the place hap hazadly !!)*****

    In my VB Project Window ufResults does not seem to be tied down to a Sheet. And , again, I cannot find this “thing” fm anywhere! What is fm!!!

    Or how about.
    ufResults is a sort of class thing, and fm is one instance of it. *****


    Alan

    _............................

    *****Edit: OKI I did anyway experiment
    Just to balance out a bit...
    I did this .... I “kloned”
    _- got two with things showing
    Please Login or Register  to view this content.
    May be I do get it... a bit,
    I did this, and the second code does not work...
    Please Login or Register  to view this content.
    _............................

    But now I do this in a normal Module:
    Please Login or Register  to view this content.
    (_....And this in my Worksheet:Change code..
    Please Login or Register  to view this content.
    _.......)
    and all is well
    All the following work , that is to say give the results I expect.

    Please Login or Register  to view this content.
    There, you see, preparing concisely in a Thread Reply can help you sometimes get there yourself...

    I Thanks us, you and me
    Alan

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Quote Originally Posted by Doc.AElstein View Post
    Or how about.
    ufResults is a sort of class thing, and fm is one instance of it. *****
    Bingo! Hence your subsequent results.

    Unlike normal classes, userforms are auto-instantiating, so you can simply call them by name and a new instance of the class is created. That's why the changes weren't being reflected in your code - you were referring to two separate instances of the form.

    There, you see, preparing concisely in a Thread Reply can help you sometimes get there yourself...
    Exactly.

  11. #11
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Thanks Rory,

    Just one last thing to make sure I got it..
    Quote Originally Posted by rorya View Post
    ... That's why the changes weren't being reflected in your code - you were referring to two separate instances of the form......
    I was refering originaly ( in my Debug check lines ) to
    ufResults..

    That was not an instance was it, as it is the original ( Blueprint ).... Or is it... In this case....

    Alan

  12. #12
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Yes it is. As soon as you use ufResults for the first time, a new instance of the form is created. It's confusing because you effectively get a variable of the same name as the class. So that instance was not the same as the fm instance. (If you'd added a Debug.Print Userforms.Count line in there, you'd have seen 2 forms)

    For a simple demo, in a new workbook, add a blank userform, then add this code and run it:
    Please Login or Register  to view this content.
    Last edited by rorya; 05-10-2016 at 09:57 AM.

  13. #13
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Thanks Rory.
    That is very helpful. These niggly small discrepancies can be very annoying.
    Nice to get them cleared up
    This also clears up some problems i had once when looking at different Add-Ins and getting confused which one was running. I had been changing the class UserForm, rather than the Instance of it. Or the other way around, or whatever... At the end of the day I thought I was changing a code somewhere I was not. ( I sorted all that out by exporting the UserForm , changing the name of the in the File then importing the original back.....and then being very careful to check where I was when I changed anything )
    _
    So Fuck he’s off agiain...

    In these two lines The first line changes a Property of the instance, which then “dies” when the File closes. No Changes are made to the User Form ( Class ).
    So when I “make this “ instance we have a “ByValue Call” here, as it were, passing a Copy of the User Form to the screen.
    Not 100% sure, yet what a Load is... I have a Load of...____x ... when...?

    The second code line seems to have no effect. ( I guess I have to change things to the class manually )
    fm2.Caption = "Poo"
    ufResults.Caption = "Poo"

    I said The second seems to have no effect
    _........
    So I thought it did nothing.. now I know better..
    So
    _ ....I did what you said, added a User form to a new File, ( and I added a button.. because I could )
    Quote Originally Posted by rorya View Post
    ...... It's confusing because you effectively get a variable of the same name as the class. So that instance was not the same as the fm instance. .....
    I get a Variable of that name which refers to a new instance, but then I cannot seem to add a caption to it... or I can , you did.. and with my modified version of your code below I see “it” . So maybe it is indeed suddenly a new instance with the same name. And as it is an Instance, when I save and close and reopen the File, I find that the name on UserForm1 is “UserForm1”, and not as I aptly named the instance in the code below:
    "Loaded another instance annoyingly with the same name of The User Form Class" as in the VB Project .
    Please Login or Register  to view this content.
    _.........................

    I think I really have this now as far as I am ever likely to need. Thanks very much. But I think in this case you will agree with me that we are indeed talking a “Load of ..... “ . This goes right back to your original answer which
    Quote Originally Posted by rorya View Post
    HOw/when are you loading the form? If it's not still loaded when you run that code, you will get False.
    , There you were talking a Load of..... i was also talking about a Load of...... Unknowingly I had created another instance in testing for ufResults button values, A Load of ... ByVal .. Copy of... and I had been doing checks on that instance rather than that instance which was loaded.
    ( So I expect I cannot change the Class Properties by code, or not at any rate through UserForm1.___ as that refers to the instance. ( and even if I could do such , I do not see the point. Doing it manually is easy enough.. ) )

    The fact that I can create an instance of the Class ( With the name of the class ) without the
    Dim
    Set

    pair is an interesting oddity. But who knows... might have its uses.
    _.............................................................

    I think I answered my other question as well, - How to uncheck a Button with a code. If a button is checked, I can uncheck it with
    Let fm2.Refresh.Value = False ‘ Refresh is the Button name

    That does have the annoying habit of kicking off the code behind it. But if I do not want that then this in the first line sorts that out
    If Refresh.Value = False Then Exit Sub

    Thanks again
    Alan
    Last edited by Doc.AElstein; 05-10-2016 at 12:29 PM.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    You can change the class properties through code, using the Designer object.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Quote Originally Posted by rorya View Post
    You can change the class properties through code, using the Designer object.
    OK, thanks, can't think why I would want to do that, but worth knowing.
    Actually I was just going to Edit BTW. my last post and say I have it well solved now thanks .....
    _ the last bit I wanted I just did and was easy now. -As I had changed my Declaration of fm to be a Globie, as part of the Experiments today.( and like you originally said
    Quote Originally Posted by rorya View Post
    ..... need access to these from other routines, ..move ..declaration of fm appropriate...
    ....... I was able to just add a few lines to create the fm instance and check the button I wanted to on opening the file in the This Workbook code in the Workbook_Open code thus:

    Please Login or Register  to view this content.
    So well all sorted. Should keep me out of harms way with my project now for a while
    Thanks again for all your help.

    Alan


    P.s. While I was stuck and frustrated with this User Form stuff.. , I rambled in some more threads of little or no interest, may be
    Such as these...
    http://www.excelforum.com/showthread...t=#post4381274
    http://www.excelforum.com/showthread...t=#post4381275
    http://www.excelforum.com/showthread...t=#post4381420
    Last edited by Doc.AElstein; 05-11-2016 at 01:39 AM.

  16. #16
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Problem and Solution Summary

    Based on the Thread Title I think a Summary could be useful for anyone coming here on a Google Search. This was a subtle problem that caught me out. Explaining it and the solution is a nice Explanation of the Basic Class, instancing thing.

    First the Problem and answer, and then a bit of explanation. ( Names and the exact progression of what went on are changed a bit to protect the innocent and make this a bit clearer )

    _1a ) Problem:
    So I “had a UserForm.” – ( ( To “get” a UserForm ... in VB Editor .. Insert .. UserForm )
    This “thing” gets a default Name, UserForm1. I see that name in a few places.
    I double click on UserForm1 in the VB Editor, and without too much effort I can “ “drag” a check box into it “. “It” gets the Name CheckBox1. . I see in the VB Editor ( Alt + F11 ) the following:

    Attachment 460117
    The above is effectively an empty Blue Print form to represent a Class of Objects. It will be “used” by VBA as a guideline for when it creates such Objects, helping it to assign memory, has code instructions for what to do in later events associated with such Objects, how to store , use them ( in this case , for example, how to “show” it ) etc. ... etc - all to be explained further......

    So in my origianly problem, I tried to check in a code if the CheckBox1 check box is checked ( has a tick in it ) . I tried that with this code line, and everyone and every Google search told me it was how to do it

    If UserForm1.CheckBox1.value = True Then ______

    But that always gave me False.

    _1b) The Solution:

    Something like this did work

    If fm1.CheckBox1.Value = True Then

    _1c) What to do if you have this problem .. : check if your instancing of the actual userForm you are interested in has been done Explicitly, and not left to the Implicit Default

    _..................

    To er labberate
    _2 ) What’s going in?

    I got caught out because ( at least indirectly ) of the following VBA habit:
    VBA tends to guess what you want when you leave things out. ( the compiler tries to determine what it is you're asking it to do ). This is good sometimes. But then relying on these Implicit defaults can “bite you when you least expect it”. In addition I fear that this results in People forgetting what VBA does . The UserForm was done for me by someone who knows what he is doing and does not rely on the Implicit defaults. I think much of what I Googled had forgot ... something here....
    In all that lies the key to the problem I had


    That “thing” “called” UserForm1 up there is a Class. That is to say it is a Blue Print, or a form, or a questionnaire not yet filled in, a template ... .. etc. It does not really exist in the terms of a Final product. It is just describing how something of that form or type would be.

    In VBA you “Dim” things. In doing so you usually give:
    a)
    Variable : , a name, say fm1 if we are talking about UserForm things , or ws if we are talking about Worksheet things, and
    and
    b )
    A Type : , what sort of thing is it.

    For example as in these this “pseudo” code Lines

    10 Dim fm1As AnExistingBluePrint
    There is no Blue Print “AnExistingBluePrint” , that is just made up for now.
    11 Dim ws As AnExistingFilledInBluePrint ‘ There is no Blue Print “AnExistingFilledInBluePrint” , that is just made up for now.

    ( This prepares memory, instructions etc... for something of that and instructs how generally to deal with it. , and allows me to use intellisense through typing a period ( . ) to get a selection to choose from the available Methods and properties of that Object . For any particular Blue Print, the memory required may not be too different for an empty or filled in Blue Print. But there could be differences, that change further, when for example, things like strings are concerned that may vary considerable in length. Part of the instructions will explain how to handle such awkward things )
    _........................
    For the case of Line 10, we will be filling in from scratch our Blue Print, and do not want to mess up the original so need a copy of it.
    For the case of line 11, we are talking about the equivalent of like taking a UserForm Blue print and modifying it and filling it in such that it could be used to “make” a Worksheet. In such a case it is used by Excel itself to "do that" 1 - 3 times, every time I open up Excel !!
    So now I need a line to either copy a fresh Blue Print for An Existing Blue Print or pass over the Existing Filled In Blue Print
    Here we go

    20 Set fm1 = New UserForm1
    21 Set ws = Worksheets(“Sheet1”)


    That’s enough background there.
    _.............................

    2b) Implicit defaults for UserForms.
    Now, although one might want to, it is not so typical that one finally wants to “see” or “use” more than one UserForm of a particular form and layout at the same time. If I did , these would be the appropriate code lines for a UserForm like the one I made a Blue Print Copy for above

    10 Dim fm1 As UserForm1
    11 Dim fm2 As UserForm1
    20 Set fm1 = New UserForm1
    21 Set fm2 = New UserForm1


    If now I want to change any properties I can do so as in the following code line, for example, to change the state of the CheckBox1 ( say to check it - put a tick in it ), these would work, ( note each line is working on a different Object, a diffferent instance ot the Class – A differnet box is checked)

    50 Let fm1.CheckBox1.Value = True

    Let fm2.CheckBox1.Value = True
    It would not work without lines 10 and 20. It would error , moaning that the variable fm1 had not been declared.
    That sound reasonable.

    Because in most cases only one instance of a UserForm is used, VBA will, on any attempt to “use” something with the same name of the Class, Userform1, it will immediately effectively take code lines similar to the following as existing, ( this is the Implied default for when it first “sees” a code line attempting to use UserForm1 ):
    ( The two lines below is what VBA "does" effectively for you internally at Compile - you will never see these lines )
    10 Dim UserForm1 As UserForm1
    20 Set UserForm1 = New UserForm1

    This has actually declared a new variable UserForm1, which is an instance of the Class UserForm1.
    If I simply write

    Let UserForm1.CheckBox1.Value = True

    It will “work” as I am wanting, ( or at least as VBA is guessing I want. )

    If I change the name in the of the Class userForm1 ( double click on UserForm1 in the VB Project window and edit the first row in the Properties Window, typically bottom left ) to say myFormBluePrintClass then similarly in a code I can use

    Let myFormBluePrintClass.CheckBox1.Value = True

    A last passing observation, to help bring out the point about the Class. The following code will give you three instances, but they will always generally be the same. They are klones. They can only be distinguished bv the Variable names given ( ot the default one discussed abobe. ). Referrencing these variables can be used then to change the properties.
    But these are changes on the copy called into life by Dim Set based on / by Values of the Class UserForm1 rather than referring back to the oRefiginal.
    – P-Tang Pftang OLE Wigwam Biscuit Barrel Makro Du Moley Wolly Mod Pod ; )
    So they die when the UserForms are closed withh no effect on the oRefiginal Class UserForm1.
    Code: (and here
    http://www.excelforum.com/showthread...t=#post4384393
    Please Login or Register  to view this content.
    Attachment 460261




    Rem Ref http://gregmaxey.mvps.org/word_tip_p...nced_tips.html
    Rem Ref Rory as always


    _.........................................

    ¬_2c) Why can this catch one out, (why am I posting this “warning” )


    ......”........... Continued in next post..............”......
    Last edited by Doc.AElstein; 05-17-2016 at 03:35 PM.

  17. #17
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA to Check If User Form Buttons are checked and Check a Button on Worksheets Open

    Problem and Solution Summary

    Based on the Thread Title I think a Summary could be useful for anyone coming here on a Google Search. This was a subtle problem that caught me out. Explaining it and the solution is a nice Explanation of the Basic Class, instancing thing.

    First the Problem and answer, and then a bit of explanation. ( Names and the exact progression of what went on are changed a bit to protect the innocent and make this a bit clearer )

    _1a ) Problem:
    So I “had a UserForm.” – ( ( To “get” a UserForm ... in VB Editor .. Insert .. UserForm )
    This “thing” gets a default Name, UserForm1. I see that name in a few places.
    I double click on UserForm1 in the VB Editor, and without too much effort I can “ “drag” a check box into it “. “It” gets the Name CheckBox1. . I see in the VB Editor ( Alt + F11 ) the following:

    Attachment 460117
    The above is effectively an empty Blue Print form to represent a Class of Objects. It will be “used” by VBA as a guideline for when it creates such Objects, helping it to assign memory, has code instructions for what to do in later events associated with such Objects, how to store , use them ( in this case , for example, how to “show” it ) etc. ... etc - all to be explained further......

    So in my origianly problem, I tried to check in a code if the CheckBox1 check box is checked ( has a tick in it ) . I tried that with this code line, and everyone and every Google search told me it was how to do it

    If UserForm1.CheckBox1.value = True Then ______

    But that always gave me False.

    _1b) The Solution:

    Something like this did work

    If fm1.CheckBox1.Value = True Then

    _1c) What to do if you have this problem .. : check if your instancing of the actual userForm you are interested in has been done Explicitly, and not left to the Implicit Default

    _..................

    To er labberate
    _2 ) What’s going in?

    I got caught out because ( at least indirectly ) of the following VBA habit:
    VBA tends to guess what you want when you leave things out. ( the compiler tries to determine what it is you're asking it to do ). This is good sometimes. But then relying on these Implicit defaults can “bite you when you least expect it”. In addition I fear that this results in People forgetting what VBA does . The UserForm was done for me by someone who knows what he is doing and does not rely on the Implicit defaults. I think much of what I Googled had forgot ... something here....
    In all that lies the key to the problem I had


    That “thing” “called” UserForm1 up there is a Class. That is to say it is a Blue Print, or a form, or a questionnaire not yet filled in, a template ... .. etc. It does not really exist in the terms of a Final product. It is just describing how something of that form or type would be.

    In VBA you “Dim” things. In doing so you usually give:
    a)
    Variable : , a name, say fm1 if we are talking about UserForm things , or ws if we are talking about Worksheet things, and
    and
    b )
    A Type : , what sort of thing is it.

    For example as in these this “pseudo” code Lines

    10 Dim fm1As AnExistingBluePrint
    There is no Blue Print “AnExistingBluePrint” , that is just made up for now.
    11 Dim ws As AnExistingFilledInBluePrint ‘ There is no Blue Print “AnExistingFilledInBluePrint” , that is just made up for now.

    ( This prepares memory, instructions etc... for something of that and instructs how generally to deal with it. , and allows me to use intellisense through typing a period ( . ) to get a selection to choose from the available Methods and properties of that Object . For any particular Blue Print, the memory required may not be too different for an empty or filled in Blue Print. But there could be differences, that change further, when for example, things like strings are concerned that may vary considerable in length. Part of the instructions will explain how to handle such awkward things )
    _........................
    For the case of Line 10, we will be filling in from scratch our Blue Print, and do not want to mess up the original so need a copy of it.
    For the case of line 11, we are talking about the equivalent of like taking a UserForm Blue print and modifying it and filling it in such that it could be used to “make” a Worksheet. In such a case it is used by Excel itself to "do that" 1 - 3 times, every time I open up Excel !!
    So now I need a line to either copy a fresh Blue Print for An Existing Blue Print or pass over the Existing Filled In Blue Print
    Here we go

    20 Set fm1 = New UserForm1
    21 Set ws = Worksheets(“Sheet1”)


    That’s enough background there.
    _.............................

    2b) Implicit defaults for UserForms.
    Now, although one might want to, it is not so typical that one finally wants to “see” or “use” more than one UserForm of a particular form and layout at the same time. If I did , these would be the appropriate code lines for a UserForm like the one I made a Blue Print Copy for above

    10 Dim fm1 As UserForm1
    11 Dim fm2 As UserForm1
    20 Set fm1 = New UserForm1
    21 Set fm2 = New UserForm1


    If now I want to change any properties I can do so as in the following code line, for example, to change the state of the CheckBox1 ( say to check it - put a tick in it ), these would work, ( note each line is working on a different Object, a diffferent instance ot the Class – A differnet box is checked)

    50 Let fm1.CheckBox1.Value = True

    Let fm2.CheckBox1.Value = True
    It would not work without lines 10 and 20. It would error , moaning that the variable fm1 had not been declared.
    That sound reasonable.

    Because in most cases only one instance of a UserForm is used, VBA will, on any attempt to “use” something with the same name of the Class, Userform1, it will immediately effectively take code lines similar to the following as existing, ( this is the Implied default for when it first “sees” a code line attempting to use UserForm1 ):
    ( The two lines below is what VBA "does" effectively for you internally at Compile - you will never see these lines )
    10 Dim UserForm1 As UserForm1
    20 Set UserForm1 = New UserForm1

    This has actually declared a new variable UserForm1, which is an instance of the Class UserForm1.
    If I simply write

    Let UserForm1.CheckBox1.Value = True

    It will “work” as I am wanting, ( or at least as VBA is guessing I want. )

    If I change the name in the of the Class userForm1 ( double click on UserForm1 in the VB Project window and edit the first row in the Properties Window, typically bottom left ) to say myFormBluePrintClass then similarly in a code I can use

    Let myFormBluePrintClass.CheckBox1.Value = True

    A last passing observation, to help bring out the point about the Class. The following code will give you three instances, but they will always generally be the same. They are klones. They can only be distinguished bv the Variable names given ( ot the default one discussed abobe. ). Referrencing these variables can be used then to change the properties.
    But these are changes on the copy called into life by Dim Set based on / by Values of the Class UserForm1 rather than referring back to the oRefiginal.
    – P-Tang Pftang OLE Wigwam Biscuit Barrel Makro Du Moley Wolly Mod Pod ; )
    So they die when the UserForms are closed withh no effect on the oRefiginal Class UserForm1.
    Code: (and here
    http://www.excelforum.com/showthread...t=#post4384393
    Please Login or Register  to view this content.
    Attachment 460261




    Rem Ref http://gregmaxey.mvps.org/word_tip_p...nced_tips.html
    Rem Ref Rory as always


    _.........................................

    ¬_2c) Why can this catch one out, (why am I posting this “warning” )


    ......”........... Continued in next post..............”......
    Last edited by Doc.AElstein; 05-19-2016 at 05:10 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to check if excel file is Checked Out in SharePoing Check Out, run code, Check In
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 04:36 PM
  2. [SOLVED] VBA help password check then open form or sheet depending on option button selection
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2013, 06:35 AM
  3. vba user form to create message depend on option button/check box
    By young_86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2013, 06:12 AM
  4. Automatically check one or more check boxes when a parent check box is manually checked
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2013, 01:56 PM
  5. Replies: 2
    Last Post: 12-19-2012, 11:23 PM
  6. Can't manage to check if radio button is checked
    By Smoka in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2012, 06:54 AM
  7. Get one form control check box to be checked off of another.
    By clemsoncooz in forum Excel General
    Replies: 10
    Last Post: 01-20-2012, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1