Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Form Checkbox If Statement

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

How could I write a VB code that runs an IF statement when a user selects a checkbox on a form that would accomplish the following..

Checkbox Name - Checkbox1
Control Source: Lists!A55

When a user checks the box, the if statement would be

IF Sheets("Lists").Range("A42") > 6 Then

ActiveCheckbox = False

End If

Basically, I just want a simple logical test, and if it is true the box that the user just selected would automatically deselect itself.

I am just not sure about the ActiveCheckbox wording, like how to tell excel to "undo" what just happened.

I am trying to limit user input based on a variable, that is why I need to do this.

Thanks in advance for your help!!!


Similar Excel Video Tutorials

Helpful Excel Macros

Open a Word Document from Excel
- This Microsoft Excel macro will open a Microsoft Word document from excel. With this macro, you can open any word docume
Open a PowerPoint Presentation from Excel
- This free macro for Microsoft Excel allows you to open any PowerPoint presentation from excel. You can change the file
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Open Microsoft PowerPoint from Excel
- This free macro will open the Microsoft PowerPoint program on your computer. You do need to have this program first. T
Open Microsoft Word from Excel
- This free macro will open the Microsoft Word program on your computer. You do need to have this program first. This wi

Similar Topics







I have a group of Form control checkboxes in a worksheet. I also have a Form control checkbox labeled "All." I would like to have all of the other checkboxes checked when the user checks the "All" checkbox, but I don't know how to do this.

I have set up a macro so that it changes all of the outputs of the checkboxes to TRUE, which checks each checkbox. This is my code below:

Code:

Sub CheckBox36_Click()
Sheets("Valid Values").Range("TeamCbOutPut").Value = True
 
End Sub


Pretty simple. However, say if the user changes his/her mind and wants to deselect all, I do not know how to write code to change all of the values to False.

Any suggestions?


I have a simple activex checkbox on sheet 1.

All my VBA code is in a separate module. I'm creating an 'if' statement based on the checkbox's designation of true or false.

When I start the if statement with

If checkbox1.value = true then

........

I get a compile error that my variable is not defined. How do I refer to the activex control on sheet 1?


Okay, so I got this from
http://www.mrexcel.com/forum/showthread.php?t=3525

The page is addressing exactly what I want to do. I want to have my code check to see what the value of a checkBox is and run the corresponding click event code if it's true. These are Activex Control Boxes. My code doesn't work. I get several different errors. I have left all of the checkboxes at the default name. This is the last key to my project and I can show this tool to my manager. This is one of the major keys though. Any help would be appreciated. Sometimes I get a type mismatch error, sometimes I get an object or with block variable... error, sometimes I get an invalid character error. I have tried it a few different ways. I can't seem to make it work. Any help would be appreciated.

Code:

 
Private Sub OptionButton8_Click()
Dim i As Integer
Dim CheckBox(1 To 26) As Control
Set CheckBox(1) = CheckBox1
Set CheckBox(2) = CheckBox2
Set CheckBox(3) = CheckBox3
Set CheckBox(4) = CheckBox4
Set CheckBox(5) = CheckBox5
Set CheckBox(6) = CheckBox6
Set CheckBox(7) = CheckBox7
Set CheckBox(8) = CheckBox8
Set CheckBox(9) = CheckBox9
Set CheckBox(10) = CheckBox10
Set CheckBox(11) = CheckBox11
Set CheckBox(12) = CheckBox12
Set CheckBox(13) = CheckBox13
Set CheckBox(14) = CheckBox14
Set CheckBox(15) = CheckBox15
Set CheckBox(16) = CheckBox16
Set CheckBox(17) = CheckBox17
Set CheckBox(18) = CheckBox18
Set CheckBox(19) = CheckBox19
Set CheckBox(20) = CheckBox20
Set CheckBox(21) = CheckBox21
Set CheckBox(22) = CheckBox22
Set CheckBox(23) = CheckBox23
Set CheckBox(24) = CheckBox24
Set CheckBox(25) = CheckBox25
Set CheckBox(26) = CheckBox26
For i = 1 To 26
If CheckBox(i).Value = True Then
Call CheckBox(i)_Click
Else: End If
Next
End Sub





Hello all. First off I want to thank the forum--for almost a year now this has been a place I've found a ton of help as a new user. I appreciate all of the work that goes into helping anyone and everyone (beginner, advanced and otherwise).

