Automatically Calculate Based On CheckBox State

Jan 23, 2008

I have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................

View 7 Replies


Tally Cell Automatically Based On CheckBox State

Jan 18, 2008

In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")

View 6 Replies View Related

Set Cell Color Based On CheckBox State

Jan 18, 2008

I want to write code for a Checkbox that when checked it fills a cell with a color, but if it is subsequently unchecked, the cell color disappears.

View 9 Replies View Related

Call Macro Based On CheckBox State

Mar 12, 2008

I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:

Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA

'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub

View 4 Replies View Related

Get State Of Checkbox On Worksheet

Jan 14, 2009

I need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.

View 3 Replies View Related

Default State For UserForm CheckBox Controls

Jun 14, 2008

I am having a little bit of difficulty find ways to effectively incorporate a checkbox panel into a form interface. What I am trying to do is all the user to select using checkboxes which of 31 separate columns will automatically be deleted or not deleted. The method I have been using is to create 31 checkboxes, and then use the value of the checkbox (true/false) to determine a value in an array, which then can be used to delete the column by a separate sub.

However, I am interested in being able to have the checkboxes start checked or start unchecked by values contained in a different array. My problem is that I am unable to reference the checkbox itself until the form containing the checkbox is opened, but at that point I don't know of a way to cause the desired checkboxes to automatically be selected without placing a command button that must be pressed to fill them in. Essentially I want to be able to open a userform that has checkboxes preselected.

View 5 Replies View Related

Automatically Create Named Range Based On CheckBox

Jan 20, 2008

I am quite new on this forum.

I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?

I have multi checkbox in column F,

So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.

I want to entry data in the Range H14:P? by this code

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

View 9 Replies View Related

Conditional Count (calculate Number Of State Holiday Days)

Jan 19, 2010

I m trying to do is this. For every person in company there is a table 30 days (cells) long, and 3 shifts (cells) wide. Every shift leader fills in the data in the "calendar" so every day, for every person, he will fill in number of hours.

I have to make excel process the whole calendar 30 x 3 = 90 cells, into final results for our payroll department. So the end product of the excel table should be Person name, total work days, total sick leave days, holiday days, and so on.

specific problem i have is how to calculate number of State Holiday days. You can see my best attempt in the attached file.

if there is only one number / mark entered each day (in only one of the 3 shifts) thansumproduct works ok.

View 8 Replies View Related

Automatically Calculate 2 Cells Based On Cell Changed

Apr 25, 2009

I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).

Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.

I really do not think the actual formulas matters at this time...

Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.

View 9 Replies View Related

Returning State Cities By Inputting Given State Abbreviation

Jun 3, 2012

I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.

View 7 Replies View Related

Return State Value Based On Match Of Name And Number Between High And Low

Aug 27, 2013

I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.


View 2 Replies View Related

Inserting State Names Based On Telephone Numbers

Oct 22, 2007

I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.

There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.

Bleh, 555-555-5555, =ChkState(B2)

I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field

Function ChkState(pVal As String) As Long

Dim AreaCode As String
Dim StateAbrv As String

AreaCode = Left(pVal, 3)

If AreaCode = "201" Then
StateAbrv = "Test201"
ElseIf AreaCode = "203" Then
StateAbrv = "Test203"
ElseIf AreaCode = "555" Then
StateAbrv = "Test555"
StateAbrv = "0"
End If
MsgBox StateAbrv
End Function

I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.

View 9 Replies View Related

Checkbox IF Functions: Calculate Checkboxes

Apr 13, 2009

I am trying to calculate checkboxes in my excel worksheet and am really having a hard time in doing so. - I am usually pretty good at formulas but this one is causing me to drink as it is probably something pretty simple that I have forgotten to do. Here is my dilemma:

Equipment Rentals.xls

I am trying to calculate that if cell C4 is checked then it will calculate cell a4*d4 (cost of equipment x number of days), or if cell E4 is checked it will calculate cell F4 *A4 and if cell G4 is check it will calculate cell H4*A4. - the totals will be calculated in cell I4 depending on what check box is checked.

When I write the formula as =IF(C4=TRUE,A4*D4,IF(E4=TRUE,A4*F4, IF(G4=TRUE,A4*H4,0)))

View 4 Replies View Related

Automatically Check A Checkbox Depending On Source

May 18, 2009

It might be very simple, but all I need is if I have data on A1, and i have 3 check boxes labeled: NEW, EXISTING, and ARCHIVED. I want it to automatically check the corresponding boxes. So if "NEW" is typed under A1, it'll check "NEW" box. "EXISTING" will check "EXISTING" check box and so on.

View 4 Replies View Related

CheckBox To Automatically Get Checked If A Selection Is Made In The Listbox

Oct 14, 2008

I have a userform with a CheckBox and a ListBox. Is there a way to have the CheckBox to automatically get checked if a selection is made in the listbox?

View 9 Replies View Related

Excel 2007 :: Inserting New Rows And Automatically Including ActiveX Checkbox

Nov 30, 2012

I'm using Excel 2007.

I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?

View 1 Replies View Related

Clear Row Based On Checkbox

Jan 16, 2008

I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared.

If the checkbox on J4 is UNchecked, then that row will be skipped and so on.

View 9 Replies View Related

Clear Combobox Based On Checkbox Value

Jan 4, 2013

