Combining Two Workbooks Into One (hiding Sheets, Password Protection, Etc.)
Aug 26, 2008
='NAME OF SHEET'!$#
where # equals the exact cell in the other sheet I want to copy. I'm also starting to realize that with this formula, Senior Monkette and I could take Dingleberry and Bliddiboo and combine them into a more powerful and robust spreadsheet (called "Voltron!").
1. If we're going to be sending Voltron! to the client, all I would need to do is password protect, then hide the Dingleberry sheets so that the client doesn't look at them accidentally, right?
2. If I hide and protect the Dingleberry sheets, the cell information will still show up on the Bliddiboo sheet, right?
3. Since Senior Monkette isn't as Excel-savvy as I am, the entire process would have to be as painless as possible. What I was thinking of doing was having one master Voltron! where Senior Monkette could make her changes and updated. Then every week, save the entire thing, protect/hide the Dingleberry sheets, and then save a copy as a separate Voltron! file, marked by the date, which then gets shipped off to the client. Is there a macro I can run that will do that all with the press of one button? (And how do I install macros?)
View 10 Replies
ADVERTISEMENT
Jul 17, 2008
I had a Costs file which had a sheet per product (about 30) and a totals sheet with a basic 3D sum in each cell.
Columns A:E held the cost codes and descriptions and then column F onwards were a column per month showing any associated costs for a six year plan.
This file was cumbersome and not user-friendly, as they then linked into the 30 different product files, so I've effectively moved every sheet from this file into the relevant product file, so the cumbersome Costs file no longer exists.
This works much better. However, I still want a summary page to show me the total costs of all of these products.
Can I do some kind of SUMPRODUCT, or INDEX MATCH to take the cost for Code 13011234 for Jan 08 from each of the 30 files, without having to make a copy of each sheet or write a long winded suma+sumb etc?
NOTE: Not all sheets contain all codes (although they did for the previous 3D sums). My master list does, but I don't want any errors if code 13011234 is not found on one of the sheets.
Also, all of the sheets are now called COSTS within each of the individual files.
View 9 Replies
View Related
Sep 17, 2009
I have several workbooks that supervisors in a call center use to grade calls for quality.
Each supervisor has a seperate workbook, and after scoring the call, the supervisor runs a vb script that copies all of the data from the worksheet "observation" onto "sheet1" for storing the data.
So now I want to use another workbook to:
1. Copy each sheet1 from all of the supervisor workbooks onto a sheet named "cumulative" in a different workbook.
In a perfect world it would also:
2. Keep a count of how many observations were done by each supervisor each day
3. Keep a count of how many have been done week to date
4. Keep a count how many have been done total since January 1, 2009
Each workbook is kept on a network drive at s:supervisorsqadata and named - for example QAformMelissa.xls
I'm okay with keeping the master sheet in the same directory if that makes it easier to accomplish this.
View 8 Replies
View Related
May 27, 2009
When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
View 11 Replies
View Related
Aug 28, 2007
I use the following piece of code to show/hide certain worksheets in a workbook. To access the hidden sheets, a command button runs the code. It works very well, except that the password is openly displayed in the message box (as opposed to returning asterisks for the typed characters).
Sub togglesheets()
Dim Ws As Worksheet
Dim strPassword As String
strPassword = InputBox("Enter Password")
If strPassword "Password" Then MsgBox "Wrong Password": Exit Sub
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVisible Then
Ws.Visible = xlSheetVeryHidden
ElseIf Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
End If..............
View 9 Replies
View Related
Jun 5, 2014
I am trying to protect my file with a password which does not contain capital letters and contains less than 12 characters.
How can I do that?
View 4 Replies
View Related
Jan 27, 2010
I have tried Tools/Options/Security Password to modify, but it can still be saved as a copy to the network which becomes very confusing to many. If I use Tools/Potect Sheet, I need to re-enter the password for all sheets twice. I am trying have it so that everyone is read only, select people can edit (with same password). Password protect all sheets and the workbook with the same password everytime when the workbook is closed
Basically 2 questions
1) Is there a way of remembering a password so that when the workbook is closed it and all sheets are automatically protected with the same password every time
2) Is it possible to have it not copied unless it is opened by an editor
View 2 Replies
View Related
May 29, 2009
I have a macro that needs to unprotect the sheet before it does what it needs to do. The sheet has a password (i.e. it is not just protected). the macro runs fine but when I go back to the sheet and try to unprotect it, no password is required. simply clicking on Unprotect works and I am nopt prompted for the password in the macro.
I realise that they are not the most secure things anyway but the people who are going to using this will mess around enough to try and unprotect the sheet but if they were to be promted for the password they would stop looking.
View 3 Replies
View Related
Apr 13, 2012
I want to password protect a workbook but have seen softwares that claim to crack any password.
how safe is my book if I choose a really safe password with upper and lower case alpha numeric characters?
View 4 Replies
View Related
Jul 19, 2014
I have a spreadsheet that was given to me, which I used as a template. It already had password protection in it for each worksheet, and the macros are also written to unprotect before performing the actions, the re-protect at the end. Everything works fine except I cannot change the password. The thing is is that the password in the macro code is not the same as the actual password which you have to type in when unprotecting the worksheets.
For example the password in the code is "IPP" and the password you have to actually enter into the unprotect sheet box is "AAAABBBABBB&", yet all the macros still work like this. When I attempt to unprotect the sheet (with the AAAA... code) and re-write the password codes in the macros to say "###" then re-protect the sheet with the new password and run the macros it'll work fine, but when I save and close and then re-open the password required to unprotect the sheet reverts back to the "AAAABBB...." and then the macros stop working because the passwords don't match (even though they didn't match before!!!)
There is no encryption password or workbook password as far as I can tell, only the worksheet protection. Why cannot I no get the password changed?
View 6 Replies
View Related
May 3, 2007
I have an auto_open module that runs a couple of other small sub routines, the query I have is it possible to hide the active window from a sub?
Because it does not seem to work for me, I can step through (F8) but when I open the workbook it just flikers but does not hide it's self.
I want to hide the work book as there is nothing that the users need to see, it just has VBA code to prcess data. There is a menu item created on the command bar so the user can run the code.
I have a pasword on the VBA code would this cause issues?
Sub Auto_Open()
my_auto_open
Add_Workbook_Menu_And_Items
End Sub
Sub my_auto_open()
Dim w As Window
For Each w In Workbooks("Star_Plus_Hindi_MKII.xls").Windows
w.Visible = False
Next
End Sub
View 9 Replies
View Related
Aug 2, 2007
I have 1 workbook with 11 worksheets.
Is there a way users can be prompted for a password when a tab is clicked? if wrong or no password is entered, then show sheet 1 only.
1 main sheet, and 10 sub sheets. Any of the 10 tabs that get clicked need to be prompted for a password, each different.
View 9 Replies
View Related
Aug 28, 2007
I use the following piece of code to show/hide certain worksheets in a workbook. To access the hidden sheets, a command button runs the code. It works very well, except that the password is openly displayed in the message box (as opposed to returning asterisks for the typed characters).
Is there a way of achieving this?
Sub togglesheets()
'This asks for a password before show / hide data sheets will work
Dim Ws As Worksheet
Dim strPassword As String
strPassword = InputBox("Enter Password")
If strPassword "Password" Then MsgBox "Wrong Password": Exit Sub
'This will toggle show / hide the data sheets
Application.ScreenUpdating = False
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVisible Then
Ws.Visible = xlSheetVeryHidden
ElseIf Ws.Name = "Apr-Sep" And Ws.Visible = xlSheetVeryHidden Then
Ws.Visible = xlSheetVisible
End If
If Ws.Name = "Oct-Mar" And Ws.Visible = xlSheetVisible Then
Ws.Visible = xlSheetVeryHidden..........
View 9 Replies
View Related
Jul 24, 2008
I have a payroll spreadsheet that is going to be placed on our network server. My boss wants me to have this spreadsheet to be protected so that one person can get in, add hours and commission, and save it up on the server. Then another person can go in to that spreadsheet, open it up to get the info and use it to cut the checks. What my problem is that the person who is making the changes does not need to see the pay rate or the total dollar amount columns, but the person who gets the data out does. Neither one of these people really know how to hide and unhide columns in Excel, so I need it to have when one password opens it, the pay rate and total pay columns are hidden, but the other password will show those columns.
View 9 Replies
View Related
Oct 25, 2009
I want to be able to put password protection on a button in a spreadsheet. It doesn't need to be secure, as it's just to prevent accidental changes by 4 year olds.
My plan was to store the password value on a hidden worksheet, as I don't think there's a way to retain the value of a VBA variable when a workbook is closed. Is there a neater way to do it?
View 9 Replies
View Related
Dec 15, 2009
Is there a tool that can make an Excel application behave like a “real” software? Meaning, the user will still be able to do what I planned for him to do with the file, BUT it will be very difficult for the more-than-average user to “crack” any password protections I have applied to the file? (Something like an MDE file in Access, perhaps?) The reason I am asking is because I have invested a lot of time and efforts into creating an application that I wish to sell to some of my clients, and don’t want them to easily find their way into the formulas and macros I have placed in the file.
The protection tool does not have to be an Excel tool, necessarily, but it should not require any programming skills (because I don’t have them. If there isn’t such a tool? My application contains User Forms (for input), a Database (with Auto Filters and formulas) and some Pivot Table reports. BTW, my application was created in Excel 2003 but should also be used by users with Excel 2007.
View 2 Replies
View Related
Jun 29, 2006
Is there a way to password protect a sheet so that you have to enter a password just to access it. I dont want most users to be even able to view one sheet unless they have the password.
View 2 Replies
View Related
Jul 20, 2006
See the attachment. Once you enable it. A userform will ask you for a password. Enter "aes" that will let you in.
My question is, when I enter the wrong password the first time, a userform popsup and asks for the correct the password. once I do place the correct password in the 2nd attempt. The file gets deleted!
It is not suppose to happen. I understand if I entered the password wrong twice the deleteing should take place.
View 9 Replies
View Related
Jan 13, 2007
I have a workbook where I need to protect the sheets with a password. However, for certain macros contained in the workbook to run the sheets need to be unprotected. I know the code to unprotect the sheet but the password is required. I do not want other users to know this password.
What I need is the code to protect a worksheet with a password. Then, when a macro runs, to unprotect without user having to enter the password and reprotect with the original password when the macro has completed its run. note: I am NOT looking for any form of password breaking code!!
View 9 Replies
View Related
Jan 27, 2007
The spreadsheet basically copies data by looking in all workbooks in different folders (27 in total) and copies the data across. The only problem is that it keeps asking for the password on all of the spreadsheets when it is opening up the files (all have the same password) - how do I get the code to "know" the code and bypass asking for it?
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh. Cells. Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error Goto 0
End Function
View 7 Replies
View Related
May 14, 2007
I got this form RoyUK and was wondering if anyone know why it comes up with a debug here
Option Explicit
Dim sPw As String
Dim i As Long
Dim r As Long
Dim iChk As Integer
For Each rCl In .Range(Cells(r, 3), Cells(r, Columns.Count).End(xlToLeft))
View 9 Replies
View Related
Aug 1, 2007
i have a spreadsheet with about forty different sheets and all of these sheets are protected with the same password, and wheni come round to doing an amendment to the spreadsheet i have to unprotect and then re protect each sheet in turn, very time consuming
each sheet is naturally named different
View 9 Replies
View Related
Dec 8, 2008
I have a button that executes a macro which basically activates a worksheet. I wish to put a password which would allow only certain users to access this worksheet through this button.
View 2 Replies
View Related
Jan 24, 2010
All my macros in Module 1 work OK when I password protect my Excel 2003 worksheet named: Data. The only code that does not work when I password protect my worksheet is the Private Sub shown below. If I do not use a password, it works OK. As soon as I enter a password to protect my worksheet, the code below no longer works.
In ThisWorkbook, the following code shows the password to be password. In reality, there is a different password.
View 10 Replies
View Related
Sep 29, 2011
I am trying to change a password on a workbook. I have already gone to encrypt document and changed the password. However, when I open the workbook again, my new password works but another popup shows and asks me for another password for write access. That second password was my original password, and I don't know how to change that.
View 1 Replies
View Related
Jul 14, 2013
I`m struggling to insert password protection to code.If Yes is selected from the messagebox then a messagebox opens and asks for a password.
Code:
Sub DeleteRanges()
If MsgBox("Are you sure you want to delete data ?", vbYesNoCancel + vbExclamation, "Delete data") = vbYes Then
With Sheets("Score")
For i = 3 To 273 Step 10
.Range("D" & i).Resize(7, 11).ClearContents
Next
End With
End If
End Sub
View 2 Replies
View Related
Dec 19, 2006
how can you set up a workbook to display different sheets for different users depending upon what password they input to open the workbook? The manager should see all sheets but employees only data entry sheets.
View 9 Replies
View Related
Feb 26, 2007
My macros are set up with hard-coded passwords to protect and unprotect the sheets where they run.
But I need a way for the macros to refer to a user-provided password without having the user edit the macos themselves. The simplist approach (I suppose) would be to have a worksheet for this with a cell for the user to enter the password he/she wants. The macro protect/unprotect code would then look at the cell to get the password.
View 9 Replies
View Related
Oct 23, 2011
I have many spreadsheets in Excel 2007 that are password protected to open. How can I remove this function???
View 5 Replies
View Related
Jul 31, 2005
I am trying to import data from a website protected by ID and password. I do have the login ID and Password to login to the website.
I am importing data from the website to my excel sheet to track some shipping informations. I do have the 'Import to Excel' option but I would rather open the
spreadsheet and click Refresh Data and have all the Data imported with click of a button.
I have already tried to use Import External Data but it is not working.
View 9 Replies
View Related