Question
I've included an example. my spreadsheet is a gpa calculator. In one section there are two course groupings listed (only ONE is to be included in the gpa calculation).

When the user selects a checkbox (group 1 or group 2), I would like the opposing course group to be cleared (just the "letter grade attained" column) of any value and disabled (so the user can't put a value into the opposing course group).

I feel like this could be done with something simple like

IF checkbox is true then value is 0 and locked is true
else if locked = false

If anyone has any input it would be greatly appreciated...

IF you decide to help and can try to explain to me (briefly of course) how and why it doesn't work my way and needs to be another way that would be amazing...

Thank you in advance, sorry for the novel.
-Eric (a student willing to learn)

*Note - checkbox (not sure whether it should be an activeX Control form) would be right beside the titles "Group 1" and "group 2".

In a perfect world -Removing the course group that is disabled from the GPA calculation isn't neccessary but it would be ideal...



Hours/week Letter Grade attained in course Level 5 - Add'l Req: Take 1 of the following Groups:



Group 1


xx xx 3 d xx xx 3 d OR
Level GPA Group 2


xx xx 6 d


Hello,

Can anybody help me with a code that shows a comment within the user form when specific a checkbox is selected?

I am attaching a sample file with user form containing 6 checkboxes. I need when Name6 is selected, a comment "Test" to appear inside the user form beside the checkbox.

Count on your help!


Good Afternoon -

I'm running WinXp SP3 and Excel 2007

Is there a way to have a text alert or pop-up or something come up when a user selects a checkbox?

I'm creating a form and I have a checkbox that, if a user selects it, I want something to remind the user to submit additional information.

Thanks for all the great stuff here!

B


I have a checkbox on form. When user checks or unchecks it (changes it), I provide a msg prompt yes/no asking if user wants to proceed. If the user selects NO, I want to revert the checkbox back to original value (checked or unchecked).

For example: User checks box that was unchecked, user selects not to proceed when prompted, checkbox returns to unchecked value.

Problem is I get a single loop when code returns the box to original value (unchecked) and the message prompt appears again.
1) [BeforeUpdate] event: I am grabbing the original value and storing in a variable.
2) [Click] event: I'm running Yes/No prompt and code the resets the box to original value (chkgox.value = x). (I've also tried placing it in [AfterUpdate] event, instead but not any better.)

The only way I can see to solve this is to create/use a "runcode" variable (yes/no flag) to prevent the [Click] event's code from running the second time (after checkbox value is set back to original value by code).

Maybe I'm missing some obvious other approach. Is there a simpler way (i.e., using different events)???


Hi

I am having trouble with a check box on my userform.
I have set it up so that when the user selects the checkbox the value in a connected textbox is added to the value in a seperate textbox and displayed in that textbox.
i.e.
Textbox 1 = 9.00 (this textbox value varies)
textbox 2 = 10.00 (this textbox value also varies)
Checkbox 1 selected by user
Textbox 2 now = 19.00
However the trouble I'm having is getting the second textbox to revert back to it's original value if the checkbox is the deselected by the user.
i.e.
Textbox 2 = 19.00
Checkbox 1 deselected by user
Textbox 2 should now revert back to 10.00

I know it's probably a simple answer but if somebody could point me in the right direction I'd be very grateful cos it's doing my head in now!!!


I have a userform with a checkbox in it. The checkbox default value is set to true, but I would like it to only be true if the formula bar is visible. The code I have so far is:

Private Sub Frame1_Click()
If Application.DisplayFormulaBar = False Then
CheckBox1.Value = False
End If
End Sub

The code is placed in the frame section, but the checkbox only updates when the user left clicks anywhere inside frame 1. What I really need to do is force checkbox 1 to update as soon as the check is performed.

Thanks in advance for any help.


My User Form does what I need it to do except:

1) when I bring up the User Form (Userform2.show) I would like to preset one of the two checkbox's. Also, if I were to choose one of the checkbox's, the other checkbox displays a faint check. How can I make the other check box be blank?

2) once a command button is pressed, how do I remove the User Form? It stays displayed until I select the X in the top right corner.

Thanks very much...


Can someone please help me out? I have a form where if the user selects the checkbox, the user is required to select a value in the combo box. How can I can make the checkbox and combox field to be dependent of each other? For example, if the user did not select the checkbox, there should be no value selected in the combox box. Please help!


I have five checkboxes on a worksheet labelled check1, check2, etc

