I have placed ActiveX checkboxes on my worksheet. I would like to have some code that would uncheck them all at once, rather than some really long code unchecking each one individually (which is what I currently have). Is there code for this?
View 3 RepliesCode:
ColAColBColCColD
R1 checkbox1
R2 checkbox2
R3 checkbox3
R4 checkbox4
R5 checkbox5
R6 checkbox6
R7
R8
R9
As soon as i mark checkbox1, i want to send the checkbox Caption to A1. Then if i choose checkbox5 and mark it, i want to send the checkbox Caption to A2, which is in this case next cell available on colA.
How could i do thin on excel 2010?
Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)
I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)
(if quarter 1 clicked than all columns are visible if unclicked hidden).
The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.
Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1
[Code] ...........
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), "")
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell)
( logical test ) ( value if true ) ( value if false )
Using the code below I can't clear the clipboard, the range still selected after copy and paste. Obviously, "Application.CutCopyMode = False" failed. Is there a way to deselect/clear? Using Excel 2010.
View 4 Replies View RelatedI am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
I am trying to clear check boxes and list boxes on multiple worksheets within a workbook. Below is what I have,but doesn't work. Note: the "bottom " code does work to clear selected cells of their data. I tried to create a macro for the list boxes selecting "----" which I have as the last entry in the vlookup table but was totally unsuccessful at.
Sub cleardata()
Dim Obj As OLEObject
Dim ws As Worksheet
[Code]....
I have a workbook with 100 tabs and I have an index page with checkboxes for each tab. I need a macro that will clear all of the checkboxes on the page with one button and a separate macro that will print only the checked tabs.
Can anyone write that code for me so I can just add it into my index tab?
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I'm having issues with some coding to work in excel 2003, 2007 & 2010 which will ensure that if cell E3 is changed to anything except "pool_car", then cell D12 is changed to 0.00.
So if a user originally selects "pool_car" in E3 and then enters a figure in D12 (which they are allowed to do using data validation), if they then choose to change the car type in E3, that D12 is then cleared of the figure they entered for the "pool_car".
I'm already using a similar code to clear other cells if another changes but am uncertain of who to change this coding or write other coding to suit.
Well to start off I am using Excel 2007, I have an invoice template set up across two sheets (Invoice & Price Table), on the first page is the actual invoice that needs to be filled in. I have made an userform for being able to fill in the invoice number,what type of test was done, and also things like hours and miles (an their costs per also)
. Now I am wanting to try and get it to where I can have the form look at the type of test cell and from there select the price located on the second sheet that is appropriate to that type, the thing is, there are two possible prices that it can be depending on if the company wanted the results "rush". I was suggested that I could use a check box on the invoice to indicate in the spreadsheet that it is a rush, and somehow link that in with a vlookup to be able to get what I wanted, but I'm not sure exactly how I would go about doing that.
I was viewing this thread here: [URL] ...
How to add checkboxes and radio buttons.
Checkbox.JPG
So I have an 2 ActiveX button and 2 ActiveX checkboxes. I have it set up so that the Split button will enable when both checkboxes are checked. The ClearAll button has a code/macro in it that will check the "Cleared All" checkbox when the Clearall button is pressed.
My problem is with the "Populated Data" checkbox. I want it to check/uncheck itself if there is anything in Range("A1") of a tab called "Data".
The names of these buttons/checkboxes are
"Cleared All" = ClearCheckBox
"Populated Data" = DataCheckBox
"Split" = SplitButton
I have a spread sheet whereby it contains:
- 12 unique values in column 6 which represent states within the country.
- 7 unique values in column 25 which represent the types of services.
- 5 unique values in column 27 which represent the days for doing the services.
I also have a user form that contains CheckBoxes equivalent to all above unique values, and I want the CheckBoxes to do the duty of a filter, whereby after selecting the needed CheckBoxes and clicking on a seperate botton "creating report", I want to show on the spread sheet only the rows of the values being checked and all other rows which don't contain the needed values should be either hidden or deleted.
I'm using Microsoft 2007 on Windows 7.
How I can merge multiple word documents based on checkboxes selected on Excel.
Specifically, I have some 75 checkboxes found on Column F with linked cells on Column G (for example, checkbox1 place on Cell F1 linked to Cell G1). Each of the checkboxes have a corresponding Word document (containing text and tables about 1 to 4 pages in length). Based on what checkboxes are selected, I'd like to have those corresponding Word documents merge together and create an entirely new Word file (for sake of order, the datapoints from each selected Word document should be placed at the beginning of a new page in the new file).
The user will eventually name and save the new file on their own.
I'm not familiar with the required code as well as where it should be placed (whether on the excel file that contains the checkboxes, on the individual word documents that are eligible to be merged, or parsed out on all files).
Using Excel 2007, I have a workbook with 7 sheets. The first one is a Navigation Page where I have checkboxes (form controls, not active X) with the names of the other 6 sheets. When the box(es) are checked, the sheet(s) become visible. I have accomplished this by assigning macros I recorded.
I need to now add the opposite: When the box(es) are unchecked, the sheets become hidden. From googling and looking for other threads/forums here, I gather that I need to add code/ VBA, but I know nothing about these at all.
I am using Excel 2013. I have an autofilter turned on for the columns in the spreadsheet. When I click on the filter button at the top of a column, the filter values do not have any check boxes next to them so I can't check any values. how to fix it so that it has checkboxes? I've attached a screen shot.
View 2 Replies View RelatedI am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedI keep encountering an error while creating multiple sheets within the same workbook. This is a known error by Microsoft as documented here. In order to avoid using multiple workbooks i need to clear excels cache each time I run the macro as i have limited users sheet creation to 30 sheets which should not trigger the error. Does any one know a code to clear excels cache or memory?
I have used cutcopymode = false but that does not do the trick.
I'm using Excel 2011 for MAC.
What I need is to clear and lock some cells based on a drop down list selection.
The case is like this:
In Cell [E14] is a drop down list : 0,1,2 or 3
Cells: (D19:E19), (D21:E21), (D23,E23) Merged are PART DESCRIPTION
Cells: [G19], [G21], [G23] are COST
So I need when 0 is selected in the dropdown list, all of the above cells are cleared and no input is permitted, Cells locked.
If 1 is selected, just the first row is allowed for data entry and the other 2 are cleared and locked as this values populate another worksheet for calculations.
So far I did this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [E14] = "0" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("D19:E19").Locked = True
ActiveSheet.Range("G19").Locked = True
ActiveSheet.Range("D21:E21").Locked = True
ActiveSheet.Range("G21").Locked = True
ActiveSheet.Range("D23:E23").Locked = True
[code]...
It works fine to lock the cells, but I can not fined the way to clear the values previously stored before locking the cells.
How do i deselect grid-lines in Excel 2003 so as the sheet is clear.
View 6 Replies View RelatedI've have a tool/code that takes the screen prints of the active window (when I press F9) and pastes it in a word document.
But whenever I do copy some text or any other image and when I press F9 to take the screen print of the active window. I get the text pasted into the word, this is because the clipboard content has text first and then the screen print of the active window.
Instead When I press F9, I wanted the below to happen
1. Content from the Clipboard should be cleared
2. Take the screen print
3. Paste it in the word document.
Note: My tool/code already does 2 and 3. Looking for 1 alone, ,
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.
here's atleast the vb for the macros:
VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select
[Code].....
keep getting "The formula you typed contains an error".
To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.
3. If we want his age in years and months, then we can use a formula like this one:
=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”
The output of this formula will be as follows:
40 years and 2 months.
I change A1 to G3 because that is where I the birth date.
I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.
The layout is similar to this :
H1, H2, H3, H4, H5, H6
A4, A3, dummy, Dummy, ~,
A4, A3, dummy, "Dummy ,Dummy", ,
A4, A3, dummy, Dummy, +,
A4, A3, dummy, Dummy, -,
A4, A3, dummy, Dummy, "(5 + 4) /4",
Consider that the formula (5 + 4) /4 has a CR and LF between (5+4) and /4
I've tried a few methods to import the files:
Reading the CSV file as a Text File a line at a time but with the CR a LF it get's all messed up
[Code] .....
Tried with Using OLEDB but the Sign + , - and ~ are read a Null
[Code] ....
And Finally tried wit th QueryTable but it does not recognize the Double Quote that precedes the comma so
"Dummy ,Dummy" it's seen as two column values instead of one.
[Code] .....
I am trying to use the code that can find the last used row in particular column.
Here is the code in module1
Code:
Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp > temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function
and code in the range
Code:
=LastRow(V12:V300)
My problem is, this code was working nicely but now it only shows ###.
PS I use MS office 2010