In Excel 2003, I have a checkbox in a cell (C15). I want a range of cells (F15:O15) to respond to this box. When the box is not checked and someone attempts to alter one of the cells in the range, I want a message box to appear, and then to have the data input in that cell removed.
The cells in the range are a mix of drop down lists using data validation and check boxes, so I need to make sure it doesn't erase those, just the choice made from the list or the mark in the box.
If possible I want to know if the code below can be changed. At the moment it searches for cells not marked with an X and then prints the remaining cells.
i'm having a bit of a issue with a do while loop. It might just be my complete lack of understanding, but i'll briefly explain what I am doing before I show you the code. I am marking cells with what I consider an invalid type for that cell red. Well unfortunatley in some sheets the number of invalid types is so high the macro crashes. So my bright idea was to inclose the check statment into a do while loop, that will only check until so many cells have been marked red. So I created a public variable, assigned it a value of 1. I then assigned the macro that marks the cell red to also take y and add 1. So in theory (mind you what I take for theroy might be in my head) it should only run until the paramaters of the do while are met which in this case are do while y < 20.
y = 1 Do While y < 20 Set MyRange = Intersect(Columns(7), ActiveSheet.UsedRange) For Each r In MyRange CheckDate Next r Loop
That is the snippet from the program calling the function, and here is the function
Public Function CheckDate() If IsDate(r.Cells) = False And IsEmpty(r.Cells) = False Then r.Select MarkDate y = y + 1 End If If r.Cells < 1 / 1 / 1910 And IsEmpty(r.Cells) = False Then r.Select MarkDate y = y + 1 End If End Function
what i'm doing wrong here? Oh and y is assinged as a public varaible, integer data type.
I have a command button and ten checkboxes - one for each row of a list. I need a macro that will delete the rows of every checked checkbox when I press the command button.
The list starts in row 3 and continues to row 12. Checkboxes are checkbox1 for row 3 to checkbox10 for row 12.
I have a worksheet that has been created to essentially request information needed to determine a demo system configuration. Within this worksheet there are a series of checkboxes that if checked, will need to have the form extended (i.e. add additional questions to clarify say a model or manufacturer). For example, if the Yes checkbox is checked next to a question I want a macro to copy some rows from a hidden worksheet within the same workbook to the bottom of the non-hidden worksheet. If the No checkbox is clicked, nothing should happen. Furthermore, if a user accidently click Yes but didn't mean to I want the added rows to be removed. This is further complicated by the fact that there are 2 or 3 questions that have check boxes that can add additional questions.
I currently have a macro which will copy a row of data from one sheet and paste it into the first blank row on another sheet "Invoices".
Now I want to write some code that will insert a checkbox to the left of each new row of data, (presumably in column A). I'm getting confused because I have never worked with checkboxes before and when I search the net for info. I'm getting some hits on Userforms (which I don't want), others on ActiveX controls and still others on form controls (what I think I need).
Here is some more info on what I'll be doing with them:
I am writing a second macro to see which boxes are checked. For any checked boxes I want the respective data to be sent to a mail merge and then the entire row, data and checkbox, to be deleted.
This adds a minor complication because if I delete a row, then all rows and checkboxes below it will need to move up. Is this something that will happen naturally or do I need to create some special code to handle that.
The final complication is that the data is data is going to sorted sometime between the creation of the checkbox and its later deletion. I'm wondering whether its necessary to keep a checkbox associated with a particular row, or if I can just tell the checkbox to figure out which row it exists at.
So, to sum up. I need to: First - Learn how to create a checkbox Second - Scan through all existing checkboxes and see which ones are checked Third - Delete rows and sort the data while maintaining one checkbox for each row of data.
I am trying to determine that if a range of cells are marked with an "X", then cells containing "Pass" (A4), Pass with Opportunity for Improvement (A6)or "Fail" (A8) will be marked with an "X". I have 3 columns that are selectable for Pass, Fail, and Non-Applicable. There are a total of thirteen questions that this evaluation form asks, and the evaluator must check one box. In order to be considered a Pass, there must be more 12 or more "X"'s marked in the Pass column. A Pass with Opportunity for Improvement would be equal to 11 marked in the Pass column, and a Failure would be 10 or less showing up in the Fail column. If there is a check in the non-applicable section, this has to be figured into thye pass fail as well. A Pass would be 85% or higher, and failure anything less than this. I will attach the file for review. I would very much appreciate help on this problem first, and then there is one other element to the sheet that plays in at the end.
I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...
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
I'm looking to see if it's possible for me to leave information in specific cells hidden unless a checkbox is marked off. I do not need/want the entire column or row hidden (which I've found plenty of tutorials on via google)
I'll try to provide a basic example:
ie: Column A1: checkbox;;;Column B1: Dairy;;;Column C1: milk;;;Column D1: slim milk Column A2: checkbox;;;Column B2: Poultry;;;Column C2: beef;;;Column D2: hamburgers;;;Column E2: bubba burgers (<--not a fan of them)
(the values in row C and D will never change - there are some instances where there are values in column E)
I need the checkboxes in column A and the information in column B visible at all times but C and D (and E) visible only if the checkbox is checked.
Here is an example code I tried creating myself (with .entirerow.hidden and .entirecolumn.hidden codes I found online). Obviously it didn't work lol, but I was wondering if there is a way to only get these specific cells to hide rather than an entire row/column.
Sub CheckBox1_Click() If CheckBox1_Click = True ThenRange(C26, C27, D26, D27).Cells.Hidden = False ElseRange(C26, C27, D26, D27).Cells.Hidden = True End IfEnd Sub
Is there a way to link to cells so that one cell is populated with the others value if a checkbox is set to true?
In other words if the checkbox isnt selected the two cells would be as normal. If checkbox is checked, I want to enter data in one cell and have it show up in the other. I cannot use formulas as I use spin buttons on these cells as well.
I can get it to work with a worksheet change event but that is proving to be more buggy than anything I have ever seen. The worksheet change event is set to one cell but it the code executes if any cell is changed. This has happened before so Id rather not use the worksheet change event if possible.
code to write down to make a checkbox hide the text in a few cells..
What I want is..
When I tick the check box I want it to show me the text in the cells (C11:D11 to be specific) and then when I untick it I want it to hide the text so all you can see is the background colour.
I have a calendar in the sheet attached. If there are leave dates that are marked in red, can I create a formula to count the number of leave dates for the entire year ?
inputting a formula that will look at the dates in row 2 on the tab marked “Cap Plan” on the sample sheet and then go to tab “Monthly Mode” and match the date and then take the percentages for each work type on that tab and put them in the percentage columns in the tab “Cap Plan” for the correct date. This document will be a rolling 52 week one so a simple = formula will not work.
I have this excel workbook that when i tick the first sheet ("251" in temp), it copys the row onto the second sheet ("order" in temp). This all work wonderful, but now if i would like to add additional pages to this excel workbook and have it do the same thing (by same thing i mean adding additional sheets but keep only one "order" worksheet and have all the information go onto the order worksheet.
So for example:
I would add an additional page name 252 into the workbook, it would look and function just like the 251. So after all the ticking on 251, all the information would go onto the "order", i can then move onto 252 and do the same type of ticking of information and those information would also continue onto "order". And i can keep adding multiple worksheet onto the workbook and do the same exact thing.
I have a spreadsheet that keeps track of my travel. Column A has the date I arrived somewhere, and Column B has the date I departed, and Column C has the name of the city I went to.
I am wondering if there is a way to generate a calendar using my list that will mark those dates. For example, a calendar for the month of June 2008 that would show I was traveling from June 3 to June 14, either by marking those dates with a different color or labeling them with the city names, or even just putting an x in the box.
I run a football sweepstake involving 120 people. Each week everyone picks a premiership team who they think is going to win. If that team loses or draws they are out. This carries on until there is one person left and they win the money. I need to make a table with all the premiership teams and names down the x and y axis. When the team is marked as "lose" everyone who has chosen that team gets deleted.
I have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.
The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).
I am trying to build a user form where people are filling in their holiday requests.
I need once the date is choosen to be marked somehow on a calendar in the same workbook. Besides the date, I need in this form, people to upload a file "Holiday Request" which, after the submission of the report to be sent to a specific email, and to be stored as Object in another sheet against the name of the person who is submitting it.
I am using a For Next statement that doesn't return the results for all the rows. The statement is as follows:
For Row = 1 To 100 If ActiveCell.Value = "CHANGE" Then ActiveCell. Offset(0, 2).Range("A1").Select ActiveCell.FormulaR1C1 = "=RIGHT(""0000""&RC[-1],20)" ActiveCell.Offset(1, 0).Range("A1").Select ElseIf ActiveCell.Value <> "CHANGE" Then ActiveCell.Offset(1, 0).Range("A1").Select Else: Range("A1").Select Exit For
End If Next
Range("A1").Select
I hope I did that according to the rules. It only returns the result in the first cell that does have a value of "CHANGE". It seem to be going through the entire range of cells, but I'm not getting any results.
I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB: Private Sub addBtn_Click() Dim y As Integer y = findFunc("end") // Y define where To insert the New row Cells(y, 11).EntireRow.Insert Cells(8, 11).Copy
by using radio buttons or checkbox, or anything similar. Based on the answer, I would like cells to appear below the question, for example in B3 another question would be "how many guests would you like to invite?" and B2 would be their answer but is preset to "0"
How can i hide and unhide one checkbox using another one? Can it be done using IF formula? And also i am using this checkbox to function something else as TRUE/FALSE.
Automatically copy rows to new sheet in excel when column marked with an X. If a column is marked with an X, I need to copy this row to a new sheet. So if a column C is marked with X, I need to copy this row in Sheet2 , if a column D is marked with and X, I need to copy this row below next empty row in Sheet2, if a column E is marked with X, I need to copy this row in next empty row in Sheet2.
I have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
If a column is marked with an X I need to copy this row to a new sheet. So if a column C is marked with and X I need to copy this row to Sheet2 , if a column D is marked with and X I need to copy this row to Sheet3 and if a column E is marked with and X I need to copy this row to Sheet4., Please can someone help with the VBA code to make this work?