I have a piece of code to run when one of the checkboxes is true but which checkbox will vary depending on a user set variable.

So I would like to have statement which will point to the correct checkbox depending on the variable x.

Eg. If check & x.Value = True Then.......

However this is obviously not correct syntax and I have been struggling to do this. Is there any way this can be done.

The alternative I have tried is to loop though all the checkboxes on the sheet until I find the one with the correct name.

Eg. For Each CheckBox In Worksheet.Shapes
If Right(CheckBox.Name, 1) = x Then
If Checkbox.Value = True Then.........
Next CheckBox

But again this does not work.

Can anybody help with correct syntax for this, or any alternative suggestions.

Thanks

Jon


Hello Everybody,

It is my first time here so please be mercifully if I made mistakes.

I want to understand the VBA code better and I propose myself to write a simple game.
I made already some part of the code but I meet an issue that I don't know how to solve it.
It is probably simple.

So here is what i want to do.


So when I click a checkbox, if the other around him are clicked they will become un-clicked or vice versa. I think everybody knows this mini game.

Anyway, I made a function for it, that will be run from every Checkbox_Click() code.
Here is the function :

VB:

Private Function Click() 
     
    Dim i As String 
    Dim r, l, d, dr, dl, u, ul, ur As String 
     
     
     
    i = Me.ActiveControl.TabIndex + 1 
     
     'I made this to stop excel from freezing out because of the endless loop
    x = MsgBox(i, vbOKCancel) 
    If x = vbCancel Then 
        Unload Me 
    End If 
     
    r = i + 1 
    l = i - 1 
    d = i + 5 
    dr = i + 6 
    dl = i + 4 
    u = i - 5 
    ul = i - 6 
    ur = i - 4 
     
    Select Case i 
         
    Case 1 
         
        If UserForm1.Controls("Checkbox" & i).Value = True Then 
            If UserForm1.Controls("Checkbox" & r).Value = True Then 
                UserForm1.Controls("checkbox" & r).Value = False 
            Else 
                UserForm1.Controls("checkbox" & r).Value = True 
                UserForm1.Controls("checkbox" & r).Enabled = False 
            End If 
             
            If UserForm1.Controls("checkbox" & d).Value = True Then 
                UserForm1.Controls("checkbox" & d).Value = False 
            Else 
                UserForm1.Controls("checkbox" & d).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & dr).Value = True Then 
                UserForm1.Controls("checkbox" & dr).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dr).Value = True 
            End If 
             
        Else 
            If UserForm1.Controls("Checkbox" & r).Value = True Then 
                UserForm1.Controls("checkbox" & r).Value = False 
            Else 
                UserForm1.Controls("checkbox" & r).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & d).Value = True Then 
                UserForm1.Controls("checkbox" & d).Value = False 
            Else 
                UserForm1.Controls("checkbox" & d).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & dr).Value = True Then 
                UserForm1.Controls("checkbox" & dr).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dr).Value = True 
            End If 
        End If 
         
         
         
    Case 2 Or 3 Or 4 
         
        If UserForm1.Controls("Checkbox" & i).Value = True Then 
             
            If UserForm1.Controls("Checkbox" & r).Value = True Then 
                UserForm1.Controls("checkbox" & r).Value = False 
            Else 
                UserForm1.Controls("checkbox" & r).Value = True 
            End If 
             
            If UserForm1.Controls("Checkbox" & l).Value = True Then 
                UserForm1.Controls("checkbox" & l).Value = False 
            Else 
                UserForm1.Controls("checkbox" & l).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & d).Value = True Then 
                UserForm1.Controls("checkbox" & d).Value = False 
            Else 
                UserForm1.Controls("checkbox" & d).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & dr).Value = True Then 
                UserForm1.Controls("checkbox" & dr).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dr).Value = True 
            End If 
             
            If UserForm1.Controls("Checkbox" & dl).Value = True Then 
                UserForm1.Controls("checkbox" & dl).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dl).Value = True 
            End If 
        Else 
            If UserForm1.Controls("Checkbox" & r).Value = True Then 
                UserForm1.Controls("checkbox" & r).Value = False 
            Else 
                UserForm1.Controls("checkbox" & r).Value = True 
            End If 
             
            If UserForm1.Controls("Checkbox" & l).Value = True Then 
                UserForm1.Controls("checkbox" & l).Value = False 
            Else 
                UserForm1.Controls("checkbox" & l).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & d).Value = True Then 
                UserForm1.Controls("checkbox" & d).Value = False 
            Else 
                UserForm1.Controls("checkbox" & d).Value = True 
            End If 
             
            If UserForm1.Controls("checkbox" & dr).Value = True Then 
                UserForm1.Controls("checkbox" & dr).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dr).Value = True 
            End If 
             
            If UserForm1.Controls("Checkbox" & dl).Value = True Then 
                UserForm1.Controls("checkbox" & dl).Value = False 
            Else 
                UserForm1.Controls("checkbox" & dl).Value = True 
            End If 
        End If 
         
    End Select 
     
