Prevent Sorting Of Entire Worksheet
Dec 27, 2006
I have a worksheet thats makes things easier by sorting to users needs. heproblem I have is I have totals in columns p:AJ rows 1:3. How can I prevent the user of sorting the whole worksheet. Most of my users have basic Excell understanding so sorting is usually the whole page. I've tried hiding and protecting the columns but must be doing some thing wrong. I just need them to only be able to sort column A:N. I'm already working on moving totals to a separate sheet to I know this will work but curious for future worksheets.
View 3 Replies
ADVERTISEMENT
Feb 26, 2010
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories:
Requirement: 10358
Title: Customer requirement 1
Text: This describes the requirements for a product for the customer[code].....
The text of the categories may begin in column A or B
What I need is a macro that will search each worksheet for a category, e.g., "Configuration:", copy the row where the keyword "Configuration" is found, and then have that row transposed and pasted to another WorkSheet (e.g., "Extracted Data") cell.
Data extracted from the next Worksheet would begin a new row in "Extracted Data"
Example:
Requirement1 Title Text Verification Method-Level ...
Requirement2 Title Text Verification Method-Level ...
Requirement3 Title Text Verification Method-Level ...
The Requirement# is best extracted from the WorkSheet tab since some of the worksheets are missing this information.
I can provide an example spreadsheet, however, I was not able to figure out how do that in this post.
I found a thread similar to this problem:
find and copy row
However, it only finds, copies and pastes for one keyword.
View 9 Replies
View Related
Feb 26, 2010
I am looking for a solution to an otherwise very tedious problem.
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories: ...
View 9 Replies
View Related
Jul 9, 2014
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
View 9 Replies
View Related
Aug 7, 2009
An old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Need figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.
Try pasting the following two event procedures into the Help sheet module:
'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub................
View 4 Replies
View Related
Feb 28, 2014
How do I write a sub to prevent all changes to a worksheet?
This is part of my thinking in covering all possible mishaps that could occur when working with sheet movement. If I could somehow introduce an active protection on either my Sheets(1) or Sheets("Main"), then I could prevent accidental writing to or removal of said sheet.
View 3 Replies
View Related
Aug 18, 2007
I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:
Sheet1.Protect Password="abc" UserInterfaceOnly:=True
However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection?
That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?
View 2 Replies
View Related
Oct 19, 2007
I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)? I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel. I have also already hidden all unused columns and rows so that they cannot oversize the window.
View 5 Replies
View Related
Feb 11, 2009
I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?
View 4 Replies
View Related
Aug 3, 2006
I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.
View 14 Replies
View Related
Jun 3, 2008
I have a spreadsheet which I'm using as a log-in front for a series of other workbooks. The usernames and passwords are stored hidden cells within a "very hidden" worksheet. The workbook is protected and VBA code itself is password protected.
In short no-one can directly view the passwords (unless the password to unprotect it all is known.
What I have found is that someone can use a formula to reference the cells containing the user details. "=a1" for example.
Is the a method hiding the contents of a cell from excel itself? I want the vba script to be able to see the value, but any "=a1" formulas to return a blank.
View 8 Replies
View Related
Jun 12, 2009
I have a entry form in which i want to register customers. the first field is the customer number (which is unique(created by me) for every customer). This is TextBox 1 in the document. I would like to search for duplicates in worksheet 2, collumn A, when pressing "enter" to move from TextBox 1 to TextBox 2. A search for duplicates should start and a message should appear " duplicate found" if found, otherwise continue to TextBox 2 for further entry of information.
Please see my attached document for clarification.
View 9 Replies
View Related
Sep 3, 2008
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
View 5 Replies
View Related
Feb 23, 2014
Basically every month I keep a track of my sales in a spreadsheet.
I've attached a sample file : Discrepancy Form.xlsx
Sometimes I don't get paid correctly. So rather then filling everything in manually, I want to simply put a "Y" (for yes, there's a discrepancy) or "N" (for no, there's no discrepancy" next to worksheet "Sales" but then have it display the values in worksheet "Discrepancy."
View 9 Replies
View Related
Feb 13, 2008
I have a worksheet (will be the last worksheet) that I want to use as a template for further worksheets.
I want the user to be able to run a macro (?) and copy the entire worksheet and then insert it to the right of all the worksheets as many times as needed.
View 9 Replies
View Related
Mar 31, 2009
I've a worksheet where I maintain the IT tickets that I handle. Whenever, the I column is updated with Implemented/Closed, the entire row should be copied to a new worksheet.
I've tried with other VBA scripts provided, but was not success with the same.
View 9 Replies
View Related
Jan 14, 2008
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application. ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error Goto ErrorHandler
ActiveSheet.Name = sName
On Error Goto 0
Sheet8.Select 'this is the overview sheet
Cells. Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate........................
View 2 Replies
View Related
Mar 31, 2014
So I have an option on my userform that the user input selected.
I need the data once written to the worksheet [Master] to move based on cell AD [Customer Divert / TBP Divert / Failed Delivery]
Example: Row 12 has Customer Divert in cell AD I need to keep a copy on the master sheet but also copy the entire row to the customer divert sheet.
Need code and here to input in the Code sheet?
View 7 Replies
View Related
Jun 29, 2006
I want to run a macro that looks thru column F of all my worksheets (in my entire workbook).
If it finds an x then I want it to copy that entire row and paste it into a worksheet called "old" or a new worksheet or a new document in word (it doesn't matter - just whatever is easiest for my little brain to understand).
I should end up with about 40 rows of data in the new sheet.
(I have some code that looks thru it all and colors the cell blue but I just don't know how to get it to copy and paste the entire row into a new worksheet.)
View 9 Replies
View Related
Jul 15, 2009
I have a report to compile where i have a master list of data with three columns.
A=Name B=Type C=Location
I have 3 Types to sort. These being D, E, F
I need to sort into three new worksheets from the first sheet the D's into one, the E's into another and the F's
into the last.
I have over 3000 different A's to sort by the Type.
View 9 Replies
View Related
Nov 7, 2009
trying to speed up my workbook, because all of the formulas depend on time and the now() function. It takes forever to add/change anything.
Here is a general description of my worksheet. It's for a game called tribal wars (online browser mmo). You have towns around you, which create resources to plunder. This resources build up over time, thus I need to use the now() function in basically every formula. Each row finds the distance to that location, the time since last attack, the estimated resources built up since the attack, and the number of troops to send.
If only there was some way to update now() only every 1 to 5 min instead of every time I hit a key...
View 10 Replies
View Related
Jun 26, 2008
Is there a formula/function in excel that can take all the cells in a column, lets say Column A, and paste/display it in another file without the spaces between the cells with values and without duplicates?
Worksheet1
Column A
1 Name
2
3 Mike
4 Rob
5 Ryan
6
7 Mindy
8 Paul
9
10 Rob
11
12 Mindy
13 Chris
Worksheet2
Column A
1 Name
2 Mike
3 Rob
4 Ryan
5 Mindy
6 Paul
7 Chris
8
9
10
11
12
13
View 11 Replies
View Related
Feb 20, 2012
I have a worksheet with about 20 columns of info. First of which is a primary key. Call it "Job Number." I would like to create a macro that runs down the list of primary keys searching for a specific Job Number I ask for in the new worksheet, in A1. It then copies and pastes the entire row where it found that job number into the new worksheet starting at row 6.
View 7 Replies
View Related
Feb 9, 2009
way to copy the entire row of data from one worksheet into another, within the same workbook, based on the criteria "Responsible." Here is a sample of the data: ..
View 9 Replies
View Related
Jun 11, 2009
Let me know how can a worksheet pick automatically equal = sign before manually feeding data
for example:
A B C
1 5+3 2+1 9+2
2 2+3 8+6 4+7
I want 8 in cell A1 and 5 in cell A2. I do not want to manually assigning/adding equal = sign to get sum 8 in cell A1
View 9 Replies
View Related
Oct 28, 2009
This might sound like a weird question, but is there any way to prohibit a user to using the sort function in a particular sheet?
Filtering for values is okay but sorting must be prohibited so that no rows "change place" in a sense.
View 4 Replies
View Related
Sep 22, 2013
I am attempting to create a workbook that will keep an inventory of items in a catalog that I own and want to order (worksheet1). Ultimately an order form would be filled in(on worksheet2). I would like to have the entire row of data copied (from worksheet1)to the next open/empty row on the order form (worksheet2), if any cell in column C (worksheet1) has data.
I have attached the workbook : Catalog Orders.xlsx
View 4 Replies
View Related
Dec 16, 2008
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
View 6 Replies
View Related
Jan 20, 2014
In Excel 2013 x64 (EN; CZ locale) I have this funny bug. I work on a large vba project and sometimes when I open it, every cell in every workbook that had default formatting now has this numberformat (shown as "Accounting")
"_-* #,##0.00 [$Kč-405]_-;-* #,##0.00 [$Kč-405]_-;_-* ""-""?? [$Kč-405]_-;_-@_-"
its seems that this formatting is assigned to styles --> Normal and it just messes up everything (pivots, slicers...) and cannot(!) be undone.
I have made some routines to check for this error on workbook.open and workbook.close and I also have file versioning. I check for the error regularly on every worksheet change, but it never comes up, nor does it whenever I close the workbook, so Im having hard time detecting when it occurs.
Sometimes when I try to open the workbook its just all messed up. When I go trough the versions, couple of them back still has the error which means it was already saved with it.
All I could figure out so far is that it sometimes happens when I try to copy some cell and paste it elsewhere (but later it works fine)
I'm 99.9% sure that my code is not causing it by accident or purpose. Now I just found the problem on different workbook that might have been opened at the same time. If you're interested, have a look here [URL] ......
View 2 Replies
View Related
Jan 13, 2008
how to sort based on the first two digit..
I need to sort the COMM CODE ( which is at Column no 3 )
View 14 Replies
View Related
