Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Nested If With Two True Conditions

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

Hi,
I was wondering how I am able to write an if statement with to true conditions.
I have attached an example.

The logic behind what i am trying to do is
if the combo-box =1 and the 2010 checkbox = true then cell reference data sheet B3
else if combo-box=2 and the 2010 checkbox = true then cell reference data sheet E3
else if 2010 checkbox = false then blank

D9 highlighted in yellow is the cell i want to populate
Help would be greatly appreciated

View Answers     

Similar Excel Tutorials

Logical Operators in Excel VBA Macros
Logical operators in VBA allow you to make decisions when certain conditions are met. They allow you to check if s ...
VBA IF Statement in Excel Macros
IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement gives you the power to ...
How to Use the IF Function in Excel
The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful ...
OR Function - Check if Any Argument is True
The OR function in Excel checks if ANY argument in it evaluates to TRUE.  If anything evaluates to TRUE, then the f ...

Helpful Excel Macros

Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
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 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

Similar Topics







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.



I would like to write a macro that looks at the checkbox in the adjacent sell and if it is checked then put the data into a new list. I would like to do this in a macro as the data is in the thousands.

maybe the code should do this

if b2="true" then put B1 in the next avaiable spot below F1
repeat for all of Col B.

inputs results
test FALSE gh
hdh FALSE djh
gh TRUE ty
djh TRUE djh
yt FALSE fj
ty TRUE djh
djh TRUE fjh
fj TRUE gggjt
djh TRUE
fjh TRUE
djh FALSE
rthjd FALSE
rtjh FALSE
tt FALSE
yt FALSE
gggjt TRUE



sample attached

Test checkbox.xlsm

thanks in advance

Hello

I have a checkBox in a userform and this checkbox is ticed, so it means true.......
I hide the iserform then.

I would like to use the value of this checkbox in the worksheet_event ?

I tried to out in the worksheet event
Code:

If checkbox.value = True then Do spmething


but the value of the checkbox in the userform was not stored, so it can be used by the sheet events.

I tried to hide the userform instead of unload, but the vaue of the checkbox disappears

How can I store the value of the checkbox in order to be used in the events ?


Regards
hmk


I need to create a checkbox that only appears if certain conditions are true.
More specifically, I need a solution that can make the following happen:

IF(113="Single Option", checkbox appears in B135, otherwise the checkbox does not appear there)

Alternatively, is it possible to make it so a checkbox in a certain location is the norm, and then if a certain condition is true, that checkbox disappears?

for example,

IF(B113="Dual Option", the checkbox disappears, otherwise the norm is unchanged and the checkbox stays visible/active)

System: Using Excel 07 on Vista.

Any help on this is incredibly appreciated! For a little background, I have no VBA experience and have been trying to do this with basic excel capabilities, so I would prefer a solution in that form. However, I am guessing that coding might be necessary, so any solutions in VBA are also happily accepted. Thanks!


I have a workshhet with column A being a date, column B is position, columns C & D are qualification questions and column E is a name. it looks like this:
DateAssigned Position RQ-RQE Qualified Last Name 4/27/2010 IA FALSE TRUE Sweet 4/15/2010 IA FALSE TRUE Friedrichsen 3/26/2010 IA FALSE TRUE Kingery 3/22/2010 IA FALSE TRUE Phillips 3/7/2010 IA FALSE TRUE Patterson 3/1/2010 IA FALSE FALSE Ashcraft 3/1/2010 IA TRUE TRUE Whillock

I want to highlight a row red if the date in A is within the past 180 days, yellow if the date is 181-365 days, and green for any dates over 365 days. In addition, row D needs to be "TRUE" to highlight the row.
I have tried conditional formatiing without success. ANy help would be GREATLY appreciated.

V/r,


Hello All,

I am trying to set up an Excel formula that calculates a $ value if a checkbox returns true or false.

Basically, if the checkbox is checked, the COST amount needs to be multiplied by the QTY value. I've attached a screen shot of the basic table.

Right now I have a basic SUM working using: =SUM(COST*QTY) but I need to include a checkbox if statement I believe. I've tried everything but can't get it to work.

Can anyone help? I'm sure it's very simple.


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?


The below is automated to show in c1 todays week number this is pulled via a if statement, were i struggle is I wish to match the week number with a day number in cell H1.

So todays date is 2/08/2010 column C44 shows a 6 (The week in the year) Column D44 shows days left - I need a formula to push the D44 to cell H4.