End Function 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



So when I press the first Checkbox, the other checkbox that is near it becomes active and then Variable i takes another value and it will never stop.

I was thinking to use Exit Function before the End Select but this doesn't really work.

So any ideas will be appreciated.

Thank you in advance.

Hello.

Looking for a bit of assistance, so for some background; I have 4 sheets with a decent amount of data on them. What I am looking to do is have a user form which displays an individual checkbox with the name of the sheets.

The idea is that when a summary sheet is selected the user can select which values are displayed. So if the sheets were called "England", "Ireland", "Scotland" and "Wales" the user would be able to select the values by ticking the appropriate checkbox. If no checkbox are marked there are no entries displayed (or the contents of the cells are cleared).

So far so good.

I am happy enough with the code to either set the Summary sheet cell values to Worksheets("England").Range("A1").Value or fill them with a formula adding on sheet value to another.

What I would like to know is, is there a better way to write all the permutations of the four boxes?

I have an example of where I am going with this below (using MsgBox as an indicator) but cannot help feeling this is the LONG way round. (code is set to Checkbox_Change Call CheckSelect)

Maybe a Select Case?

Any help or ideas of a better way to do this is greatly appreciated.

Maybe checkboxes are not the right tool?

Thanks for your input

G12


Sub CheckSelect()

If CheckBox1 And CheckBox2 And CheckBox3 And CheckBox4 Then
MsgBox "1+2+3+4"
ElseIf CheckBox1 And CheckBox2 And CheckBox3 Then
MsgBox "1+2+3"
ElseIf CheckBox1 And CheckBox2 Then
MsgBox "1+2"
ElseIf CheckBox1 Then
MsgBox "1"
End If

End Sub


I have 15 checkbox's on a worksheet that represent weeks 1 to 15.

Each time one of the checkbox's is click (to either true or false) I want to check if any other checkbox's are selected.

Basically, the checkbox's are there to enable the user to select a range of weeks from 1 to 15. The user can select all weeks or, for example, weeks 7 -12. If a range that isn't all is selected I want to either return an error message saying select a range, or get the code to automatically check all the box's in the range.


first of all, I apologize for asking a question that I KNOW has been answered before but, I am having trouble applying the answers I have found to my situation. I am simply trying to write a macro or code that will set all the check box values to "False" each time I initiate the User form. So that the user form does not "carry over" check box values from the last time the User form was used.
Thanks in advance for any help.any help at all..........

--edit---This did the trick!
However I had to make sure I cleared the value from the cell that each control is associated with.(the controlsource in the user form properties.)

Thanks for all your help!

Code:

Sub ResetAllCheckBoxesInUserForm()
Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        If TypeName(ctrl) = "CheckBox" Then
            ctrl.Value = False
        End If
    Next ctrl
End Sub





Hello all

I have a form that has 6 columns of fields. Each column represents a new episode. Each field in the different columns is named similarly, just with a different number: "Ep1Seclusion", "Ep2Seclusion", etc. Not all entries will use all 6 columns. So, to keep things clear for the user, I disabled each column until they are needed. I have a checkbox that the user clicks that will enable the column of fields.

To keep the code clean, I am trying to create a subroutine to enable the fields based on a variable, created by the checkbox, and then sent to the subroutine. So, if the user selects episode 2 checkbox, that column will enable.

I guess I am trying to keep from having to write out all the different fields 6 times.

Ex:
[Forms]![f Data Input]![Episode2StartTime].Enabled = True

I want to be able to change the "2" based on a variable. Is this possible?

Thanx


Can code be written to deselect a checkbox.

Something like:
Deselect.checkbox1 = TRUE
Or maybe execute code using code
Code:

If Range("C1").value = TRUE then
MakeWide
Else
EndIf


I have the MakeWide Macro attached to checkbox1

Will this actually physically change the checkbox? to select/deselect?

