I know Excel is supposed to guess if there is a heading row or not when you're manually sorting a group of columns. But, more often than not it seems, it guesses incorrectly, forcing me to go through the multiple steps of the "Custom Sort" menu. Most of the manual sorting I do does have a Header row.
Is there a default setting somewhere to force the sort to always assume there is a Header row? I can't find any.
how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?
View 4 Replies View RelatedI am working on lots of excels, with data queries from sql and pivot tables. Is there a way to set defaults to some settings in excel (not in this specific file).
To be more specific:
1. Import data from SQL: Refresh on opening, save password
2. Formatting of number to have two digits and a comma separator
3. Pivot table: when adding to values sum instead of count, refresh when opening the file
How can I change the default settings I get with a new Excel document?
What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.
I am not able to do these changes using settings in Option menu.
We have printer mailboxes setup for all of the employees and sometimes the print settings get saved in the excel files. This means that when another employee uses the file their prints get sent to the wrong mailbox. We have hundreds of files like this and it causes all kinds of problems with prints going to wrong mailboxes!
Obviously their default print settings are all set to their own mailboxes.
Is there some way, using vba, to get an excel file to go back to using the user's default printer settings instead of the ones saved in it's file? (or get it to remove the printer settings saved in it's file). That way I could just make an application level open event to fix the problem! Either that or some way, in vba, of changing a user's storage box number for a specific printer.
I am creating a spreadsheet on a British-based system using the d/m/y date system as default, but I am unfortunately entering data using the american m/d/y system and would like to change the default to the american system.
View 5 Replies View RelatedWhen 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.
how to change the default settings for shapes in Excel 2007? I select a line tool and it is a blue color and I would like to have its default as black
View 6 Replies View RelatedMy 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.
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
I've got a few worksheets that have staff names on Col A. And these names are under sub headings of what position they work in (Foremen, Operatives etc..).
Before I had access to them, the names were not in order, now I've change it to A-Z, for each staff under their position.
I know you can make your own custom order list, so the list goes top to bottom : Supervisor, Formen, Operative, Office.
What I sometimes need to do is sort Col B or C. But is am unable to sort it back to it's original state
Attached is an example of how I would like my spreadsheets to look. But is there a way to use Sort & Filter so it sorts the entire table by position, keeping the correct name under the correct position heading.
New Microsoft Excel Worksheet (2).xlsx‎
I have hundreds of rows of data which I need to sort into headings in a new tab.
E.g Column D has about 40 names which occur many times. Column G has 4 digit numbers which are unique.
I want to create in a new tab a column for each of these 40 names, using that name as the heading and then list the 4 digit numbers unders that heading. So if the name name "John" appears 20 times in Column D, it will get a heading in the new sheet and there will be 20 unique numbers listed below it from the data in Column G.
I'm trying to change the default option for the data series when graphing in Excel 2003 .... I am trying to create a line graph
Excel, by default, is putting static pressure on the x axis and the air flow on the y axis .... I need it to be the other way around
I have found out that:
By default, Excel plots whatever you have fewer of—rows or columns—as the data series
apparently it is possible to change this Excel default option, but I can't figure that out
in step 2 of the Chart Wizard, I have tried many different things, but can't get Excel to chart the Air Flow CFM on the x axis, and have static pressure values on the y axis
When i record a macro with an autofilter, the default setting is always "equals". Is there any VBA code or anything to be able to change so that my macro will search in a "contains" setting.
View 9 Replies View RelatedI am working with a spreadsheet created by someone else. I am trying to change a date in the spreadsheet from "12/19/07" to "5/1/08". When I type in the new date, excel assumes that I am entering a formula, places an "=" in front and displays 1/1/1900.
I can open a blank spreadsheet, type in the date "5/1/08" and copy it into the cell in the existing spreadsheet and it reads it as a date just fine.
The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...
View 9 Replies View RelatedExcel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.
View 3 Replies View RelatedI have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Excel 2013
i made XL App settings in a way suits my needs...........
Is there a code to restore these settings when i reinstall the XL App ?
Are there any settings that can be applied to a message box? ie changing the background colour or header colour etc.
View 2 Replies View RelatedI have developed a process design application in Excel using User Forms to input data. The data are placed on a sheet of a workbook with the majority of the calculations being done by cell-to-cell calculations so that users can view the formulas if they so desire. This application works well in English. We want to use the same application in our German office where it has not worked well.
In order to trouble shoot the problems, I can go to the Region and Language setting under Control Panel and set it to German (Germany). This converts the number format to a period for the thousands separator and a comma for the decimal. This seems to create a lot of problems for VBA that I have not been able to sort out.
To simplify the problem I have written a very simple program. It has one User Form with two text boxes for number input, an Enter command button and a Close command button. Sheet 1 has a command button that opens the User Form. In the User Form, I enter a number in both text boxes and click Enter. VBA code then enters the number from Text Box 1 in Cell A1 and the number from Text Box 2 in Cell A2. Cell A3 has an equation that calculates the sum of A1 and A2. All cells are formatted as General.
The workbook works as expected in English. When I set my PC to German (Germany) the application works as expected when I enter an integer in the text boxes. However, if I enter a decimal such as, in the German format, 10,5 and 5,5, the numbers entered into the spreadsheet are text (left justified) and the sum is zero. I have to enter 10.5 and 5.5 to get the numbers to enter as actual numbers and give the correct sum.
How to make VBA work with the German settings?
I have a large file of data and the data looks like this repetitively, however, there are also useless data. But I figure out that the repeat data looks the same compare to the useless data. I need to extract the data that I only need. Is there a way to set a macro to search for a String from the beginning of the excel file, when the string is found, it will keep the 2 rows above and 56 row below it, then loop.
Here is an example of it:
A1 USELESS DATA
A2 USELESS DATA
A3 USELESS DATA
A4 USELESS DATA
A5 GOOD DATA
[Code] ....
The macro will start from A1 then go down till find STRING, then will keep 2 row above it which is A5+56 row below it which is A63.
Then continue at A64, then find STRING at A66 and do the same which is copy A64 (2 row above)+till A122 (which is 56 row below), then continue.
Since A123 to A155 does have any STRING, they will all be deleted..
I change the international setting by using the API call shown below. The settings change, but is not activated before I exit Excel and go in again.
View 7 Replies View RelatedEnable Macros to open it. No one else does.
Second, all the rest of us can click the print button to print the one single page that we are viewing. He says when he does this, that each time it automatically starts printing all the pages in the workbook. He wants to only print the one page.
Is there a way to force a print RANGE, and add settings such as margins, and print to range to 1 page wide and 1 page tall? Can this be set with VBA? I tried to make a marcro but it crashes. This is for Excel 2002.
View 9 Replies View RelatedIs there an easy way to print out the Red Green and Blue settings (i.e. numerical values) for the current pallette in a workbook?
I can set them fine and transfer them between workbooks but that's not quite what I need.
In microsoft word you can clear find criteria like below.
With Selection.Find
.clearformatting
.Replacement.clearformatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.
Define printerdrivers per tray.
This last I did, so there are printerdrivers for tray3 and tray4.
With the following code I try to print the workbook:
Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter
Application.ActivePrinter = strP1
firstPage.PrintOut
The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.
Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.
Is there way we can save the layout and other preferences / settings (and there are a huge no of those in excel) to a file or can we copy out the file where excel saves this data ? Probably a User Interface folder or something ? This is so that when i do a fresh reinstall of MS OFFICE i dont want to spend so much time resetting the customizations ive created over time.
View 5 Replies View RelatedI'm attempting to develop an application that will get world-wide usage, but my first tester is in the UK (Danish) and the app Kacked on a simple time format command.
Since this is intended as a self contained project, is it possible to establish those settings as part of my project?