a1 b1 c1 d1 e1 f1 g1 h1 date Day in week Tuesday 22/06/2010 TRUE 4 current date Week Feeder Days Left Wednesday 23/06/2010 TRUE 3 02/08/2010 6 5 Thursday 24/06/2010 TRUE 2 Friday 25/06/2010 TRUE 1 Saturday 26/06/2010 TRUE Sunday 27/06/2010 TRUE Monday 28/06/2010 TRUE 5 Tuesday 29/06/2010 TRUE 4 Wednesday 30/06/2010 TRUE 3 Thursday 01/07/2010 TRUE 2 Friday 02/07/2010 TRUE 1 Saturday 03/07/2010 TRUE Sunday 04/07/2010 TRUE Monday 05/07/2010 TRUE 5 Tuesday 06/07/2010 TRUE 4 Wednesday 07/07/2010 TRUE 3 Thursday 08/07/2010 TRUE 2 Friday 09/07/2010 TRUE 1 Saturday 10/07/2010 TRUE Sunday 11/07/2010 TRUE Monday 12/07/2010 TRUE 5 Tuesday 13/07/2010 TRUE 4 Wednesday 14/07/2010 TRUE 3 Thursday 15/07/2010 TRUE 2 Friday 16/07/2010 TRUE 1 Saturday 17/07/2010 TRUE Sunday 18/07/2010 TRUE Monday 19/07/2010 TRUE 5 Tuesday 20/07/2010 TRUE 4 Wednesday 21/07/2010 TRUE 3 Thursday 22/07/2010 TRUE 2 Friday 23/07/2010 TRUE 1 Saturday 24/07/2010 TRUE Sunday 25/07/2010 TRUE Monday 26/07/2010 TRUE 5 Tuesday 27/07/2010 TRUE 4 Wednesday 28/07/2010 TRUE 3 Thursday 29/07/2010 TRUE 2 Friday 30/07/2010 TRUE 1 Saturday 31/07/2010 TRUE Sunday 01/08/2010 TRUE Monday 02/08/2010 6 5 Tuesday 03/08/2010 TRUE 4 Wednesday 04/08/2010 TRUE 3 Thursday 05/08/2010 TRUE 2 Friday 06/08/2010 TRUE 1 Saturday 07/08/2010 TRUE Sunday 08/08/2010 TRUE Monday 09/08/2010 TRUE 5 Tuesday 10/08/2010 TRUE 4 Wednesday 11/08/2010 TRUE 3 Thursday 12/08/2010 TRUE 2 Friday 13/08/2010 TRUE 1 Saturday 14/08/2010 TRUE Sunday 15/08/2010 TRUE Monday 16/08/2010 TRUE 5 Tuesday 17/08/2010 TRUE 4 Wednesday 18/08/2010 TRUE 3 Thursday 19/08/2010 TRUE 2 Friday 20/08/2010 TRUE 1 Saturday 21/08/2010 TRUE Sunday 22/08/2010 TRUE Monday 23/08/2010 TRUE 5 Tuesday 24/08/2010 TRUE 4 Wednesday 25/08/2010 TRUE 3 Thursday 26/08/2010 TRUE 2 Friday 27/08/2010 TRUE 1 Saturday 28/08/2010 TRUE Sunday 29/08/2010 TRUE Monday 30/08/2010 TRUE 5 Tuesday 31/08/2010 TRUE 4 Wednesday 01/09/2010 TRUE 3 Thursday 02/09/2010 TRUE 2 Friday 03/09/2010 TRUE 1 Saturday 04/09/2010 TRUE Sunday 05/09/2010 TRUE Monday 06/09/2010 TRUE 5 Tuesday 07/09/2010 TRUE 4 Wednesday 08/09/2010 TRUE 3 Thursday 09/09/2010 TRUE 2 Friday 10/09/2010 TRUE 1 Saturday 11/09/2010 TRUE Sunday 12/09/2010 TRUE Monday 13/09/2010 TRUE 5 Tuesday 14/09/2010 TRUE 4 Wednesday 15/09/2010 TRUE 3 Thursday 16/09/2010 TRUE 2 Friday 17/09/2010 TRUE 1 Saturday 18/09/2010 TRUE Sunday 19/09/2010 TRUE Monday 20/09/2010 TRUE 5 Tuesday 21/09/2010 TRUE 4 Wednesday 22/09/2010 TRUE 3 Thursday 23/09/2010 TRUE 2 Friday 24/09/2010 TRUE 1 Saturday 25/09/2010 TRUE Sunday 26/09/2010 TRUE Monday 27/09/2010 TRUE 5


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





I need to create a checkbox that only appears if certain conditions are true.
More specifically, I need a solution that can make the following happen:

IF(113="Single Option", checkbox appears in B135, otherwise the checkbox does not appear there)

Alternatively, is it possible to make it so a checkbox in a certain location is the norm, and then if a certain condition is true, that checkbox disappears?

for example,

IF(B113="Dual Option", the checkbox disappears, otherwise the norm is unchanged and the checkbox stays visible/active)

System: Using Excel 07 on Vista.

