Class related Stuff Userforms

**DocAElstein** · 12-26-2018, 04:27 PM

Some old notes, recovered / repaired from here
https://www.excelforum.com/excel-pro...n-on-open.html
7 May 2016 up to about the 11 May, then possibly the original Problem and Solution Summary , beefed up a bit after the thread Help Understanding Class Instancing. Can't Set ws = New Worksheet. Intellisense offers that went from 11 May to 16 May. The last edit on the original of all this was on the 19 May, (but I may have messed the last posts up a bit)

This is what I got. Rory did it for me
UserForm Rory did for me.jpg UserForm Rory did for me.jpg

UserForm Rory did for me.JPG

Code:

'Static fm As ufResults  ' instead of this I have it as a Global variable so that i can check it elsewhere, and I add it and check a box on opening the file
'Dim fm As ufResults ' This would result in the UserForm "Dieing" a t every code ending
    If Fm Is Nothing Then Set Fm = New ufResults ' if this line was not yet done, then a new instance of the class ufResults with the name fm is made
    If Not Fm.Visible Then Fm.Show False 'make sure UserForm is always there,
'Start of Part only done for entry in the three C column Ranges and no check for Calculate all else. --  Things here are that can be done quickly, 1 "row" stuff!!                  http://www.excelforum.com/showthread.php?t=1107695&p=4380613&highlight=#post4380613

**DocAElstein** · 12-26-2018, 04:28 PM

https://www.excelforum.com/excel-pro...ml#post4380945
https://www.excelfox.com/forum/showt...ll=1#post24168

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.

Code:

Sub CheckOptionCheckCheckedCheck()
Dim v As Variant
Let v = StatusBarNormal.Value
Let v = OptionButton2.Value
Let v = Refresh.Value
End Sub

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

Code:

Option Explicit
Public RefreshCColumn As Boolean 'For Check box to recalculate all values

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

Code:

'Part 6) Possible Refresh ( Afforderlisch wenn a value is changed on an existing column to Refresh all )' Will be done for check in Refresh Check box
    If RefreshCColumn = True Then

The corresponding User Form Code looks like this:

Code:

Private Sub Refresh_Click()
    If Refresh.Value = True Then
    Let RefreshCColumn = True 'Global variable as Flag for if Refresh is checked
    Else
    End If
End Sub

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

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

Code:

Private Sub StatusBarNormal_Click()
Application.DisplayStatusBar = False 'Normal Situation
End Sub

Private Sub OptionButton2_Click()
Application.DisplayStatusBar = True
End Sub

Then in the code
Private Sub Worksheet_Change(ByVal Target As Range)
I have this line to check that state..

Code:

 'All From here Parts 2) to 5) will be done for an appropriate check to do all other as well as quick stuff
'Start Other parts ---( Parts 2) - 5) )----------------------------------------------------
    If Application.DisplayStatusBar = False Then 'Parts to complete all calculations and Totals Outputs
    'I have a radio ( option ) buttons on the UserForm and can make Status Bar False, ( That is to say in it's normal state )

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)

Code:

'Part 0.5 )RoryA UserForm                                                           http://www.excelforum.com/excel-programming-vba-macros/1086822-contents-of-an-array-derived-from-a-macro-into-a-fixed-display-window-user-form.html#post4152417
Static fm As ufResults: If fm Is Nothing Then Set fm = New ufResults: If Not fm.Visible Then fm.Show False  'make sure UserForm is always there,

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

**DocAElstein** · 12-26-2018, 04:28 PM

https://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html#post4380955
https://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html#post4380967
https://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html#post4380992
https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms?p=24169&viewfull=1#post24169

Answer from Richard ( RIP (: )

Originally Posted by Richard Buttrey

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

Code:

If UserForm1.OptionButton1 Then 
    '....your code if option button has been selected
Else
   ' Code (if any) if ob not selected
End If

Originally Posted by Doc.AElstein

Hi Richard

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

Originally Posted by Doc.AElstein

Hi Richard,

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

Code:

Sub CheckOptionCheckCheckedCheck()
Dim v As Variant
Let v = ufResults.StatusBarNormal.Value
Let v = ufResults.OptionButton2.Value
Let v = ufResults.Refresh.Value
End Sub

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

**DocAElstein** · 12-26-2018, 04:28 PM

https://www.excelforum.com/excel-pro...ml#post4381146
https://www.excelforum.com/excel-pro...ml#post4381201
https://www.excelfox.com/forum/showt...ll=1#post24170

Rory starts chipping in 05-08-2016 8 May 2016 ….._
_..... and away we go

Originally Posted by romperstomper

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

Originally Posted by Doc.AElstein

Hi Rory,
Morning. Thanks fort he reply.

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:

Code:

'Part 0.5 )RoryA UserForm                                                           http://www.excelforum.com/excel-programming-vba-macros/1086822-contents-of-an-array-derived-from-a-macro-into-a-fixed-display-window-user-form.html#post4152417
Static fm As ufResults: If fm Is Nothing Then Set fm = New ufResults: If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,

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

Code:

StatusDeBuger:   Dim v: v = ufResults.OptionButton2.Value: Debug.Print "ufResults.OptionButton2.Value "; v

And

Code:

EresDeBuger:     v = ufResults.Refresh.Value: Debug.Print "ufResults.Refresh.Value "; v
GlobieDeBuger:   v = RefreshCColumn: Debug.Print "Global RefreshCColumn= "; v

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

Code:

Static fm As ufResults: If fm Is Nothing Then Set fm = New ufResults: If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,

Does not work like this:

Code:

Static fm As ufResults: If fm Is Nothing Then Set fm = New ufResults
    If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,

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

**DocAElstein** · 12-26-2018, 04:29 PM

https://www.excelforum.com/excel-pro...ml#post4382657
https://www.excelfox.com/forum/showt...ll=1#post24171

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 )

