Email:      Pass:    Pass?


Advertisements


Free Excel Forum

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


Similar Excel Video Tutorials

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







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.

Hi everyone,



First post here many thanks in advance to those that are kindly able/willing to assist. I'm running Excel 2010 (Windows 7)

I'm trying to find the most efficient way to return a "sequence of TRUE values" that qualify with a TRUE checkbox condition from a range of 9 data values (C10 to C18)

I've setup 9 checkboxes linked to 9 different cells (AO10 to AO18) that display a TRUE/FALSE value when each respective checkbox is checked/unchecked respectively.

C10=Data A [TRUE] linked to cell AO10=FALSE
C11=Data B [TRUE] linked to cell AO11=TRUE
C12=Data C [TRUE] linked to cell AO12=TRUE
C13=Data D [FALSE] linked to cell AO13=FALSE
C14=Data E [TRUE] linked to cell AO14=TRUE
C15=Data F [FALSE] linked to cell AO15=FALSE
C16=Data G [TRUE] linked to cell AO16=TRUE
C17=Data H [TRUE] linked to cell AO17=TRUE
C18=Data I [FALSE] linked to cell AO18=FALSE

On a different sheet I'd like to display only the qualifying Data Values with a TRUE condition in sequence. In the above example, there are 5 TRUE conditions so I'd like to display 5 data values in sequence such as:

Data B
Data C
Data E
Data G
Data H

Further the checkboxes can occur in any combination. The above is only one example of numerous possibilities.

I imagine a complicated set of IF statements if setup properly could do the trick but it seems there must be a more streamlined solution by using a combination of MATCH / INDEX and SEQUENCING function?

Any help is certainly much appreciated.

thanks,
santosing


Hi, I have a checkbox which will alter the value of a specific combo box when it is checked. The problem is that I need to click TWICE on the checkbox to get "check" display. May I know is there sth wrong with these codes?

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox1.Value = True
ComboBox1.Value = "Selected"
Else
ComboBox1.Value = "Please select"
End If
End Sub

The value of the Combo Box did change when i clicked on the checkbox but the "check" sign didn't appear.
Thanks in advance.


Hi,

I'm using a checkbox to make a cell true or false as it is linked to a graph to turn the data on or off. I now have want to stop a checkbox being ticked if a certain checkbox is already checked. The problem is that, using a checkbox enters a true or false into the linked cell but without pressing the enter key. So the cell is effectively empty. Is there a bit of code to press the enter so I can then link this to the checkbox


Hi, i was wondering if it's possible to have a macro that shows only the selections on the checkbox.Checkbox where the cases are worksheets.
Example:

If we have the months on a checkbox and we select 3 (january,march,april) we click on the button and we are going to have only the worksheet january,march,april. The rest of the worksheets with all of the monts will be deleted.

More or less like this :
Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then
ThisWorkbook.Sheets("2010, January").Activate
Else
ThisWorkbook.Sheets("2010, January").Delete
End If
If CheckBox3.Value = True Then
ThisWorkbook.Sheets("2010, March").Activate
Else
ThisWorkbook.Sheets("2010, March").Delete
End Sub
Thank you,

JohnnyQ "Newbie"


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,


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!


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


Hi all,

I posted this earlier but when I saved it it noted that I wasn't logged in so I'm not sure if this is a double post or not. If it is, please excuse my incompetence as I'm learning how ot navigate this site.

Hi all.

I need your help to optimize the following formula. I'm new to excel and to this forum and programming skills are very limited so would like to work with a formula if at all possible that is Excel version backward compatible

- The task is to evaluate three checkbox's where only one of the three can be checked/true at any one time or all three have to be unchecked. IF more then one of the three grouped checkboxs are selected a message such as "Select only One" needs to be displayed

- the fourth checbox is independent of the three but if true, compute a different value from a false/off state

The checkbox's are linked to colums M, N, O P where, M, N & O are the grouped checkbox's and P is the control checkbox that will determine which value to work with.

I created the following formula but excel wont let me create it.