Any help on this is incredibly appreciated! For a little background, I have no VBA experience and have been trying to do this with basic excel capabilities, so I would prefer a solution in that form. However, I am guessing that coding might be necessary, so any solutions in VBA are also happily accepted. Thanks!

Hi. I've been trying to create an excel workbook with checkboxes. My problem relates to protecting the worksheet and being able to still use the checkboxes (checkboxes are from the 'Forms' toobar).

I've linked each checkbox to specific cells (so a 'True/False' statement appears in the cell when the checkbox is selected or deselected). I then use an 'If/Then' formula in another cell so that a message is displayed if the checkbox is ticked.

When I protect the worksheet, I've found that I can't select the checkbox - it gives me the 'Worksheet protected' message. Unprotecting the checkbox has not worked.

Unprotecting the cell the checkbox refers to works (I would rather not allow users to be able to select this cell though). From reading through the forum(s?) I gather that using VBA I could reference the checkbox directly (maybe), I have no idea how to use VBA (but will give anything a try). I'd be grateful for any advice.

Peter


Hi,

I have a spreadsheet which allows a user to calculate their net pay (Irish tax rules) on a calendar year basis.
The workbook consists of a sheet named 'Simplifed method' (in which the user enters their gross pay) and a series of 'Reference data' sheets in which I store the various tax rules/ rates per year (so there's a Reference data 2009' sheet, 'Reference data 2010' sheet etc, one per calendar year)

My query:
On the 'Simplified method' sheet, I have a checkbox which the user can tick to signal that the value entered is a monthly salary amount rather than an annual amount. At present, this checkbox is (statically) linked to cell B82 in the 'Reference data 2009' sheet. While it is not absolutely essential (the link would not have to be dynamic to work), I'm curious to find out if it is possible to automatically update the check box's linked cell reference to the reference sheet based on the year the user selects-so the linked cell would be, say, cell B83 of the 'Reference data 2009' sheet if the 2009 year is chosen and cell B83 of the 'Reference data 2010' sheet if the 2010 year is chosen etc.

I suspect that this must be done using VBA if it is possible at all. I've had a look at macros (I'm not remotely approaching the level of a macro expert) but can't seem to generate the code to change the relevant checkbox property. I'm wondering if someone in the forum can confirm whether this dynamic linking of a cell is possible.

If necessary I can post a copy of the existing spreadsheet for review purposes.


I know that clicking a checkbox (form control) can be directly set to run a macro.

I also know that you can link it to a cell so that the data in the cell reads True or False.

I have a macro that runs every time it's sheet is changed.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)


Unfortunately, it doesn't run every time the sheet changes. I have a cell linked with a form control checkbox and any time I click it the cell changes but the macro does not run. If I change the cell manually (from true to false or false to true) the checkbox reflects the change and the macro does run.

How do I make it so that the macro runs when I click the checkbox? The macro is private to the worksheet and is not under the selection list when I select 'Assign Macro...' .


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.


A small checkbox problem that should be easy.

When checkbox checked, I'd like Cell H17 to take value from Sheet "test" cell A2.
If not, Cell H17 = empty

Didn't make it further then this :

Code:

Private Sub CheckBox9_Click()
If CheckBox = True Then

If CheckBox = False Then
Range("H17") = Empty
End If
End Sub





Hello all,
I am attempting to add a check box to the form I created in excel. What I want the macro to do is if the checkBox is checked (= true) then I want the information in cell B13 to be inserted into cell F13. If the checkBox is not checked (= false) then I do not want anything to be done.

I am very new at this so I may need to have things explained to me.

Here is what I have now

Code:

Sub CheckBox1_Click()
 
If CheckBox1.Value = True Then
    Cell(F13) = Cell(B13)
Else
'do nothing
End If
 
End Sub


Thanks for the help

R


In column J I type YES or No manually for the Column heading “Project closed”. What would be the best way of putting YES or NO, instead of typing on almost 800 rows, each time there are changes.
Should I use checkbox in column K, that changes TRUE or FALSE, instead of YES or NO. Should I use another column that should have formula,
=IF(L8=TRUE,"YES","NO").
Also, if I use checkbox, is there a quick way to link the corresponding cell instead of going to each checkbox – property – and linkedCell. ..

Thanks

Sohail


Hi

I have several macros that I want to activate one after the other.

The macros would be selected via a user form that has multiple check boxes

depending on which check boxes are checked determines the macro to run


ie

Form contains 5 checkboxes and one comand button

I want, when command button is clicked for it to execute in turn the checkboxes with a value of TRUE

if checkbox 1 = True then run Macro A
if checkbox 2 = False then ???
if checkbox 3 = True then run Macro B
if checkbox 4 = False then ???
if checkbox 5 = True then run Macro A

help would be appreciated


I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX. I hope that that was a good choice.

