Rewrite Line Of Code To Prevent Changing Number Format
Aug 5, 2007
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I'm trying to search for a column by looking for a specific column header and then format the entire column in the specific number format desired, but I keep getting an error message with this line.
Code: With Rows("1:1") .Find(what:="Go Live Date").Column.NumberFormat = "m/d/yyyy" End With
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.
I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"
The following is not working for me:
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim DistractorText As String Select Case Target.Address Case "$B$23" If Target.Value = "Distractor" Then DistractorText = InputBox("Type of Distractor:") Target.NumberFormat = "@" & " - " & DistractorText
There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.
This should be pretty simple but I don't know how to do it. When I run the macro recorder, it doesnt record the change. I just want to change the number format of the chart labels from currency to a normal number and visa-versa. Why doesnt this work?
If I change the number of A2 to 0 decimals places, the value shown will be 2 (which is correct, of course), but the value of A3 becomes 20. I would like for this to stay at 15 because the correct value of A2 is 1.5, not the rounded value of 2.
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
Is it possible to display both the current code line number and the total line count of a macro while it's running?
I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
I want to allow users to select a cell in a column, but not allow them to change the contents of the cell. Also, don't want to allow them to select all the cells on the sheet.
I'm guessing that I need to use the Change event, and restore the contents back to what they were before the change.
I have been searching for a way to prevent a user from changing a worksheet name. I ran into some code from the post below (see link) but I find that the code lets the user change the name and save the changed name of a sheet; only when the user selects a different sheet does it change the name back to the set name (sname) from the code.
I'm sure there must be a way to make this happen without deactivating the sheet, but I can't figure it out. Is there some other sort of event that could cause this to happen.
I would like to write a macro that performs the following function:
I have a value in cell A1
In cell A2, I have the formula "=A1"
I want to make it so that cell A2 can never be directly changed; the only way to change the value in A2 is by changing A1. A2 should always equal A1.
I want a message box to pop up and alert the user whenever they attempt to modify A2, instructing them that if they wish to change A2, they have to change A1.
I had written this macro, and it kinda does the trick:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a2") Range("a1") Then MsgBox "text goes here" Range("a2").Value = Range("a1") End If
End Sub
When I attempt to change cell A2, I get the MsgBox popup and it works how I want it to. However, when I alter cell A1, I get the same popup before cell A2 updates (to be precise, the popup occurs after I click another cell). I would like this to be immediate and without a MsgBox.
When I enter this : =now(). it gives the local date and time in my PC in that exact cell. assume it is in (A2). when I enter in Any cell in the spreedsheet the sam formula =now(). then the previous formula (A2) is changed to the new date and time.
I've got an issue where Formulas Referencing Cells on another sheet are getting changed automatically to refer the the moved cell data. The formulas on a sheet (2) refer to specific cells on sheet (1) within my workbook. I update Sheet(1) data with a macro. When sheet (1) updates, the present data gets shifted to the right.
In other words, I need to prevent the formulas from changing to reference the moved data values and keep referencing the initial cell with the new values. xample of change that occurs with update:
I am trying to create a worksheet_change event that will prevent a user from changing or deleting data in the totals column of my worksheet yet allow them to insert rows and delete rows on the worksheet. I have a few issues I can't get resolved. First, if I delete a row and then click the undo message, I get my error message about trying to change the totals. Second, I cannot copy and insert copied rows. I can only insert blank rows. Here's my code and data.
Private Sub Worksheet_Change(ByVal Target As Range) Static lngRow As Long Dim iStrtRow As Integer, iEndRow As Integer, rRowMrkr As Range
[Code]....
Defined range names: "Header" in cell A1 and "Totals" in cell A12
The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to
What I have is basically a form on the main sheet. Here other users can add and/or delete information in a form. I refer to the information in another sheet with formulas.
Now everything was great when using protections/ validations, to the where they fill in the information, but the problem occur when they are allowed to delete and remove rows.
This is something that I want to allow them, even though I do realise that this is something that many advice against it.
To begin with I found the post about changing relative references to absolute references (ConvertFormula). Still the formulas changes when rows are added or deleted and I would have to redo all formulas and run the macro again. I then found posts advicing on the use of the INDIRECT forumla. This formula was exactly what I needed as the formulas would not alter when rows were added/deleted. Now the problem is a combination of the use of INDIRECT and the the ConvertFormula. Now if I write a formula, for example:
=INDIRECT("'Sheet1'A1")
Then when I try to expand the formula over to the rest of the cells, then the references stay pointing at A1, when I want them to continue pointing at A2, A3, etc...
Now what I am looking for is some way to tell all my formulas, after expanding the original formula, that they should all have the INDIRECT function. If it was possible it would be great if you could implement it in the same way as with the absolute reference, for example:
For i = 1 To MyRange.Areas.Count MyRange.Areas(i) = Application.ConvertFormula(MyRange.Areas(i).Formula, xlA1, xlA1, xlIndirect) Next i
Now the above code sample does not work of course, as there should be xlAbsolute instead of xlIndirect, but I hope you understand my intention. Do anyone have the answer to my problem? Is there an easy way to implement the INDIRECT function into a big amount of formulas?
I need in changing the format of sheet2 to exactly like the following image below. *there are some codes in sheet2 which I think is the place to change the format.
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
see the attached "sample.xls". In cell "D1", after I typed in some text and pressed enter, the cell format changed. It only happened for texts not for numbers. There's no macro or conditional format that I could find.
I'm trying to rewrite our commission spreadsheet with a new structure and as my excel knowledge is limited, I'm not really getting anywhere.
I'm looking for a few lines of formula however I'll just post one at a time otherwise I'm in danger of scaring you all off!
Firstly, I am trying to work out the formula for the following:
If the value in C7 is up to £14,999 = 1.5% of the whole value is given, if it's over £15,000 = 3.5% of whole value is given. I would like the total amount of commission to show in F7.
I previously had a query resolved in this forum when I was trying to write data to multiple sheets but the requirements have now changed and I need to update the script.
I have a multi-sheet workbook that calls a routine to rewrite sheet4 based on values in sheet1 each time sheet4 becomes the active sheet. This script makes single line entries or double line entries in sheet4 depending on whether data appears in one or both of a two specific data cells (col:J,K) in sheet1. However I now need to manually input extra data items into blank cells (col:B,C,J) in sheet4 and retain those data items after input, but each time I make sheet4 the active one it clears the contents of all the cells including the data that I have entered into cols B,C,J. Is there a way of fixing cols B,C,J on sheet4 so that they are not cleared when sheet4 is activated. Or is there a way of writing just the the additional lines added to sheet1 since the sheet4 was previously activated rather than clearing all the data lines written previously into sheet4 and rewriting them all from scratch each time the sheet4 becomes the active one.
I think you will need to see the workbook that I am working on to see how the sheets link and a zipped blank version is attached.
