Is there a way to fix the the allowed numberformats in a workbook?
For example, is it possible to have only the following numberformats in a file : 0.0, 0.0% and $0.0. The file should not allow any other formats, and the user should not be able to create custom formats.
I understand that exceeding approx 4000 cell formats causes the message "Too many cell formats" and a freezing up Excel.
Are there versions of Excel (2007?, Vista?) for Windows XP that significantly increase the 4000 limit, and if there are, what are their limits on the number of cell formats?
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
I have used the formatting option to make numbers that once appeared as 1, 2, 3, 13, etc into four digit numbers, to appear as 3001, 3002, etc.
The problem is, I have other data whose original values are the 3000 formats, and Excel won't combine the two or sort them correctly.
Is there a way I can format it to change the actual value?
I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.
View 4 Replies View RelatedCode be to change the number format of the range H21:H37 on my attached spreadsheet from the currency format with the $ symbol and 2 decimal places whenever cell H20 displays "UNIT PRICE" to a number utilizing the comma seperator and with no decimal places and no $ symbol whenever the cell H20 displays "SHIPPED"?
I have a simple piece of code that gets a date from the user via inputbox in the format dd/mm/yyyy. That date is then put into a cell (e.g. B1), and the cells to its right get given something like "=B1+2", "=C1+5" etc so that they will all show a date a few days further down the track. (Much more complicated than that but this is the basic idea)
The entire row is then formatted "ddd dd mmm yyyy" with the intention of every column getting a header like "Wed 27 Jan 2010". This works perfectly for every column EXCEPT B which shows the the string "27/01/2010" it was given from the inputbox.
Right clicking --> Format Cells shows every cell in the row has the exact same 'Custom' format, and the 'Sample' at the top looks correct for every cell except B1.
BEFORE the macro runs and formats all the cells, but AFTER they have been given their formulas, they all display something along the lines of '40021', '40023' which I assume is the number of days since Jan 01 1900 or the like - proving that THEY knew the value in B1 was a date, so why doesnt B1 itself know?
I'm new to VBA so my i'm having alot of problems figuring out simple stuff.
Below is my script and when i run it, the dates turns out incorrect.
I noticed this only happens to dates that are before 13th of each month.
Example,
1st May 2008 ( 01/05/2008)
will turn up as
5th Jan 2008 ( 05/01/2008)
However when i manually open the file Todays trades.csv
The date looks just fine.
Is there some problem with using VBA to call up the file?
I need to create some custom number formats, but after a few failed tests I
have a lot of junk that I'd like to remove. I've checked the Microsoft
knowledge base which suggest there should be a delete button, but I don't
have one.
I want to display a cell with the value -1,000 as Negative 1,000. In custom number formats I have selected #;"negative" #,###;; which does indeed display the cell with -1000 as Negative 1,000, however, I want to be able to link to this cell and have the link pull in Negative $1,000.
View 9 Replies View RelatedI am running a macro that contains a template of a report and when it is done adding data to that report, it copies it to a new tab in the workbook, gives it a new name, and repeats with the next set of data in the blank template, over and over again.
Things work well, the code is correct. But I get to a point where the "Copy Sheet to new tab at end of workbook" doesn't work anymore. The copy sheet to new tab doesn't even work outside of the macro. Is there a limit on the number of sheets (it stuck at sheet 27) or a limit to the characters used across all sheets (my tab names can be long)?
The code I am using to copy to a new sheet is pretty simple:
Sheets("REPORT").Select
Sheets("REPORT").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = SheetName & " Data Report"
Thanks for some clarifications about this problem. I am wondering if there is a limitation in excel that I don't know about.
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.
If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
I'm trying to limit the controls users have on a specific workbook. Problem is I would like for them to have this workbook open throughout the day and if I look all the controls then they will be locked out of these controls on other seperate workbooks they open. The only reason I ask is because of some awesome code I just found by Dave Hawley. The code is...
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application
. CommandBars(1).Enabled = Not Sh. Name = "Sheet1"
.CommandBars(" Formatting").Enabled = Not Sh.Name = "Sheet1"
.CommandBars("Standard").Enabled = Not Sh.Name = "Sheet1"
.DisplayFormulaBar = Not Sh.Name = "Sheet1"
End With
ActiveWindow.DisplayHeadings = Not Sh.Name = "Sheet1"
End Sub
This works great with one exception. It will only block the menus for one Sheet.
In my workbook I have 8 sheets and would like to apply this to 4 of them. Is there any way to modify code to hide menus on mores than one sheet. Also while I'm in the neighborhood. Is there a way to hide the sheet tabs for this workbook only. So once again if a user opened another workbook they would be there?
I have a macro that selects a range of about 20 columns and fills down. It works fine on my smaller set of data, but when I use one that has about 40,000 rows I get the error:
FillDown Method of Range Class Failed
It fills down about 7,000 rows, then fillsdown the first cell of the next row, but leaves the others blank. The entire range (including the nonfilled cells) is still selected.
I am using a diamond shape that is copied into the active sheet when the user runs a macro. The user then selects the shape and enters some text. I want to limit the characters to a certain number or at least be able to give the user some advice when they enter too many characters.
I have found that, as long as I have manually named the shape "Diamond1", I can get the count in my immediate window with the following:
?ActiveSheet.Shapes("Diamond1").TextFrame.Characters.Count
but I want the count to relate to the active shape. So I tried:
?selection.shapeRange.TextFrame.Characters.Count
and I get a "runtime error 438 Object doesn't support this property or method".
How should I count the characters in the selected shape, and then if it is over say 12, require the user to shorten it or at least present yield a message Box?
I want to automatically prevent users from entering more than two digits into a textbox.
i dont want to show a message box or anything like that. I want it to be physically imposible for them to enter more than two digits i.e. when they enter two digits, pressing any other keys on the keyboard should have no effect on the contents of the textbox.
I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.
I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:
* Maintain tab names
* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)
* Keep the file name the same except adding "- FINAL" to the end
Conditional Formating Text equaling to Number formats:
Example:
C20 is '436.59 (its pasted as text often is must remain as text) and D20 is 436.9535
I want D20 to have only 2 decimals and turn green if it equals C20. And turn red if it doesnt equal C20.
I am attempting to copy some numbers from one spreadsheet to another including the formats. The format I am using is a user-defined one which doesn't normally appear in the list of personalised formats.
My code seems to work fine within the same spreadsheet but fails when I do it using 2 spreadsheets.
How do I limit the number of digits to the right of the decimal in a calculation?
View 3 Replies View RelatedI'm trying to create a gradesheet in which any score that falls into the
failing range will automatically highlight in red...
I have a macro that runs every 15 minutes and stores data into a table. This table is attached to a chart and auto updates with the new data every 15minutes. The table will house historical data over time, however I only want the chart to reflect the last 96 entries (15min data points over 24hours). So, once i have enough data my table will be A2:A97. When A98 is populated, i want the data in A2 to be removed from the chart only.
Here is a breakdown:
A2:A97 is currently displayed on the chart.
I enter new data into A98.
The chart will now display A3:A98.
I enter new data into A99.
The chart will now display A4:A99.
I enter new data into A100.
The chart will now display A5:A100.
and so on.
How I can accomplish this? Does this need to be done in VBA?
trying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I'm making a line diagram, showing how a value changes over time. My data is measured every 30 min, which results in a mess when it's plotted over a month.
How do I limit the number of times shown on the x-axis, for instance so that only the date (every 24 hours) is shown?
I wish to limit the number of text characters in a cell and have excel prevent the entry of additional characters after limit is reached. I have tried the Data Validation but it does not preven the entry of additional characters. I want to be able to show the error immediately when the limit is reached and no additional characters are permitted.
View 3 Replies View Relatedcontent from form is captured via formulas to a CIMLoad format on another tab
Users keep entering four to six decimal places in weight value
I need to limit the cell to either whole numbers, or a maximum of 2 decimal places
The cell is formatted to two decimal places, but when you activate the cell it shows the full value they entered, and so does my CIMLoad
How to I limit the value in this cell to whole numbers or numbers with a maximum of two decimal places?
I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:
=PROB($C$11:$C$71,$L$11:$L$71,G11,H11)
I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
View 1 Replies View RelatedIn the toolbar there's a section titled "Number" which allows you to change cell formats with selections named General, Number, Accounting, Date, Time, etc. I want to permanently change some of their default formats.
For instance, currently when I select Number the default is -4000.00. I want to PERMANENTLY change its default to (4,000.00). How do I do this?
When you want to use Data Validation to limit the entry of the number of characters to a cell does this apply only to the cell that you are entering the characters or can it also apply to a cell that contains a LEN(A1) formula, for instance? Also,does the Data Validation limitation function includes spaces as well? Will the message appear while you are entering the characters (when it has reached the limit) or will it wait until you have hit Enter?
View 1 Replies View Related