Stop User Changing Rows
Jan 19, 2007Is there a general way to disable a user from changing row size or adding new rows.
View 7 Replies
ADVERTISEMENT
Stop Procedure Window Changing When Changing Sheets
Sep 9, 2007Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
View 4 Replies View RelatedHow To Stop Random Numbers Changing
Mar 1, 2012I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.
But the two columns are not the same? I want to copy the same random numbers into the other sheet.
Code:
'Generate random numbers
Lastrow1 = Range("c65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=RAND()"
Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"
'Finding a specific row number and copying random numbers
[Code] ........
Stop #REF! Occurring When Changing Spreadsheet
Jun 3, 2008Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).
View 9 Replies View RelatedHow To Stop The User Selecting Entire Row
Sep 18, 2008I want to stop the user selecting an entire row by clicking on the row header.
How can I do this?
Stop User Typing In A Combobox
May 11, 2007how to write VBA to stop user typing in a combobox but only choose from the dropdown list?
SO that to avoice any device I/O error.
How To Stop Linked Cell Destination From Changing
Oct 30, 2013This is the current layout I have:
Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
Stop Macro From Changing Range In Formula
Jul 6, 2006If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.
i.e.
Cell A1 has formula = sumproduct(e1:e10>10)
Currently cells E1:E10 are empty
Run the macro
E1:E10 now has data in.
But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)
Changing Calendar Start And Stop Dates
Jun 20, 2007The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?
View 9 Replies View RelatedStop European Date Format Changing
Jan 21, 2008I have a userform which is used to update a spreadsheet.
There are 7 textboxes which add dates to the spreadsheet. These dates are then used in formulas.
Every time a record is saved the date format changes.
Ths means that if the date is entered dd/mm/yyyy it writes mm/dd/yyyy and this is fine. When this record is next retrieved it will show mm/dd/yyyy and any new date will be dd/mm/yyyy.
Wen saved to the spreadsheet both formats change meaning the dates in the spreadsheet appear in diffrent formats messing up all formulas.
How can I stop the date format changing each time the record is saved?
Data Validation: How Can I Stop The User From Moving On?
Apr 3, 2009I have attached a sheet that I'm having an issue with. Columns D and E on the Dim Calculator sheet have a data validation formula in the cell. It works in the sense that it pops up if they are outside the parameters that I want them to hit. However, if the user clicks the Cancel button they can just keep going on even though the entry in the cell is aganist the validation.
Example: Columns D and E cannot be larger than Column C. Currently now if Columns D or E are larger than Column C the validation error pops up. But I can just click on cancel and keep going. That is what I want to prevent.
Is there a way to grey out the cancel button?
Should I do something else within the validation itself?
Stop Excel From Changing Numbers To Dates In CSV File?
Jun 6, 2005When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
Stop Cell Reference Changing When Insert Column
Jun 4, 2009I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?
View 2 Replies View RelatedHow Do You Stop Changing The Date Format During Find And Replace?
Aug 26, 2009When I remove "Release date:" from "Release date:24 December 2008," excel will automatically change the text to "24-Dec-08." Yes, I did try to format the column to "text" before using find and replace.
This wouldn't be a problem, except that half the dates are in Dutch, so only half the dates are changed, which means that I can't make all date formats in a column uniform.
Stop Users Changing Computers Time/Date
Aug 31, 2006I am creating time sheet application , obviously i need to store the time when the user logs & logs out .. The issue is, the user can "Cheat" by changing the system time ...
Any alternatives?? Is it possible to store the time from a particular server etc or some other source.
Stop Formula Cell Reference Changing When Copying
Nov 1, 2006I am trying to cut/paste a row of formulas in my spreadsheet. The problem is that I want some of the referenced cells in the formula to stay CONSTANT and not increment on each new row. In this example, I want the references to row 17 (F17,E17) to increment (E18,E19,...), but I want the "G7" reference to remain hard coded (as G7) on each row I paste (because that is where my master value is). (The formula is working fine) =IF((F17>0),IF((F17<=G7),E17,0),0)
View 3 Replies View RelatedCode To Stop A User Adding Or Deleting Sheets
Aug 31, 2007I need to stop users from adding and deleting sheets in a workbook
My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.
But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?
I also wasnt sure if there were short cut keys to add or delete sheets.?
Some of my users use excel alot so i want to account for an tips they know that i might not.
Template Excel File - Stop User From Saving
Oct 3, 2008I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
Excel 2007 :: Stop Links Changing When Use SaveAs To Create Backup?
Sep 25, 2013I have two spreadsheets that are linked in Excel 2007. They both hold very different information about the same projects, so each row shares some information. However, they are both very large and I do not want to combine them in case the whole spreadsheet becomes unusable. I'm not allowed to use a database
When I save Spreadsheet 1 in a different location, so that I have a backup of the precious data, the link in Spreadsheet 2 changes to show that new location. I want the link to stay at the old location.
Is there a way to stop the link changing when I use SaveAs, so that when I open Spreadsheet 2 it refers to the original location of Spreadsheet 1?
Stop Formula Changing Cell Reference But Change Sheet Number When Dragged?
Feb 8, 2010need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies View RelatedRestrict User Changing Data
May 19, 2007A Macro needed which will be assigned to a button,say “Check” and when the same will be clicked all the sheets will be say protected an users cannot modify any data. Another button will be there which will be assigned macro & when clicked will ask for a password to be known by only some administratives say departmental heads only and they can modify the data.Users after clicking the “Check” button,cannot modify the data.
Restrict The User From Changing Or Inserting A Value In A Particular Column
Aug 16, 2007I m just looking out for some code where i can restrict the user from changing or inserting a value in a particular column in excel
View 9 Replies View RelatedVBA Code Prevent A User From Changing Cell Contents
May 26, 2009Is 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?
Changing Auto Filter Based On User Inputted Cell
Apr 7, 2007I have a Workbook that has 2 worksheets, one called CALL QUERY and one called CALL LOG. On the CALL QUERY sheet, cell D9 is a user inputted cell with the cell validation set to list. The user uses the dripdown list to pick an office identifier (3-Letter Code).
On the CALL LOG sheet, I have info about individual calls. Column E has the 3-letter office identifier for each call.
I am trying to find a way to automatically auto filter the list on the CALL LOG sheet with the user input in cell D9 on the CALL QUERY sheet.
I was given the following (N2 is a cell on the CALL LOG sheet that equals the user inputted cell D9 on the CALL QUERY sheet)
Changing The Color Of A Command Button :: To User Define Colors
Jan 29, 2009so I know how to change the color of a command button(right click, properties), but is there a way to change the color to a specific red green blue range? lets say 102 28 02 for example
View 2 Replies View RelatedStop Graph From Changing Named Range To Formula Range
Sep 8, 2006I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
Stop Formula Column Reference Changing On Insert But Not Row Reference
Mar 6, 2008A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
Stop Macro From Changing Onto Sheet Top Sheet And Flickering
Dec 24, 2008How can I stop my macro from changing onto sheet top sheet and flickering away like mad??
View 2 Replies View RelatedHighlighting More Rows Than I Want To - How To Stop Please
Nov 28, 2009I am using a 2007 excel spreadsheet and on parts of it I can't just highlight one horizontal row of cells, it wants to highlight three down. I suppose that I have asked it to do this but I can't for the life of me undo it.
I have tried unmerging cells, cutting and pasting somewhere else, but nothing works.
Can anyone help me please as it's driving me to distraction?
Formulas Every X Rows With A Stop
Mar 23, 2007I have this macro that adds whatever formula that is in L1 to the same cell down 66 rows. But it doesn't work like the way I want it to because it's stopping when it sees empty cells...so the problem is that my colum is actually completely empty...so how do I get it to stop when I want to?
Sub every66rows()
Dim i As Integer
i = 1
With Range("L1")
Do Until IsEmpty(.Offset(66 * i))
.Copy .Offset(66 * i)
i = i + 1
Loop
End With
End Sub