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'd like to create a variable list. If a number is entered into B7 (see attachement), I would like that number of rows being inserted below. Starting at A8. But I would like the list to be variable. So if a lower number is entered, the rows would be deleted. And I would be great if the formulas could be maintained.
I've a protected sheet with one table with 6 columns and 1 row (to be filled), and the column headers.
I want to allow the user that will fill the sheet to insert more rows with a button (+) I want also to allow him to delete the last row with a button (-), except the first row, because the table must have at least one row.
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
Account Number
211-00-5936-00-001-3-00-2-00 Trans. Date Doc. No. Trans. Line Comment Actual Amount
[code]....
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00 Trans. Date Doc. No. Trans. Line Comment Actual Amount
Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.
I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.
There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows() Dim NextRow As Long Dim NrOfCopies As Long Dim i As Long Const NrOfCopiesDefault = 1 Const NrOfCopiesMaximum = 9
I am looking to insert a drop down box in Excel 2010 which will allow users to select multiple items. At the moment I have data validation drop down lists in other columns, however I need one column to allow for multiple selections.
Basically I have a drop down which allows for a region to be selected, my next drop down (in the next column) I need to allow users to either select "ALL", or multiple countries which fall within the region selected previously. I need a check box so that users can select multiple countries, and deselect if an incorrect country is selected in error.
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 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.
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.
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 Hide and Unhide several rows depending on if several checkboxes are marked or not.
[Code] .....
This is the code I am using, which is effective in hiding and unhiding the rows. However, the issue I am having is: I want Row 20 to stay visible if either one of the checkboxes is marked. This string of code runs the "hide" portion if either box is unchecked.
I am trying to have a checkbox that will hide any row between 6 and 300 say IF column P of that row that is empty, or does not contain the letter "Y", whichever is easier. I have found a bunch of different code snippets, but most hide a predetermined row range, and I need this to be dynamic.
If it can also expand the range automatically as I add more rows to the spreadsheet, that would be even better, but not necessarily required.
I've had luck with hiding rows with a checkbox, but I cannot get them to unhide when I uncheck the box.
Here's my scenario - (using Excel 2000) In an estimating spreadsheet I have a print range of A1:N74. What I'd like to do is add a checkbox or button so I can hide and unhide rows based on a null or zero in column range b5:b62. This is for printing purposes, but I can't get a 'beforeprint' event to work either. All I need is box checked and cells hide, box unchecked and cells show (unhide).
Sub CheckBox1_Click() Dim Rng As Range Dim MyCell As Range Set Rng = Range("B5:B62") For Each MyCell In Rng If MyCell.Value = "" Then MyCell.EntireRow.Hidden = True End If Next MyCell End Sub
I need to copy all the cells to a "new worksheet" if the checkboxes are checked. I have thousand of cell in here but i just narrow it down in this example.
example: A B C D E F G 21 22 23 24 25 26 checked 31 32 33 34 35 36 unchecked 41 42 43 44 45 46 checked 51 52 53 54 55 56 unchecked
there is a command button in here that when it was click it will open a "new worksheet", what I need to see in the new worksheet is this:
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?
I have a multipage wizard like userform that for each page has check box selection choices a user can pick from. The sample I've attached uses animal attributes for 2 animals. What I'd like to have is code that populates the worksheet with the animal under consideration (determined by the Frame Caption) in say A5, then populates the attribute selections made starting in B5 with no blank rows in between if they chose not to mark a checkbox.
So for animal one on page 1 of the multipage form, if the user selected attributes 1, 3, 4, and 6 and clicked the "NEXT" button on that page, animal one appears in A5 and then those attribute selections would be in B5, B6, B7, and B8. If the user then selected for animal 2 on page 2 of the multipage form, attributes 2 and 6 and clicked the "NEXT" button on that page, then animal two would appear in A9 with the selections appearing in B9 and B10.
I should mention that the number of rows in Table 1 may grow (or shrink) row size with additional rows added (or deleted) by the user. Not sure if it matters.
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).
The checkboxes go from D5:D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].
Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).
I did not know how to do it any cleaner, so I created 142 of these:
Code: Private Sub CheckBox_D5_Change() Dim i As Long For i = 5 To i + 30
[Code]....
It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1 Price 2 Price 3FruitDeliciousPearStore 1 Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