Code:

StatusDeBuger:   Dim v: v = ufResults.OptionButton2.Value: Debug.Print "ufResults.OptionButton2.Value "; v

Code:

EresDeBuger:     v = ufResults.Refresh.Value: Debug.Print "ufResults.Refresh.Value "; v

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

Code:

Static fm As ufResults
    If fm Is Nothing Then Set fm = New ufResults
    If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,

Then a bit further down I have this DeBug Line

Code:

StatusDeBuger:   Dim v: v = ufResults.OptionButton2.Value: Debug.Print "ufResults.OptionButton2.Value "; v

Towards the end of the code I have a line which puts the contents of an Array into a list in the User Form

Code:

    fm.lstResults.ColumnCount = UBound(ArrDisplaySP(), 2): fm.lstResults.List = ArrDisplaySP(): fm.Repaint

Shortly after that I have my second Debuger line

Code:

EresDeBuger:     v = ufResults.Refresh.Value: Debug.Print "ufResults.Refresh.Value "; v

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

**DocAElstein** · 12-26-2018, 04:32 PM

https://www.excelforum.com/excel-pro...ml#post4382670
https://www.excelfox.com/forum/showt...ll=1#post24172

Originally Posted by romperstomper

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.

**DocAElstein** · 12-26-2018, 04:32 PM

https://www.excelforum.com/excel-pro...ml#post4382748
https://www.excelfox.com/forum/showt...ll=1#post24173

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 ?

Code:

Sub CheckOptionCheckCheckedCheck()
Dim v As Variant
Let v = ufResults.StatusBarNormal.Value 'Always gives False
Let v = ufResults.OptionButton2.Value 'Always gives False
Let v = ufResults.Refresh.Value 'Always gives False
'Let v = fm.StatusBarNormal.Value 'At attempt to run code error with complie "error variable not defined"
'Let v = fm.OptionButton2.Value
'Let v = fm.Refresh.Value
End Sub

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

Code:

    If fm Is Nothing Then Set fm = New ufResults
    If Not fm.Visible Then fm.Show False 'make sure UserForm is always there,
Static fm2 As ufResults
    If fm2 Is Nothing Then Set fm2 = New ufResults
    If Not fm2.Visible Then fm2.Show False

May be I do get it... a bit,
I did this, and the second code does not work...

Code:

Sub MakeAInstanceOfufResults()
Static fm2 As ufResults
    If fm2 Is Nothing Then Set fm2 = New ufResults
    If Not fm2.Visible Then fm2.Show False
Dim v As Variant
Let v = fm2.StatusBarNormal.Value 'At attempt to run code error with complie "error variable not defined"
Let v = fm2.OptionButton2.Value
Let v = fm2.Refresh.Value
End Sub
Sub CheckOptionCheckCheckedCheckOffm2()
Dim v As Variant
'Let v = fm2.StatusBarNormal.Value 'At attempt to run code error with complie "error variable not defined"
'Let v = fm2.OptionButton2.Value
'Let v = fm2.Refresh.Value
End Sub

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

But now I do this in a normal Module:

Code:

Public fm2 As ufResults
Public fm As ufResults

(_....And this in my Worksheet:Change code..

Code:

'Static fm As ufResults

_.......)
and all is well

All the following work , that is to say give the results I expect.

Code:

Option Explicit
            'Public RefreshCColumn As Boolean 'For Check box to recalculate all values  ' I do not need this now as i think the Nut has User Forms sussed...
Public fm2 As ufResults
Public fm As ufResults
Sub MakeAInstanceOfufResults()
    If fm2 Is Nothing Then Set fm2 = New ufResults
    If Not fm2.Visible Then fm2.Show False
Dim v As Variant
Let v = fm2.StatusBarNormal.Value
Let v = fm2.OptionButton2.Value
Let v = fm2.Refresh.Value
End Sub
Sub CheckOptionCheckCheckedCheckOffm2()
Dim v As Variant
Let v = fm2.StatusBarNormal.Value
Let v = fm2.OptionButton2.Value
Let v = fm2.Refresh.Value
End Sub
Sub CheckOptionCheckCheckedCheckOffm()
Dim v As Variant
Let v = fm.StatusBarNormal.Value
Let v = fm.OptionButton2.Value
Let v = fm.Refresh.Value
End Sub

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

I Thanks us, you and me
Alan

**DocAElstein** · 12-26-2018, 04:33 PM

https://www.excelforum.com/excel-pro...ml#post4382778
https://www.excelfox.com/forum/showt...ll=1#post24174

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.

**DocAElstein** · 12-26-2018, 04:33 PM

https://www.excelforum.com/excel-pro...ml#post4382802
https://www.excelforum.com/excel-pro...ml#post4382894
https://www.excelfox.com/forum/showt...ll=1#post24175

Thanks Rory,

Just one last thing to make sure I got it..
Originally Posted by rorya
... That's why the changes weren't being reflected in your code - you were referring to two separate instances of the form......

I was referring originally ( 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

_.___________
Rory (Romperstomper) 05-10-2016, 10 May 2016

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:

Code:

Sub foobar()
    Dim fm As UserForm1
    Set fm = New UserForm1 ' created an instance of the form
    MsgBox UserForms.Count ' you'll see 1 here
    UserForm1.Caption = "Loaded another instance" ' this loaded a new instance of the form and assigned a variable called Userform1
    MsgBox UserForms.Count ' so you'll see 2 here
End Sub