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.
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.
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.
I'm currently creating a workbook whereby I have (regrettably) highlighted entire columns and formatted these columns to show borders. The reason I did this was because drawing them in manually (I thought) would take much longer with the way the worksheet is laid out.
Obviously highlighting the entire column has meant that borders were drawn all the way down to the bottom row of the workbook, and so the vertical scroll bar cannot effectively be 'dragged' up and down as even a tiny movement will cause it to scroll right past the worksheet.
I have tried running a macro to clear contents and formatting for all the unused rows, but the scroll bar remains to be a pain.
I want the scroll bar to only scroll down to a few cells below the available work space like it should do.
I have a worksheet with a column("A:A") of about 1000names. The user will start at the top and work down, entering stuff into other columns for some ofthe names. My need is: How do you automatically scroll, with code, say row 48to the top of the worksheet when the user reaches row 48 with?.
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?
able to keep a chart stationary while scrolling through a worksheet entering data. A few points:
1) 'Freeze pane' wouldn't work for me since the chart is too large and I'd prefer to have the data sheet unfrozen for easy perusal.
2) I saw mention of opening a separate window with the chart in it, while having the workbook window aligned next to it. I don't know if that can work, but I'd also prefer to just have the chart on the worksheet and stationary.
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 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...?
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.
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................
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.
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:
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?
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?
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.
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.
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.
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.
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.
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........................
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.
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
[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 With ActiveWindow .ScrollRow = 1 .ScrollColumn = 15 End With End If
If Target.Column >= 1 And Target.Column < 15 Then With ActiveWindow .ScrollRow = 1 .ScrollColumn = 1 End With
End If End Sub[code/]
...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.
I want to return the value of E2 from sheet, "ALL INFO" and paste it in B2 on my current sheet. When I scroll this formula to right, I want the next to be the value of J2 from sheet, "ALL INFO," etc.
Ive seen on someone elses workbook the first 6 columns are fixed (always in view) then you can still scroll across but those first six columns always stay in view.
I'm having a problem working with a protected document made by another user.
The sheet has a vertical split at column F, and when I use the arrow keys to scroll right beyond this line (ie from column G and beyond), when I reach the last column (BB), it just loops straight back to the beginning (column F). This also happens in the same direction.
How I can stop this happening, so that it just stops when it reaches the end of the document?
There doesn't appear to be any coding on the sheet.