=IF(AND(M2=FALSE,N2=FALSE,O2=FALSE,P2=False)," ",
IF((AND(M2=TRUE,N2=FALSE,O2=FALSE,P2=TRUE)),80,
IF(AND(M2=FALSE,N2=TRUE,O2=FALSE,P2=TRUE),160,
IF(AND(M2=FALSE,N2=FALSE,O2=TRUE,hP2=TRUE),175,
IF(AND(M2=FALSE,N2=FALSE,O2=FALSE,P2=False)," ",
IF((AND(M2=TRUE,N2=FALSE,O2=FALSE,P2=FALSE)),90,
IF(AND(M2=FALSE,N2=TRUE,O2=FALSE,P2=FALSE),170,
IF(AND(M2=FALSE,N2=FALSE,O2=TRUE,P2=FASLE),185,"Select only 1"))))

I hope this information is enough to get this started. Thank you for your time and help with this request.




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.


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


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





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


I have two worksheets in the same workbook.
One sheet (Sheet 1) has a list of products that have a Form checkbox resulting in a TRUE/FALSE option for each row.
The second sheet ( Sheet 2) has formulae, row by row, testing the TRUE/FALSE result of each row in Sheet1 and if TRUE, replicates the contents of the product name contained with Sheet 1.
Understandably when the test is FALSE, the formula is set to display nothing and the resulting row is blank i.e. =IF(Sheet1!D1=TRUE,Sheet1!A1,"")

This leaves me with numerous blank rows, albeit there is still a formula.

Is there a formula ( maybe VLOOKUP which confuses me) whereby in Sheet 2, each row will be populated with the contents of a particular cell ( product name) row by row , from Sheet 1 only if the checkbox test is TRUE, thus eliminating the blank rows.
Hope this all makes sense.




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


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 am trying to use checkboxes that will place one of two values in a cell
based on the checkbox being TRUE/FALSE. The spreadsheet I have created
calculates my baseline annual salary. In doing so, I had to list the
holidays that the county recognizes. If I work that holiday, then I get paid
for 16 hours. Otherwise I get paid for 8 hours. The problem is that the
formula is not picking up the ELSE statement. The value placed in the cell
is 16, regardless of the checkbox being TRUE or FALSE.

The cells involved a
E23:E33 (name of holidays)
G23:G33 (value of hours worked - 8 or 16)
H23:H33 (placement of checkbox)

The formula that I created in the macro is:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then Worksheets("Pay-Calc").Cells(23, 7).Value
= 16 Else Worksheets("Pay-Calc").Cells(23, 7).Value = 8
End Sub

NOTE: 1) The IF statement is on one line in the macro. 2) The appropriate
adjustments were made to reference the correct CHECKBOX and CELL - ie
CHECKBOX1 - CHECKBOX11 and CELLS(23,7) - CELLS(33,7)

Can anyone help me out with this?

Thanks,
Les



I have some code for another project which was using checkboxes to select elements to copy/paste.

However, I need too many checkboxes and I think it is slowing down the run time.

I'm wondering if it's possible to have a cell act as a true/false checkbox?

Then I would just change my code from

if checkbox1.value = true then...

to

if range("A1").value = true then....


I have started using check boxes more and more now and I have been using the Linked Cell in the checkbox properties. In cell formulas, I reference the linked cell as either a TRUE or FALSE to determine what I want. (Mostly if text is suppose to display or not)

This is working well, but a bit messy.

I was wondering if I can reference the checkbox directly in a formula. This would help keep my spreadsheet a bit cleaner and I wouldn't have to have the cells with True false all over the place - if it can be done.

Just wanted to make sure I wasn't overlooking something obvious.

Thanks,
Mark


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





Hi,

I've got a userform with 6x5 checkboxes that I want to use to control cell values in my workbook. If the checkbox value is true I want to have "X" as a value and if false the cell should be empty. All of the checkboxes are named A-E, 1-6 i.e. A1, A2, B1, B2 etc.

I've thought that I figured everything out by using this code but it seems to be both ineffective and not working correctly.

Any ideas how to make this work properly?

Thanks in advance!

Code:

If F1 = True Then
Range("B7").Value = "X"
ElseIf F2 = True Then
Range("B8").Value = "X"
ElseIf F3 = True Then
Range("B9").Value = "X"
ElseIf F4 = True Then
Range("B10").Value = "X"
ElseIf F5 = True Then
Range("B11").Value = "X"
ElseIf F6 = True Then
Range("B12").Value = "X"
End If

If L1 = True Then
Range("C7").Value = "X"
ElseIf L2 = True Then
Range("C8").Value = "X"
ElseIf L3 = True Then
Range("C9").Value = "X"
ElseIf L4 = True Then
Range("C10").Value = "X"
ElseIf L5 = True Then
Range("C11").Value = "X"
ElseIf L6 = True Then
Range("C12").Value = "X"
End If
If S1 = True Then
Range("D7").Value = "X"
ElseIf S2 = True Then
Range("D8").Value = "X"
ElseIf S3 = True Then
Range("D9").Value = "X"
ElseIf S4 = True Then
Range("D10").Value = "X"
ElseIf S5 = True Then
Range("D11").Value = "X"
ElseIf S6 = True Then
Range("D12").Value = "X"
End If

If I1 = True Then
Range("E7").Value = "X"
ElseIf I2 = True Then
Range("E8").Value = "X"
ElseIf I3 = True Then
Range("E9").Value = "X"
ElseIf I4 = True Then
Range("E10").Value = "X"
ElseIf I5 = True Then
Range("E11").Value = "X"
ElseIf I6 = True Then
Range("E12").Value = "X"
End If
If M1 = True Then
Range("F7").Value = "X"
ElseIf M2 = True Then
Range("F8").Value = "X"
ElseIf M3 = True Then
Range("F9").Value = "X"
ElseIf M4 = True Then
Range("F10").Value = "X"
ElseIf M5 = True Then
Range("F11").Value = "X"
ElseIf M6 = True Then
Range("F12").Value = "X"
End If