Prevent Worksheet Scrolling
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 Complete Thread with Replies
Related Forum Messages:
Prevent Scrolling Outside Range
What I have is a interesting task but unfortunity I am well out of my Depth so if you can help that would be GREAT.
<Snip by admin. Read the rules!>
PREVENT USER SCROLLING: Some VBA code for the workbook that for all
worksheets stops users from scolling outside of the set print area
once it is set on.
Prevent The User From Scrolling In A Sheet?
I have an Excel sheet that displays 50 rows and 20 columns when it is opened the first time. These rows and columns are the only ones that I want the user to be able to see. The user shouldn't be able to navigate the sheet by using the scroll bars or the wheel on the mouse. I have of course already removed the scroll bars but I don't know how to handle the scroll wheel problem.
Trap Worksheet Scrolling
When the user scrolls the worksheet (using a scroll wheel, or the scroll bars) I would like to have a VBA-code triggered, but in Excel 2003 there is no "VBA-event" for scrolling...
Is there any way of trapping that a scroll has happened, even if the selection didn't change?
Scrolling And Freezing Worksheet Areas
I am attempting to set a range of cells (say c5:h25) that can be scrolled through horizontally whilst the rest of the sheet remains frozen.
I'm trying to set this so the scrollable range will automatically expand horizontally as user data is added to the last (h etc) column.
Limiting Scrolling Area On A Worksheet
When I limit the scrolling area in a worksheet, and it works there and then, when I close and reopen the whorkbook, the limiting does not work.
This is how I do it:-
I Right-click the sheet tab for the sheet which I want to place a limit.
In the resulting Context menu, I choose View Code. The VBA editor appears and asks for the password to view the code. I keyin the password
I then set in my limit as B2:O25
Then I close the VBA Editor and display the worksheet where I try and I find it working.
I close and save the workbook.
When I reopen the limiting has disaapeared!
Worksheet Scrolling With Scroll Wheel Is Broken
I have a workbook with 8 sheets, one of which is the "main" sheet that holds all of my user data for logins at my job (I'm in IT). There are about 4300 users and 23 fields for each user, so there are 4300 rows in the sheet. Other sheets in the book are important, but not as much as this one.
The workbook has a total of over 1000 lines of VBA code right now, as I'm building a tool that finds users, adds users, modifies users, etc. (I know, Access would be smarter for this, but for some reason they want me to use an xls). Most of this code works with the sheet that I'm having the problem on.
This ONE sheet has seemed to disable the functionality of the scroll wheel, and I can't use Page Up or Page Down while in the sheet either. Also, if I select cells with VBA (Sheet2.Range(<range>).Select), it will select the cell, but it will not draw focus to that cell on screen (the sheet will not move at all). The other 7 sheets in the workbook have all regular functionality.
This just started happening a couple days ago, and I've been building the code for about a week, so I'm wondering if it's something in the code...?
Does anyone have any idea what might cause this?
Prevent Delete Worksheet
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
Prevent Worksheet Protection
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?
Prevent Pasting Data Into Worksheet
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
Prevent Referencing Hidden Worksheet
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.
Prevent Sorting Of Entire Worksheet
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.
Prevent Duplicate Entry From Textbox Into Different Worksheet
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.
Code To Prevent Editing & Selecting Of Range Worksheet
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.Range("e" & lLoop & ":g" & lLoop).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
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.
Prevent Worksheet Change Event Causing Chain Reaction
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, _
I've created a worksheet with split panes. I'm able to scroll through my data vertically and horizontally while keeping one area of th screen stationary. Here's my question:
Is there a way to limit the movement of my scroll bars so that it can't move beyond the viewable portion of my worksheet? In other words, I want the scrolling to end when there is nothing else to see but a field of empty cells.
Scrolling Of Several Windows
way of getting two sheets to scroll together, so when I scroll one window the other one moves too? Some text editors have that future (IIRC UltraEdit does)
I can't find a scroll event in the windows code to trigger a macro to run itself.
I can get around it by using a selection change event which would restrict me to moving around with the cursor keys/Pageup/down, but I've got 15,000 to review regularly in a short space of time and it would be quicker by mouse
Scrolling To Next Blank Row
[code/]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This macro scrolls the Report to TopLeft when you click
'anywhere in the Report columns
If Target.Column > 16 And Target.Column < 22 Then
.ScrollRow = 1
.ScrollColumn = 15
If Target.Column >= 1 And Target.Column < 15 Then
.ScrollRow = 1
.ScrollColumn = 1
...but I cant figure out how to scroll it down the list to the NextBlankRow-20 such that the previous 20 lines of data are shown to the top of the screen.
Scrolling In Userforms Without Scrollbars
I want is a form that has three different partsof it. Each part containing checkboxes, a spreadsheet, textboxes and labels. But I only want to display one of these parts at a time, depending on what the user clicks.
I know I could write code to move everything into the visible part of the form but that would be messy. I hoped I could find some way to instead move the visible part. Like if I had scrolled.
An example. Say the visible form is 100 high and 100 wide. When opening you see from the width 0-100 and height 0-100 (pixels or whatever those things sould be called). Upon pressing a button I instead want to see width 101-201 and height 101-201.
Scrolling Area Code
I have a sheet in which I want to limit the scrolling area to B2:B60 & D2:H60, in other words I don't want to include neither A2:A60 nor C2:C60 in my scrolling area.
Is there a way to do this?
I know I can lock the cells, however, pre-existing code will not work if cells are locked.
Scrolling Dynamic Chart
I have graphed a range of data that was generated using VB, but the data is a series of 6 models plotted against each other over a period of 6 years and the data is daily and as such would be useful to reduce the range to look at certain periods , is it possible to do this using the mouse to select a region or would something like embeded text boxes for the ranges of dates the user could enter be better, or would it be better to move away from excel for this part of it be better. Also is it possible to plot say range A1:A5 and A20:A25 against the the relevent dates to examine two separate periods side on side.
Scrolling Charts Two Lines
I have adapted Andy Pope's scrolling chart and have done some searching here on how to adapt this chart to plot at least two lines. For example, have sales data for the months of 2005 on one line and sales data for the months of 2006 on the other line so that corresponding months move together. I read a previous thread suggesting to change the source data to include the new data however I get a reference error message. I have attached the example that I am currently working on
Create Scrolling Xy Scatter Chart
I have downloaded a file called "scroller.xls" under the section "Excel charts". The file was made by Andy Pope and shows how to make a graph dynamic by combining Named Ranges and a scroll bar.
My chart is actually XY scatter chart with data points connected with smooth lines. I followed but when I start scrolling, the data points (smooth lines) got "eaten up" as I move.
I tried line chart and it works perfectly.
Does scrollbar work for XY scatter chart? Is there a different formula for XY scatter chart?
And I want the chart to be in new sheet, but I couldn't put the scrollbar in new sheet. I would like to know if it is possible when the chart is located in new sheet because my chart is quite large.
Scrolling Macro Buttons With Sheet
I have a spreadsheet containing a group of Macro Buttons, each button has been assigned a macro that enters specific text in the selected cell when the button pressed.
But as you scroll down the sheet, the group of buttons is left above. Is there a way to make the buttons scroll down (or up) along with the spreadsheet ?
Use Vlookup To Change Cells Without Scrolling
if it is possible to change a cell that the vlookup has found without scrolling all the way to that cell.
I have attached a small sample of my workbook. Once the vlookup has found the item I would like to change the number in G5 and make it change the number below in the OUT column that corresponds with the found item.
row 11 is found with vlookup. i want to be able to change G5 to the number 8which will make G11 the number 8. And then make it stay even after i have done vlookup on another item.
I believe this will require a VBA code. Also I am using Excel 2007. I have added a test page. It is saved in 2007 format. If anyone needs it saved in 2003 please ask.
Lock A Textbox From Moving While Scrolling
I know it's possible to lock objects (textboxes), via their properties. ( ie: to move or not with cells),
However, I'm trying to lock down a textbox so that when I scroll the main window, the entire page scrolls except the textbox, which remains in it's position.
A quick solution would be to freeze/split this area... but part of my work area is already utilizing a frozen partition.
I don't believe there is a way to freeze multiple areas.. or is there?
how to prevent a textbox from moving?
Shared Workbook Scrolling And Views
1. I have a shared and protected workboook that about 15 people update. For some reason, when people open it they can't always scroll. Anyone know why this happens and how to fix?
2. Is there a setting so that whoever opens the workbook will see the same view (instead of whatever view the last person saved the file in)?
Scrolling Through Listbox Item's With Buttons
within the userform contacts, when mouse-over the image two buttons are shown.
I want when one of these buttons are it displays the next image. the names of all images for the contact are loaded in userform1 listbox. also change the name of the label1.caption.
The part im having trouble with is knowing where the current picture name is loacated within the listbox.
on a side note, if insted of using a picture object i use a activex picture viewer thing. Can i play movies within the same object as being used for the pictures.
ScrollArea: Zoom Percentage Prevents Scrolling
I have a workbook with 4 worksheets in it. The first 2 worksheets have a restricted user area of A1:Z48.
Private Sub Worksheet_Activate()
ScrollArea = "A1:Z48"
On the first one of these sheets the user cannot scroll around the sheet unless they change the zoom percentage to 85% or above. The second sheet, which is almost identical, and has the same scroll area does not suffer from this problem.
Prevent Zeros Showing
I did a search 'cos I assumed this was so trivial it had to be out there, but couldn't find anything.
Have a row where one cell contains
How on earth do I specify the formatting so that if none of D3 to D5 contain any data, then the summed cell should show blank rather than (as it does) 0,00 ??
(FYI - am creating the SUM Formula via a VBA macro if that is relevant)
Prevent Changing Value In Cell
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.
Is there a better way to do this?
Userform And ESC - How To Prevent?
When user presses "ESC" key on an open userform, data he has changed in controls (simple textbox for example) gets resetted. Right? I'm seeing such behavior in Excel 2003. How about other versions?
I'd like to see some documentation about what exactly is going on, and how to prevent such unexpected feature (that forces data loss), but could not find.
I mean that i have no commandbutton with cancel=true. By default I want to keep the data user has entered/changed. Unfortunately this data reset finds place before any trappable event, or am i wrong?
I have not found any working way to capture keypresses on userform level. Userform has _KeyPress and other events, but those are not firing? If there is one, that would be helpful. Perhaps even on system level with some APIs? Altough that sounds way too complicated for a problem that should need only one little-known flag set. Perhaps there is such property somewhere?
Prevent Tab Switching
I made up a small subprocedure that searches through a large list of zipcodes (These zipcodes are in a separate sheet). Whenever I execute my subprocedure in another sheet, it jumps to the zipcode sheet and then goes back to where I originally executed the subprocedure.
Is there any way that I can prevent the sheets from switching back and forth?
How To Prevent User Error
I have a simple spreadsheet which requires a date (mm/dd/yy) in Column A. Columns B-F use formulas to break this date down into the month, quarter, year, etc. Users frequently need to add new rows at the top of the sheet just below the column headers.
To be sure the formulas in Columns B-F are included when adding new rows, I put together a simple macro which copies the top ten rows, inserts them at the top of the sheet, and then clears existing data from the new rows (keeping formulas intact).
I put a button on the sheet to run this macro. However, sometimes users insert new rows without using the macro. This results in no data in Columns B-F for these new rows because the formulas weren’t copied.
How can I prevent this from happening? If I protect the sheet and prevent users from inserting new rows, the macro won’t run. Is there something I can add to the macro that will allow it to run properly while the sheet is protected? Below is the macro code.
Private Sub CommandButton1_Click()
Application.CutCopyMode = False
Application.CutCopyMode = False
Prevent Saving And Allow Only Through Macro
Prevent saving and allow only through Macro
I am contracting an excel template and need to stop users saving the file using “Save” or “Save As”. I am able to do this by using the private sub below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
I also understand that by switching to “Designer” mode you can save the file and once re opened designer mode will be switched off. What I need is to allow saving using a macro, is there a code that I can insert in the macro to switch designer mode?
Prevent Toolbar Customizing
I want to restrict users from adding or removing toolbars from the workbook.
I have created a custom toolbar named xlUtilDemo1 which consists of 4 buttons that appear at the top of the screen. I was able to restrict the movement of the toolbar, yet the user still can click the down arrow of the toolbar or right click the toolbar to customize the toolbar (select other ones).
below is the code that creates the toolbar and buttons. can anyone tell me how to make the add or remove buttons and the customize toolbar option disabled?
Dim oBar As CommandBar
Dim oControl As CommandBarControl
Dim oControl2 As CommandBarControl
Dim oControl3 As CommandBarControl
Dim oControl4 As CommandBarControl
Set oBar = Application.CommandBars.Add
oBar.Name = "xlUtilDemo1"
oBar.Visible = True .......................
Prevent Sheet Deletion
Is it possible to prevent a particular sheet from being deleted?
I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.
Prevent Cell From Displaying - #VALUE
Am trying to create a simple spreadsheet with some multiplication functions on it. Using this example:
Cell A1 = 5 (multiplicand) x
Cell A2 = 10 (multiplier) =
Cell A3 = 50 (product)
All is well providing I have a number value in cells A1 and A2. When I don't, I would like them to remain blank, as well as A3, the product. The problem I face is when I remove the values in either A1 or A2, what's displayed in A3 turns to the word - #VALUE!. Which also shows up on the printed page.
My question is, like removing the checkmark for "zero values" under tools and options to prevent the "0"'s from being displayed, is there a way of preventing the word "#VALUE!" from being displayed when the values for the multiplicand and/or multiplier have been removed?
Prevent Workbook Being Closed
I display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.
In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.