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.
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
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
The corresponding User Form Code looks like this:
_.........................
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
Then in the code
Private Sub Worksheet_Change(ByVal Target As Range)
I have this line to check that 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)
_......
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 ! ....
Bookmarks