Excel 2010 :: VBA To Start Macro When Changing Data In A Cell
Jul 21, 2014
When a change is made in cell L11, cell N11 should be locked automatically for typing. This is true for the cells between L11:L25, and cells N11:N25.
I have been trying to come up with a code that will:
1 - start my macro when f.ex. L11 is > 0
2 - lock N11 when L11 is filled out
3 - unlock N11 when the input in L11 is deleted
This is my attempt so far to put together a code:
[Code] .....
I'm using Excel 2010.
View 1 Replies
ADVERTISEMENT
Mar 16, 2014
Data validation in an Excel 2010 workbook.
I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.
As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.
But I can enter 1/9/2020 and the Data Validation happily accepts that date.
View 12 Replies
View Related
Mar 6, 2014
I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is < 2.5
Sheet1.Tab.Color = vbRed
Case Is > 2. 5, Is < 4
Sheet1.Tab.Color = vbGreen
End Select
End Sub
The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.
View 10 Replies
View Related
Apr 25, 2014
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
View 1 Replies
View Related
Mar 13, 2012
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
View 2 Replies
View Related
Apr 12, 2012
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
View 3 Replies
View Related
May 1, 2014
I have a large userform created and the save and close function works well, i have read multiple post about searching, updating - however i dont understand the coding (yet), really need a breakdown so i can adjust to suit. My whole file is to large to upload so attached is a small snippet.
What i am after exactly is:
Label 1 = unique number id (eg. Vehicle Rego...) - entered by user
Search = to see if it is used already - if so populates the other cells with data from database, if not it can be used to start a new data entry on a new line in the database
Update = if a search was found can be ammended and "Updated" - overwriting the same line in the database
Save & New = Save new entry and clears for newer entry
Close = closes userform
I have added in a combobox to see any different programming.
I my original file the database stretches from A1:FL1 - hence why only a small file can be attached.
View 3 Replies
View Related
Aug 26, 2012
Is there functionality in Excel 2010 to convert a cell that has last name, first name to first name last name? For instance:
Before: Mouse, MickeyAfter: Mickey Mouse
I'm trying to merge data from 2 sources, and the common data between the two is the name. However, it is formatted differently in each data source.
View 2 Replies
View Related
Mar 5, 2012
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].....
View 1 Replies
View Related
Sep 18, 2012
is there a way to change the legend keys (aesthetically) in 2010?
I created a pie chart using a template but I don't want to keep the defualt legend keys. I tried changing the theme but it appears that the legend keys that come up are the same no matter what theme I change it to.
The Chart is a 3d Pie chart, I think that matters because the regular square legend keys show when the chart isn't 3D.
View 3 Replies
View Related
Jan 23, 2013
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
View 7 Replies
View Related
Mar 6, 2014
I'm using excel 2010, looking to import a .xls file to a tab from a web query. Herein lies the problem: the .xls file is named based on the current date. So for instance, xLfile03.05.14.xls. Which changes the next day to xLfile03.06.14.xls. This is not a local file but one on microsoft sharepoint. It's ONLY available through sharepoint.
View 1 Replies
View Related
Oct 19, 2013
I am working with a 2010 Excel spread sheet and need some direction.
One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.
I need all of the dates to be in the first format (dd/mm/yyyy).
Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?
View 14 Replies
View Related
Sep 5, 2012
When I place a text box in Excel 2010, the default is for everything to be locked (Box is locked AND text is locked). I want to change the default setting so that when I start Excel fresh, anytime I place a text box the "LOCK TEXT" box is UNCHECKED.
There's a place where you can select "Set as default text box") after you make the changes, but it's not sticking (even in the same document without closing. IE, I make the changes to a text box, then click the option that says "Set as default text box", place a NEW text box immediately in the same document but the text is still locked.
View 3 Replies
View Related
Jan 9, 2013
I was recently upgraded from Excel 2003 to 2010. I have some code that creates a 3d clustered bar chart, previously the chart's rotation was 0 and now it seems like Excel wants it to default to 15 degrees. I'd like to change the rotation back to zero, but Excel doesn't seem to see the chart.
I recorded a macro, inserted the relevant recorded code in my routine, but Excel doesn't seem to like it.
' begin code ------------------------------------------------
Dim Rng As Range
Dim cTyp As XlChartType
Set Rng = Range(Cells(2, 1), Cells(11, 2))
cTyp = xl3DBarClustered
Charts.Add
[Code] ....
I'm getting a Visual Basic error that says "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found."
Excel seems to know the chart name, but doesn't seem to think there are any shapes (initially, a loop looking at the names of shapes in the shapes collection returned nothing; printing ActiveSheet.Shapes.Count returns zero.)
What is the correct method of rotating a 3d clustered bar chart?
View 4 Replies
View Related
Jul 5, 2012
I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.
Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q
Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....
Then delete blank rows below Row 5
View 7 Replies
View Related
Apr 9, 2013
I am using excel 2010. I have 3 separate form control each with its own macro. I would like to create a combo box with a drop down with a list of each form control case and be able to perform the same function as the individual form control when a specific name is chosen in the list of the combo box.
View 3 Replies
View Related
Jul 6, 2014
I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010
VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")
[Code]....
The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.
View 3 Replies
View Related
Apr 23, 2012
My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.
I have looked for the Book.xltx file to replace but can't see it any where.
View 5 Replies
View Related
Jun 11, 2013
I have a user here at my company that is having a strange issue with Excel. When she moves a page break in her document, Excel freezes up, then once it finally makes the change (if it doesn't crash), some (but not all) of the images that are in the document resize to super small.
For instance, she may have 50 rows. Each row contains a column with an image, then a few other columns with product information. Changing a page break may cause ten of the images to become tiny for no apparent reason. Resetting page breaks seems to cause the document to explode, with cells being thrown all over the page into different locations and columns becoming uneven.
When I make the same change on the same document on my system (both using similar specs and Office 2010), this does not happen.
View 1 Replies
View Related
Dec 13, 2011
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
View 3 Replies
View Related
Jul 29, 2011
We have just refreshed our entire printer fleet to Xerox printers and as part of the project we have selected all defaults to B&W and duplex.
On a multi sheet excel file, If we click print then choose entire workbook and then change the print options to colour, it prints the only first worksheet in colour, and all the others in mono. i have read that excel treats each worksheet separately and does not cascade the changes made made in the first worksheet even if all sheets have been selected.
How can I get them all to print in colour?
We can set up another printer and change the defauts to colour but we dont want to go down that road and have 2 printers installed for each model.
Is there any code out there can will/ can change the properties of the first worksheet and cascade this throughout the workbook.
We use XP and W7 workstations and office 2003, 2007 and 2010. By the end of the year all workstations will be W7 and Office 2010.
View 2 Replies
View Related
Oct 30, 2013
I am using Excel 2010.
BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500
MainList - F2= (current month)
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
View 2 Replies
View Related
Mar 15, 2012
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
View 2 Replies
View Related
Apr 24, 2014
I'm trying to create a macro that will look at each worksheet in a workbook and then delete the last line of data on each worksheet. The last row can vary on each worksheet. This is what I have come up with but it is not working. I am on Excel 2010 and Windows 7.
Sub Macro1()
Macro1 Macro
Dim ws As Worksheet
[Code]....
View 2 Replies
View Related
Jun 24, 2012
I recorded a macro to save my file as PDF and assigned the macro to a button. Easy. Now I would like to change my macro to pause when the file is saving to allow the user to enter a file name. If that is not possible, I'd like to reference a cell to use as the file name. I have gone through other posts and tried changing my macro, but always get an error. I am using Excel 2010. Following is my code for saving to pdf.
Sub SavePDF()
'
' SavePDF Macro
'
[Code]....
View 9 Replies
View Related
Jul 24, 2014
I need to write an excel macro to trace the valid PO# in my daily report.
Below is the condition:
If the PO# is an empty cell, the result return at column B as Not Valid
If the PO# starts with any character (example: FISHNH123), the result return at column B as Not Valid
If PO# is not starts with 3000 or 490 or 450(example: 3000823041,3000823037), the result return at column B as Not Valid
PO#
Status
empty cell
Not Valid
PO# starts with any character (example: FISHNH123)
Not Valid
PO# is not start with 3000 or 490 or 450
Not Valid
I'm using excel 2010..
View 2 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Apr 16, 2014
Excel VBA 2010.
I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g
I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth
I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.
View 6 Replies
View Related
Sep 11, 2013
Is there a way in excel 2010 to cut out half of the data in a cell .. to make the top four in example to look like the bottom four?
1986 Jun- 9 to Jun-13
1986 Jun- 2 to Jun- 6
1986 May-26 to May-30
1986 May-19 to May-23
1986 May-12
1986 May- 5
1986 Apr-28
1986 Apr-21
View 5 Replies
View Related