I have multiple comboboxes with correlating checkboxes. That is, checkbox 1 goes with combobox 1, 2 goes with 2, etc.

When a particular button is pressed, I would like the combobox to clear it's value if it's checkbox value is false. I was hoping there would be an easier, more efficient process than what I have, which works and is as follows:


Private Sub cmdbTest_Click()
z = "dep"
Call TestSub(z)
z = "loc"
Call TestSub(z)


I currently have 8 comboboxes. Tags were used for the comboboxes during another process which are not used on the checkboxes, so that option is out.

View 1 Replies View Related

Hiding CheckBox Based On Cell Value

Mar 20, 2012

Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)

Yes, this is an activeX checkbox


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets("Dosing").Range("BM21").Value = 0 Then
CheckBoxLD.visable = False
Else: CheckBoxLD.visable = True
End If
End Sub

View 4 Replies View Related

Value For Variable Based Off Control Checkbox.

Jan 26, 2007

I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.

for an example:

if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5

I have a bit of code that is like this....

ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .

View 9 Replies View Related

Automatically Calculate The Percentage

Jun 14, 2007

code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.

View 13 Replies View Related

How To Calculate AGE Automatically Using FUNCTIONS?

Nov 1, 2008

Hi to all. I am trying to calculate a list of person's age automatically using functions. Is that possible? I am using Microsoft Excel 2007. I try searcing the web and they say DATEDIF.... But i just can't find the function in Excel 2007!

View 7 Replies View Related

Automatically Calculate Date

Mar 14, 2008

I made to automatically calculating by date which is time formating want to count the stem hours, but it prompted error?

Sub UpdateColG()

Dim IRow As Long

For IRow = 3 To Range("F").End(xlUp).Row
Range("G" & IRow).Value = Range("F" & IRow).Value - Range("F" & IRow - 1).Value
End Sub

the logical is Range (F2-F1) returns to (G2)
sample : (F2)10:30-(F1)09:45 =(G2)00:45.

View 9 Replies View Related

Make Certain Textbox Negative Value Based On Checkbox

Mar 20, 2014

I have a part of code but would need to see if there is a simple way to combine all my textbox to become negative value If my Combobox is checked.

In my Module, This will Calculate my Textbox11 each time I call for my Sub

This will call my Sub updateTextBoxes() I have set up from Textbox2 to textbox10

In my Userform I have a CheckBox1. If this CheckBox1 is Selected then I need to change the entry of all Textboxes(2-10) to be negative Value.

Works well right now but I need to identify all Textbox separately how could I bundle this up... even if a module is called...

So IF checkbox1 = True then textbox2 to textbox10 will = a negative value... If user adds 2 to textbox2 when the checkbox1 is = True then 2 will turn to -2

View 3 Replies View Related

Create New Workbook Based On Checkbox Selections

Dec 26, 2008

Could anyone please help me frame a vb code for the below explanation?

I have a sheet where in some terms are provided. Users have to open this sheet and check its description. After going through all the terms, they have to select the required terms using a checkbox given beside these terms. After checking the reqd. boxes, they would click on 'Submit' at the end of the sheet.

Once Submit is clicked, a new excel workbook should open up with the selected terms as various column headers.

View 10 Replies View Related

Write Text To Cell Based On Checkbox?

Jan 23, 2010

I have a userform with a checkbox. If the box is checked, I would likke to write "Option One" to cell A1 in Sheet1 when the Close button is clicked. I can't get the code correct for this. The workbook is attached.

View 2 Replies View Related

Concatenate Cells Based On Checkbox Selection?

May 22, 2012

VBA Code

I would like to combine data into one cell based on check box selection. I have 25 check boxes all named CheckBox1, CheckBox2, etc...

The data will be placed in cell "Q10".

Checkbox 1 would use the data from "AL130"
Checkbox 2 would use the data from "AL131"

There could be any combination of Checkboxes selected.

Each selection must be separated by a comma.

View 2 Replies View Related

IF Statement- Created Based Off A Checkbox Answer

Aug 29, 2008

I am trying to get an IF Statement created based off a checkbox answer.

I have a cell reference C2 from Checkbox that is answer Y.

If "Y" I want to calculate *0.005+0,0.03),0)*C4.

I need to skip the 1st & 2nd quarter after C4 before the calcautaion starts.

Then continuing no greater than .03 till the date changes.

I have tried to rearange this formula every way I can think of to get it to work like I want it to.

a correct formula for me to get this to calculate?

View 9 Replies View Related

Enable/Disable A Range Based Upon A CheckBox

Oct 23, 2008

I am attempting to modify a spreadsheet so that it will enable a range on Sheet3 (I9:K42) if a check box is checked on Sheet2. If it is not checked, it will lock that range on Sheet 3 and grey (or gray, if you prefer) out that range.

View 9 Replies View Related

Macro That Edits Formulas Based On Checkbox

Jul 19, 2006

When I click this button, a new row is inserted. Depending on whether this checkbox is checked, I want some of the formulas to adjust.

Public Sub AddEntry()
thisone = ActiveSheet. Name
FinalRow = Range("A65536").End(xlUp).Row
Range("A" & FinalRow + 1).Select
Selection.EntireRow.Insert 'just inserted a new row
x = Range("T1").Value
Rows(x + 3).Select
Rows(FinalRow + 1).Select...........................

View 3 Replies View Related

Copyrights 2005-15, All rights reserved