I believe that a formula for this would be something like:

=SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3

I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.

Any ideas?

**Note: I'm just beginning to dabble with VBA, not an expert by any means**
The below is a portion of code that I'm using:

If Worksheets("Rating").OLEObjects("old_form").Object.Value = True Then
For i = 9 To 16
ActiveSheet.Shapes("check box " & i).ControlFormat.Value = True
Next i
Else
For i = 9 To 16
ActiveSheet.Shapes("check box " & i).ControlFormat.Value = False
Next i
End If

If ActiveSheet.Shapes("check box 12").ControlFormat.Value = True Then
ActiveSheet.Shapes("check box 47").ControlFormat.Value = False
ActiveSheet.Shapes("check box 47").ControlFormat.Enabled = False
Else
End If

I have 2 weird observations with regards to this:

1) if the first statement forces checkbox 12 to be false through the loop, in the second loop it still recognizes it as true. There is nothing I can do to have the second loop use false as the logical argument for checkbox 12

2) even though checkbox 12 is coming through as true (it says "True = True" when mousing over during debugging), it will not execute the following 2 lines of code to uncheck and disable checkbox 47, it just skips right over those lines.

Any thoughts? Thanks in advance


I have searched all through the threads but I can't find anything similar to my problem. I am trying to create a checkbox that when the excel file would be saved as a *.csv file, the value for the checkbox would be saved as an boolean value 0/1. I created a macro that would populate the cells with the form control checkbox and linked it to the respective cell, I just need to change the value. Any help? Thanks in advance.




I want to use the value of a checkbox on a sheet to decide which part of macro to run.

if Checkbox = true then
execute this section if box is checked
else
execute this section if box is unchecked
end if

For the checkbox on sheet, would I need to create an ActiveX Control or a Form Control? Further, once the checkbox is created, how do I determine the name of the object to use in my macro? Thanks in advance for any comments..

I've come across an issue that I can not resolve to my satisfaction. I created a worksheet with several checkboxes on the sheet. I wanted the sheet designed such that if you press one checkbox, it is the only one checked and all others are turned off (false state). To do this I added VBA code to each click event such as the following:

Code:

Private Sub CheckBox1_Click()
Application.EnableEvents = False
Cells(5, 3).Value = False ' Cell Linked to Checkbox 2
Cells(8, 3).Value = False ' Cell Linked to CHeckbox 3
Application.EnableEvents = True
End Sub

Private Sub CheckBox2_Click()
Debug.Print 2
Application.EnableEvents = False
Cells(2, 3).Value = False ' Cell Linked to CHeckbox 1
Cells(8, 3).Value = False ' Cell Linked to CHeckbox 3
Application.EnableEvents = True
End Sub

Private Sub CheckBox3_Click()
Debug.Print 3
Application.EnableEvents = False
Cells(2, 3).Value = False ' Cell Linked to CHeckbox 1
Cells(5, 3).Value = False ' Cell Linked to CHeckbox 2
Application.EnableEvents = True
End Sub


I've used similar code in Access before with no problems, but in Excel I am getting 'phantom' click events and other strange behavior. For instance, if checkbox1 is activated (True) and then I press checkbox2, the checkbox2 click event activates, and next the checkbox1 click event activates. The sheet shows the the correct checkbox views (box 1 deactivated, box 2 activated) but the associated cells will both show false.

I am not sure what I am doing wrong here. I thought that application.enableevents = False would turn off the checkbox events, but it doesn't seem to be working. Does this not work for active x components?

Appreciate any insight you might have.

Take care

Owen


I am trying to create a macro that returns a True/False value in a cell based on a checkbox. I know that the easiest way to do this is to link a checkbox to a cell but I would need to do this about 300 times in 50 workbooks. So, if I can set up one macro on a sheet that I add into the 50 workbooks I could save some time. Alternatively, if there is a way to link all of the info from the 50 sheets into one sheet, that would be even better.

This vba is working for Checkbox 1.. but I don't know how to modify this so that it repeats for 300 checkboxes.


Code:

Private Sub CommandButton1_Click()

    If CheckBox1 = True Then
        Sheets("Sheet1").Range("B2").Value = "TRUE"
    End If
    
End Sub


I'd like to put Checkbox2 value in B3, Checkbox3 value in B4, etc.
Thanks for any suggestions.


I am using the following spreadsheet. Column K (Grace Range) looks at Column B (GRC) and returns a TRUE or FALSE statement based on the following formula: =IF((B2>=45)*(B2=28 and FALSE TRUE FALSE 37 55 4 10 0 0 TRUE TRUE TRUE TRUE TRUE TRUE FALSE 10 17 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 2 16 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 19 34 5 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 6 29 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 32 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE 25 8 10 0 0 TRUE TRUE TRUE TRUE FALSE TRUE FALSE