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.
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:
VB:
Private Sub cmdbTest_Click()
z = "dep"
Call TestSub(z)
z = "loc"
Call TestSub(z)
[Code]...
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.
I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.
View 2 Replies View RelatedI have a sheet with 550 checkboxes (form toolbar). I want to be able to reset the boxes to unchecked with a button. I found an example here but I seem to need the label (Ex: "Check Box 1")
Sub UnCheckit()
ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = False
End Sub
It doesn't make sense I would need to leave the label showing on the sheet, but the label seems to be the identifer for each checkbox, not the named cell. So I am a little confused how to alter the code.
So when it works it looks like:
[] Check Box 1 Click this box to indicate something
While I wanted it to look like:
[] Click this box to indicate something
(I guess control checkboxes was the way to go but we are here now.)
I have created a checkbox which places a "TT" on a master sheet when checked on the NEW8 sheet. What I want to know is how do I get the "TT" to delete from the master sheet when unchecked on the NEW8 sheet. Below is what I have...
View 9 Replies View RelatedI have a check box that when checked needs to take information from multiple cells and copy into multiple cells and then when uncheck remove the data from the cells.
I have attached a sample.
Here is what I have so far (debugger highlights my CheckBoxLD.Visable arguments)
Yes, this is an activeX checkbox
Code:
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
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 .
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
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.
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 RelatedVBA 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"
etc...
There could be any combination of Checkboxes selected.
Each selection must be separated by a comma.
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?
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 RelatedWhen 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
Sheets("Calculator").Select
Rows(x + 3).Select
Selection.Copy
Sheets(thisone).Select
Rows(FinalRow + 1).Select...........................
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 RelatedI 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")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................
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
I currently have a spreadsheet with a userform that has multiple checkboxes. I want these checkboxes to change the value of cells depending on whether or not they are checked. 1 for not checked, 2 for checked. The column of the named range the cells are in is constant for each checkbox (though it is different for each checkbox and some checkboxes will need to change the value of a cell in multiple columns). The row changes depending on the value of a combobox on the userform. I have sorted out a code that works, but it seems very long, slow, and inefficient. I'm hopping someone can show me a better way to do it.
Private Sub CmbFinish_Click()
Dim rClSkills As Range
Dim rClLookup As Range
Dim var1 As Integer
Dim iDecision As Integer
Set rClSkills = Range("TblClSkills")
Set rClLookup = Range("ClassLookup")
iDecision = MsgBox("Are you sure you wish to change Skills?", vbYesNo, "Continue?")
If iDecision = vbYes Then
var1 = Application.WorksheetFunction.Match(cboClass.Value, rClLookup, 0)
If CboAppraise.Value = True Then.............................
married, widows, single checkbox is selected
macro solution
good work.
Trying to create a simple loop that will change the visibility of a large quantity of CheckBoxes when a certain value is selected within a ComboBox . I'm very new VBA programming and loops are not something I've tried yet. My code currently is this:
[Code]......
When I run it, I get a "Compile Error: Invalid qualifier".
I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem
Private Sub cbpDiscAlum_Click()
Dim myOption As Control
Dim myValue As Boolean
myValue = cbpDiscAlum.Value = True
If myValue = True Then
For Each myOption In pDiscounts.Controls
myOption.Visble = True
myOption.Enabled = True
Next myOption
Else
For Each myOption In pDiscounts.Controls
myOption.Visible = False
myOption.Enabled = False
Next myOption
End If
End Sub
It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.
I have a spreadsheet in Excel, there are 13 columns of information being used. 3 of the columns have just data I typed in (model name, item code, original price) the other 10 have formulas (these formulas are price discounts that will be taken off of the original price. 9 of them also have a check box on the top of the column so if the the checkbox is selected, the formula will give the customer the amount discounted off the original price(keep in mind that not all of the columns can be used together, for example, on product "A" maybe only 3 of the boxes can be used whereas on another product maybe 5 can be used). I made a multiple listbox, so that if a customer selects a product or multiple products and clicks the ok button, the sheet will only show the specific products they selected. My problem is that when I press the "OK" button nothing happens. I don't know how to link all of this together.
View 3 Replies View RelatedIn 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")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................
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
I would like to create a qestionairre with checkboxes used to answer the question "Do you need____" (Check if yes). On the following workbook I would like a list of all items needed, and a list of items not needed on yet another workbook.
Is there a way to create these lists without having blanks for values that are not true? (and because Im sure there is HOW?)
I have 2 workbooks open, one called "Temp" and the other called "CRM 2"
My active workbook is "Temp". I am trying to insert a value into the active cell in this workbook, based upon the value of a checkbox in the other spreadsheet. Here's my
If Windows("CRM 2.xls").SelectedSheets.CheckBox6.Value = True Then Selection.Value = "By Phone"
I've tried variations on this such as:
If Windows("CRM 2.xls").ActiveSheet.CheckBox6.Value = True Then Selection.Value = "By Phone"
I have 3 checkboxes; when one is checked, a set/range of rows should be visible. Only 1 checkbox should be checked at a time.
If checkbox 18 is already checked, and checkbox 20 is then checked, I want the first checkbox unchecked and the rows for checkbox hidden.
I'm using the following code. It works great as long as I check and uncheck the same box before attempting to check another box. But if Checkbox18 is already checked with its rows showing, and I then check checkbox20, the checkbox20 sub runs and as I step through, it jumps to sub checkbox18.
How can I stop my subs from jumping from one to another?
Code:
Private Sub CheckBox18_Click()
If CheckBox18.Value = True Then
Worksheets("TRF").Rows("36:41").Hidden = False
Worksheets("TRF").Rows("42:64").Hidden = True
Worksheets("TRF").Rows("65:76").Hidden = True
CheckBox19.Value = False
[Code] .........
I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E"
The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216
So - If X = E but Y does not, then the row range C:T must be cleared.
If Y = E but X does not, then the row range C:T must be cleared
If both X and Y = E, then the row range C:T must be cleared
If the letter E does not exist in either X or Y, then the row must be left alone
I'm trying to write a macro that clears the cell in the range V10:X14 if the value of that cell equals 99.
Sub MyDeleteCell()
For i = 10 To 14 Step 1
For j = 22 To 25 Step 1
If Cells(i, j) = 99 Then Cells(i, j).Clear
Next i
Next j
End Sub
It's giving me a "Invalid Next Control Variable reference" I've tried defining i and j as Integers but that didn't work.