I'm developing a few workbooks that may at some point be distributed amongst other people. If this happens, I don't want people sharing these workbooks around, and therefore I need a way of enforcing the users to only use a workbook on a single computer.
I have come up with a method that allows a workbook to open on a single machine only, and not others, but I had to devise this method myself. I was wondering if there are other ways of doing it, possibly quicker ways than my own, or if there is something already within Excel that caters for this?
When I protect my sheet, the module I have no longer works: an error message appears saying "Method failed..."
I searched around and found that if i used Me.Unprotect and Me.Protect at the beggining and end of the code, the problem would be solved, except that it isn't. After that, another message appears saying "Invalid use of me keyword"...
I have a very large workbook - there are only five sheets of this workbook that should be viewed by anyone. What I would like to do and just can't seem to find a solution is...
I would like to set the workbook so that when opening I use a password which will give me full access to anything in the workbook - upon read only, a user would be able to open the entire workbook without a password - but only view it's contents - not be able to do any adjustments.
I have locked cells which I do not want anyone to adjust the formulas to, then protected the sheet - this works, however, because there are so many sheets, it becomes very tedious to unlock and lock sheets to work in them.
I have a workbook with several worksheets in it. One worksheet for each manager. I only want the individual manager to see their individual worksheet and not those for the others. Is it possible to hide the worksheet and unhide it using an individual password, a seperate one for each manager ?
or alternativly, is it possible to hide say the columns and give each manager their own password that only allows them to unhide the columns for their worksheet. I really dont want to have to send out several seperate versions, just sending one workbook but passwording the individual worksheets within it would be ideal.
i would like the user to be able to unprotect the workbook via clicking a button. this button would first ask the user for a password before unprotecting the workbook.
I am building an Excel workbook for use by many users in a company. The Worksheet that all users will be able to see and access will reference a Data tab that contains sensitive data. I know how to hide and password protect that tab, but just wondering if anyone can tell me how secure that is? In other words, if someone really wanted to get that data, how easy is it for the password to be broken? I will need to prove to management that the data is secure.
I have a shared workbook is it at all possible to leave the cells unprotected so a user can enter data but prevent cells from being formated
for instance someone on night shift with nothing better to do has decided that black text on a white background is boring and changes it frequently to a rainbow of colors. and text types it is making a well laid out and good sheet look like something a child has done I would pursue this down a discipline line but it has proved impossible to nail down the culprit from the many users
I have a large workbook with 10+ sheets i need to protect all at once. I have created a macro in VBA below. It works, but I would like another user to be able to change formatting on the sheets while they are protected. below is my macro.
Sub ProtectAll() Dim wSheet As Worksheet Dim Pwd As String
Intention is to (automatically) fully protect each completed worksheet/workbook on "save"/"save as" in MS Excel 2007... Is this possible by means of a macro? Which one? Saving in a ".xlsm" format is required?
Is there a way to tell (using VBA in the workbook open event) whether a user has opened a particular file by double clicking on the file icon or by using the Open Command in Excel ?
my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?
The code is as follows:-
Public g_blnDate As Boolean
Public vartimer As Variant Const TimeOut = 60 'in minutes
Sub Salva() ActiveWorkbook.Save Call Tempo End Sub
My workbook contains a input sheet and multiple sheets with the data I compile. In the examples I provided, on each sheet "Server Name" is unique data to each sheet. I would like to have the ability on the Input sheet to click one of the server names and that take the user to the record on sheet 2. Not sure iif I need to be looking at a Macro or a formula to do this.
if I have been working with Excel previously and did my own "Replace" and applied it to the entire Workbook (i.e. changed the within field from ' Sheet' to: "Within: Workbook"
Then that field stays set to "Workbook" for subsequent Replace activities, including my VBA code !! Therefore if I do not manually go back and run one "Replace" and set the within field back to "Sheet", the VBA code will apply my Replacements to every sehhet and every cell in the entire workbook. Even if I have selected a Range of cells before issuing the command !! It ignores the selected Range and runs the "Selection.Replace" for the entire Workbook.
The "fix" I found on another site is to run a dummy command:
Set dummy = Worksheets(1).Range("A1:A1"). Find("Dummy", LookIn:=xlValues)
Which works. However, I am looking for a way to add a parameter to the "Selection.Replace" command that will cause it to search using the "Within: Sheet" setting. Otherwise I always have to remember to add that dummy line of code for every single Selection.Replace line of code.
I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed
i tried clearing the clipboard and resuming the code but that didn,t work
I have a macro that copys a worksheet a user-inputted number of times but throws an error after adding Sheet #62. The line it hangs on is:
Sheets(myTemplate).Copy After:=Sheets(i - 1)
Does anyone know if this is a problem with my code, a memory issue, or the workbook I'm using to copy? In the past when I've copied a lot of sheets in a workbook (to the same workbook), Excel sometimes won't let me add anymore but if I copy all the sheets to a new workbook it will sometimes let me add more sheets. I figured it was a problem with how Excel counts sheets.
Sub CopySheets() myTemplate = ActiveSheet.Name
'First, jump through the validation hoops Dim AddSheetQuestion As Variant
'Define the application input box question showAddSheetQuestion: AddSheetQuestion = Application.InputBox("Please enter the number of sheets you want to add," & vbCrLf & _ "or click the Cancel button to cancel the addition:", _ "How many sheets do you want to add?")
'Cancel or the X was clicked If AddSheetQuestion = False Then..................................
some method by which a user can insert a row that will then copy any forumulas, not their resultant value, in the row above into the new row. The best would be for a user to be able to just use Insert > Row from the toolbar and then have a macro automatically run, but I am willing to use any possible solutions.
I know that there are other posts on this subject, but I can not figure out how to make the information in them work for me. I know that I need to go to View Code in order to insert the code, but I am not clear on what to do after that in order for the code to function, even how to exactly enter the code (should I enter into General or Worksheet, into Sheet1 or ThisWorkbook, etc..). I saw someone mention something about using now() somehow to get code to automatically run or using toolbar buttons, both of which I do not understand how to do.
Dim toDel(), i As Long Dim RNG As Range, Cell As Long Set RNG = Range("d2:d2500") For Cell = 1 To RNG.Cells.Count If Application.CountIf(RNG, RNG(Cell)) > 1 Then
I am getting this error when I run the following VBA script.
Sub AutoShape3_Click() Sheets("Template").Select ActiveSheet.Copy After:=Sheets(14) Duplicate.Hide wksName.Show End Sub The break mode is highlighting ActiveSheet.Copy After:=Sheets(14) as the source of the error.
I've run this code many times before with no problem. The workbook has 48 worksheets in it. It's my understanding that Excel can handle many more worksheets, so that shouldn't be a problem.
With rngData ' clear existing filter .AutoFilter ' filter on Owner col F .AutoFilter field:=6, Criteria1:=varOwner ' check for count > 1 since heading row should always be visible If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then .SpecialCells(xlCellTypeVisible).Copy rngOutput End If 'clear filter .AutoFilter End With
Set rngOutput = wksSell.Range("A1")
It fails on the line highlighted in red, and from searches on this forum and google I think I need to specify more exactly which sheet it is. something like wksdata.SpecialCells.... but this doesn't work.
I've developed an administrative package in Excel for an After School Care programme. Essentially, there are 57 Child Records. Each record is stored on its own sheet. There are four other sheets in the workbook: three hidden templates, and the front page which holds a summary of all child attendance and balances.
Up until a short time ago, invoices were created from an "Invoices.xlt" file - the program would open the file as a new book, and copy the 1st Template page out until all invoices were created. This worked fine. I've recently changed this so the Invoice template is stored within the main workbook. After I did this, we started having problems.
We can run off up to 25-35 invoices fine (32 at this stage), but eventually it hits a point where the Activesheet.Copy command fails. After this point it is impossible to copy further sheets. I CAN, however, use Insert -> Worksheet. It is only the copy function that fails.
Because it's such a strange problem, I've uploaded an example with children's names changed. It can be found on [url]
The steps to recreate the problem are simple: Open the file, select all of the children's names (from Child to Child z), and click the "Create Invoices" button at the top.
When I call the following code from my Visual Basic 6 program to an excel spreadsheet, it runs great the first time, but then fails everytime after that unless I close vb6 and reopen it.
Private Sub lblGroundScheduleExcel_Click()
'return day of week 'MYHEADERDAY = Format(Now, "dddd")
From the current open book, I'm opening a 2nd book, then copying all worksheets from the 2nd book that meet criteria, into 1st book, (in the same order), but am getting Method 'Copy' of object '_Worksheet' failed error. What am I doing wrong? How to OVERWRITE worksheets?We'll be running the same process with new data, so also need to overwrite worksheets in 1st book.
Sub Build_Branch_File() Dim FileName As String Dim Wkb As Workbook Dim Ws As Worksheet Dim WNum As String Dim Tnum As String Dim RegionNo As Integer Dim Original_Wb As Workbook
With Application . ScreenUpdating = False .EnableEvents = False .DisplayAlerts = False End With Set Original_Wb = ThisWorkbook .........................