Thank You,
Michael


Hi,

I'm doing a macro in VBA/Excel and I have a userform where I put a checkbox which the user can tick if he/she wants the macro to create a new sheet.

In the userform code, I have added a Private Sub for when the checkbox is ticked. The code is as follow:

VB:

Private Sub CheckBox1_Click() 
    If CheckBox1 Then 
        Sheets.Add 
        InputForm.TextBox1.Enabled = True 
    End If 
     
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



What I want is that when the user tick the checkbox a new sheet is created and the field TextBox1, which is in my userform next to the checkbox, is enabled so that the user can type in the name for the new sheet.

I have run the macro and when I tick the checkbox, nothing happen. No worksheet created and the Textbox1 remain disabled.

Can anybody help?
Thanks

Loic

Hi,

I have two userforms. Userform1 is loaded first, there is a checkbox1 in the Userform1. When Userform1 is opened under the activate event I have the code that sets the checkbox1 = true if a cell in the spreadsheet is equal to 1 and false if it is equal to zero. So the purpose was to remember what the user input so they would not have to recheck the checkboxes.

But here is the problem. When the Userform1 is activated and the "checkbox1" is set equal to true, for some reason the click event (for the checkbox) is activated. This loads the Userform2 (because clicking on the checkbox is supposed to start Userform2).

I dont understand why setting checkbox1.value = true starts the "click" event for checkbox1. I also noticed that setting checkbox1.value = false does not start the click event for checkbox1. I guess I need to change the value of the checkbox but I dont want to start the Click event of the checkbox.

Windows XP, Excel 2003

Thanks for your help


I have a checkbox on a worksheet (inserted by using the control toolbox). The checkbox itself does exactly what I want it to.

However, I have a class module (taken from another website site and works 99% what I need it to, except for this problem I have), and part of the code needs to depend on what the value of the checkbox is.

For example,

the checkbox is called "checkbox1" on "sheet1".

In my Class Module, I want to say

If checkbox1 (on sheet1) = True Then

Else

End If.

However, I'm obviously not referring to the checkbox right as it always ends up in the false scenario, regardless of the value of the checkbox.

I'm very new to vba, so apologies if this is reasonably simple.


EDIT: I should say, that is obviously nothing like the actual code I'm using. Just a large simplification as to avoid over-complication of what I think is a simple issue.


Hi all,

in my latest project I am using checkboxes to let the user choose between options. Now as I am trying to deliver output based on the checked/not checked checkboxes I would like to make use of loops. Basically what I want my code to do is to check the values of the checkboxes, starting from checkbox 1 to 6 and exit the loop when the the first checkbox checked is determined. Also I don't know how to use the exit property to end the loop if the if-statement is met.

e.g. (whereas i would be the indicator for the name of the checkbox)

For i = 1 to 6
If checkbox & i.value= True then
perform task
exit
End If
Next i

Thanks a lot for your help!

Cheers


Hi,

I have a form with checkbox 1 and 2. I am using the add item code to populate checkbox 1 with 6 values, A B C D E F. When the user selects a value i want to populate checkbox 2 with the same 6 values minus the value the user has selected in checkbox 1.

Any idea how i can do this please?

Thanks


Hi,

I have a form with a checkbox in Access for 3 items. The user has the option of clicking 1,2, or all 3 boxes(They are 3 different Counties). Using the logic if the checkbox has been clicked, I would like to write a Select query with SQL that will make the criteria of the County field to include what has been clicked. This could be 1, 2, or 3 boxes.

I am not familiar with the syntax needed to write something like this. Will someone please get me in the right direction? Thanks!




Hi all,

Got a tough one for y'all.

I'm writing a code that allows the user to perform actions on shapes on a powerpoint slide that satisfy a set of conditions as determined by checkboxes on a userform. For example:
Checkbox 1: Is a rectangle Checkbox 2: Has fill color as red Checkbox 3: Has outline color as red Checkbox 4: Contains text Checkbox 5: ...
These conditions should be checkbox style--in other words, the user should be able to either set just one condition, or several, or all of the conditions.

Does anyone know the smart way to set up the IF statement? I can set up this with some highly repetitive and cumbersome code, but am looking to see if there is a better way. The "case" method doesn't seem particularly helpful either, since there are a total of 6 conditions, and 2^6=64 different IF statements or case statements would be highly undesirable...

Sample cumbersome code:



Please Login or Register  to view this content.