Checkboxes And Loop
Dec 19, 2007
I have one Sheet for each day of the month, named 1,2,3,4 and so on. Each Sheet is copied from the second Sheet upon creation, so they are all the same, except for the first sheet. On each Sheet there are a couple of checkboxes (four at the moment) placed at the same position och each Sheet.
If, let's say the first checkbox on Sheet 6 is checked, then the next seven Sheets checkboxes that is placed on the same spot should become triple-state. I want this to happen in real-time, not when the Sheets are copied/created. In other words, when I click a checkbox on any given day (Sheet), the next seven checkboxes on the upcoming 7 Sheets should change state.
This is to say "this is ok for seven days, and if there has gone 7 days since the last click - the checkbox is empty.
View 9 Replies
ADVERTISEMENT
May 16, 2011
Is it possible to distinguish a checkbox that is inside a frame from one that is outside it? What I am trying to do is loop through all the checkboxes on a multipage but only those that are within a specified frame.
View 4 Replies
View Related
Apr 8, 2012
I have a userform with several checkboxes which relate to different areas to run a report on. In total i probably have 70 odd checkboxes which are set out in groups according to what they relate to (on a multipage on the userform)
I've set out the option to 'select all checkboxes' within a particular group which may contain a certain number of checkboxes (but not all). I'd like my model to run reports to the selected group of checkboxes rather than all of them.
So for example, on page 2 of my multipage i have 5 checkboxes which i've named in the GroupName section in properties as Group1. When my user selects the relevant 'Run Group 1 Reports' button on Page 1 i'd like my model to ru though code using the group of 5 checkboxes relating to Group1.
some of my groups will be for 30 / 40 checkboxes and running it though groups would save a lot of time.
View 2 Replies
View Related
Mar 30, 2007
it's been a while for me so bare with me. I've created a user form with four checkboxes each representing a worksheet. I want to loop through all the checkboxes and those that are checked should be printed.
I'm not sure how to loop through them,
View 3 Replies
View Related
Jan 24, 2013
I am trying to change checkboxes caption name using a loop so that I don't have to change them individually. Here is a simplified example of what want to do:
1) I save the caption names in a array
2) I run through a loop to rename each checkboxes using the names saved in the array
Sub change_Checkbox_Caption
Dim MyArray(1to3) as string
Dim IndexMyArray as integer
MyArray(1) = "YES"
MyArray(2) = "No"
MyArray(3) = "Maybe"
[Code]...
View 2 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Jan 21, 2007
What I have is a long list of about 100 items. I would like to set up an excel sheet that allows me to place checkboxes in each row and then whatever checkboxes have been checked, to simply collate and print out ONLY the check boxes that have been checked.
I have gotten as far as to create and rename the checkboxes but my skill ends there. I also need to know how to get each checkbox to correspond to a row.
I appreciate ANY help that you can give me.
EDIT: this doesnt NEED to be checkboxes, it can be optionbuttons if that is easier... simply something to select/deselect and print.
1.1 a blah
1.1 b blah
2.1 a blah
2.1b blah
3.1a blah
3.1b blah
3.3.a blah
etc blah
etc
View 10 Replies
View Related
Feb 22, 2013
I have inserted checkboxes in an excel worksheet to turn on/off certain data. They are working just fine. But I have set them all to not print (by unselecting the checkbox that says "print" in the format dialogue). SOME of the checkboxes don't print, but most of them do. I can't figure out why.
I've attached the file so you can take a look.
View 3 Replies
View Related
Feb 11, 2014
how to handle checkboxes in VBA
I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1
what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.
what do I need to declare or set or ? to access the state of the checkbox
View 6 Replies
View Related
Aug 25, 2008
I have a userform that contains 15 labels, each label has Numbers in it each time i click on a label the text is inserted into an excel spreadsheet. I use it as a fast input solution and works well and has done for a few years.
I Now want to Add three checkboxes to the userform
If checkbox One is ticked i want the letter "A" and the number in the label i choose to be inserted into the spreadsheet.
Checkbox Two "B"
Checkbox Three "c"
I would only ever tick one of the checkboxes never two or three so it would be A, B or C
This is the code for The Labels i use
View 13 Replies
View Related
Aug 15, 2009
If I select checkbox1 I want checkbox3 to be false and vice versa, thats simple enough. Everytime I click on one or the other checkboxes I have to reselect the one I want.
It takes two clicks to get a checkbox checked (True).
My listbox will be populated by both lists in the "Checkboxes" code once I select a checkbox, then reselct to repopulate.
Checkboxes:
View 5 Replies
View Related
Jan 30, 2010
I have data on various KPI's achieved by diff states in different months.
I am looking an automatic way of loading the data upon selection of choices from the check boxes in the attached file.
Choice could be multiple from two different options available (Months & States).
If I select state 3 also in this, with months being same, state 3 data should automatically get appended in the data visible now.
View 12 Replies
View Related
Nov 18, 2011
I have a worksheet that has multiple (form type) checkboxes on it, and I want to write code to loop through a specific range of them (Checkbox 26 to Checkbox 36 for instance), check if the value is true and then return the .Caption value to a message box, or ultimately a cell on another worksheet.
I can do it for all of the checkboxes on the ActiveSheet, but just can't figure out how to run through.
View 3 Replies
View Related
Mar 24, 2012
I've got several rows of checkboxes, I want to be able to select 1 checkbox on a row and the others not to be selected, or if they are it automatically deselects them, and then I want to select 1 checkbox on the next row and so on.
View 9 Replies
View Related
Apr 18, 2013
I don't understand why this code doesn't work:
Sub All()
Dim chk As CheckBox
For Each chk In ActiveSheet.CheckBoxes
CheckBox.Value = Checked
Next
End Sub
All I want to do is loop through all the check boxes on a sheet and check them. But the code acts as if there are no check boxes on the active sheet, I don't get any error messages it just never lands on the
"CheckBox.Value = Checked" line.
View 3 Replies
View Related
Jul 10, 2007
I'm having trouble with checkboxes - I've set up the userform but I can't seem to get it to send values to the specific cells when certain options are checked.
The way it should work is there will be an action and this could be involved with 1 process or 40 processes (and anything in between) and what will happen is the userform will pop up and ask what processes the action is applicable to. The user will then tick the correct ones and the form will then place these values in the corresponding cells next to the action.
View 9 Replies
View Related
Nov 25, 2007
a. I m trying to put up a userform that a. select different autofilteroptions (I'm using checkboxes)
b. let the user to do som autofiltering of several workbooks (the workbooks are similar regarding structure and formats).
c. take a copy of the result from the autofilter result
d. paste this in a new workbook
e. go to next workbook, do b. and c. and paste this under the result from the last session.
View 4 Replies
View Related
Dec 6, 2007
I am trying desperately to link activex checkboxes I have in an excel spreadsheet to a textbox. I need a textbox in cell K11 to appear whenever the checkbox in cell H11 is marked. Is this possible to create in excel using VBA?
View 9 Replies
View Related
Jul 21, 2008
I am trying to use the following
[code] ....
and modifying it to:
If Sheets("TPM").Range("al13").Value = 2 Then ActiveSheet.Shapes("Check Box 391").Visible = True
The first one works OK, but when I try to add an if / then statement, it gives me an error message.
I want to hide Check boxes 391 through to 426, using a single macro.
View 9 Replies
View Related
Dec 22, 2008
Basically my code creates a load of checkboxes and I need to name them appropriately as they will be used in the next part of the code.
I haven't had a problem changing the caption properties etc but I need the name to be meaningful and unique - it works fine if I just want each checkbox to be called i.e. "CB1" etc but I want the name to be derived from a range within the worksheet.
Below is an extract from the
the_row = 10
For Each Item In ActiveSheet.OLEObjects
If Item.progID = "Forms.CheckBox.1" Then Item.Name = WorksheetFunction.Substitute(ActiveSheet.Range("C" & the_row).Value, " ", "_")
If Item.progID = "Forms.CheckBox.1" Then Item.Object.Caption = ""
If Item.progID = "Forms.CheckBox.1" Then Item.PrintObject = False
the_row = the_row + 1
Next
Column C contains the name which is unique to each checkbox, I simply need to replace any spaces with "_" but when I am not defining a set text string the name of the checkbox does not change.
View 9 Replies
View Related
Feb 4, 2009
I've been trying to make this work for several hours now and it's not throwing errors at me any more, it just doesn't work. I put a couple of checkboxes on a worksheet and am trying to identify which are checked and which are not, so that I can do something with the rows that they are located on. I'm pretty sure the rest is right, I just can't get it to identify the checkboxes as checkboxes for some reason. Eventually there will be many checkboxes, but I'm trying to get the code right before I add more. Anyway, the checkboxes are from the Control Toolbox. I looked through the forums and archives but didn't find anything that would work. If anyone could help me
Dim btn as Shape
Dim num as Integer
Dim ckbx as CheckBox
With Worksheets("AC")
For Each btn In .Shapes
If btn.Type = msoFormControl Then
If btn.FormControlType = xlCheckBox Then
For num = 1 To 150
Set ckbx = .CheckBoxes("CheckBox" & num)
If ckbx.Value = xlOn Then
End If
Next num
Else
MsgBox("bugs")
End If
Else
MsgBox("bugs")
End If
Next btn
End With
View 9 Replies
View Related
Mar 4, 2009
I'm looking for a tidy way to loop through quite a large number of CheckBoxes. This does what I need to do:
If CheckBox1 = True Or Workbooks(impname).Worksheets("checklist").CheckBox1 = True Then _
Workbooks(wb1).Worksheets("checklist").CheckBox1 = True
If CheckBox2 = True Or Workbooks(impname).Worksheets("checklist").CheckBox2 = True Then _
Workbooks(wb1).Worksheets("checklist").CheckBox2 = True
If CheckBox3 = True Or Workbooks(impname).Worksheets("checklist").CheckBox3 = True Then _
Workbooks(wb1).Worksheets("checklist").CheckBox3 = True
If CheckBox4 = True Or Workbooks(impname).Worksheets("checklist").CheckBox4 = True Then _
Workbooks(wb1).Worksheets("checklist").CheckBox4 = True
It looks as though it should be possible to loop through one line but the syntax is giving me a problem. The CheckBoxes are from the Controls library.
View 9 Replies
View Related
Apr 11, 2009
I never paid attention to how to make checkboxes work since i though i would never use them but now here I am searching Mr.Excel trying to find simple answers.
Heres the deal:
I got a form that has 6 checkboxes on it, each of the boxes has a different end-value. Kinda like a scoring sytem or quiz form.
check1 = 15
check2= 15
check3 = 10
check4 = 8
check5 =5
check6 =5
then i have a textbox(1) below it that will display the total of "points" that was checked. And eventually the points would be somehow converted to a msge box ( like 20 "you fail") but Im sure theres a post somewhere about that so i'll worry about that later.
Heres what i've been trying to work with. Also why is it i cant get multiples to check is there a property setting that i'm missing?
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox1.Value = 15
Else
CheckBox1.Value = -15
End If
View 9 Replies
View Related
Jun 16, 2009
I am making a UserForm with how many hours it takes to complete a project. The format is something like:
Task/Resource Man Hours
(check box) Task name 1 # of Hours to complete
(check box) Task name 2 # of Hours to complete
etc.
I have 20 different task names, and the User enters the # of hours into a textbox. When the form is filled out the User hits OK and the cell adds up the total # of hours and produces that value.
What I want is to have the OPTION to check a box next to each task name (shown above), and for each check box checked, the Cell's value will still show the total # of hours AND have a breakdown listing each task name checked and # of hours to complete.
View 9 Replies
View Related
Oct 15, 2009
Work with ListView in excel worksheet.
My requirements are:
1) The items in the listview needs to have checkboxes (which can be set from property I guess)
2) The listview columns have 'greyed' column headings.
3) The items in listview gets populated from the data stored in Access database.
4) The listview has multiple columns
View 11 Replies
View Related
