I am using the macro below to import every spreadsheet into a folder. I would like to modify it to check the sheets it is importing for sheet protection. If the sheet protection is turned on, I would like it to turn it off. The password for the sheet protection will be "PIR".
View 3 RepliesI am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?
View 4 Replies View Relatedi 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
I've been using the attached spreadsheet to record daily sales for a while now, however, we've recently intorduced a new product and so I've had to add a new column to record daily sales of it.
On the spreadsheet is a 'New Week' button. Pressing this deletes all entries and moves the week numbers on by 1, so giving me a blank template each week. However, after the addition of the new column I'm now getting a bug error message. I've adjusted the code to take account of the new column but I'm still getting a bug error. When I press 'debug' it's highlighting something I have changed so I'm a little confused why I'm getting the bug error. Would someone have a look for me. I only seem to get the bug when there is protection on the sheet, I need protection. The password is PHUKET38
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
I'm running a macro on Workbook Opening that checks to see if the sheet is protected, and if it is, unprotect it. I have used the code (simplified):
If Activesheet.Protect = True Then
Activesheet.Unprotect
End If
Unfortunately this seems to protect the sheet if it isn't already protected. Have I used the wrong first line perhaps?
I have a command that opens up another workbook. This workbook is sometimes open on another computer, so that the book opens up on my computer in write-protected mode (shows in the top of the window). Usually, when opening other books through the Excel functionality, a message box appears if the file is open and warns that the file is in use by another user. However, with the code I have at the moment, this is not the case when I try to open with my command. See code below:
Private Sub cmdOpenBook_Click()
'Open book
ChDir "I:DevelopmentSpecialUtvikling"
Workbooks.Open Filename:= _
"I:DevelopmentSpecialUtviklingJointApp.xls"
End Sub
Is there any way of displaying a msgbox warning when opening another file through VBA?
I need a macro that would sort out the true value in column A, and paste it to another sheet (range A77)
View 10 Replies View RelatedI have one excel file with three sheets marked 1,2 & 4
Sheet 1. is the place we add the description and the member number. When a member number is entered it gets the member name from sheet 4.
Sheet 2 is the page I need to change. Currently we can enter any score number in each box between 1 to 5. If we try to enter other numbers we get an error message.
I need to change the form so we can enter 1 to 10. Any other number needs to generate the error message.
I'm having a macro for search and all within the same file as folow:
Sheet"Search" has this vba
Code:
Option Explicit
Private Sub Worksheet_Activate()
[c3] = "Type your search here."
[c3].Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[Code] ........
Sheet"Data" is my data (which is refer to above vba as Const SEARCH_SHEET = "data")
My needs here, i need to move Sheet"Data" to another file then how can I linke above vba to Sheet"Data" that moved to another file?
Infact, that file will be as addins file (.xlam) .
I want to turn Sheet 2 into Sheet ASSET_Report, you can use the below convertion which is named range "Mapping" ...
View 9 Replies View RelatedWhen I protect the entire sheet, I can click in a cell and see what's in it, when I protect just a portion of the sheet, I can only click in the unprotected cells. Is this an Excel thing, or am I doing something wrong when protecting? I would like to see what the formula is in the cell when protected, but I can't click on the cell, what's the deal?
View 8 Replies View RelatedIs it true that the sheet protection in excel is practicaly none ?
Not even excel 2002 (XP) prevents simple cracking software from removing sheet protection.
Are there any master keys that cause that ?
Is there a way to protect a sheet with strong encryption ?
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 RelatedI 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))
I found the code below and it works perfectly if I want to copy all the other sheets to a master sheet. But, I need to specify specific sheets. Basically I have a workbook consisting of multiple sheets and multiple "master" sheets so I need to specify in the code which sheets it should be copying.
[Code] ........
I am building a model for consolidating a number of different companies (possible as many as 30). I have a worksheet for each company and then a consolidated worksheet. I can create a consolidated spreadsheet that just adds all the sheets up but I would like to be able to include or not include a sheet in the consolidate worksheet through a simple yes or no cell. I saw someone do this a few years ago with a mining company where each mine had its own page and you could activate / deactivate the mine for the consolidated (but the mine page itself would be unaffected) but I can't remember how they did it.
View 1 Replies View RelatedI need to run two macro when I modify a value in a cell
Here is the code for the macros:
Due to the lack of time and not being able to make it work otherwise this is what I have right now:
If Range("g59") = 1 Then
Sheet1.Activate
Application.Run "Edit_view"
Sheet3.Activate
Application.Run "Edit_view"
[Code] ...
There was a formula to enter sheets names in a cell range (ie: A1 would have value SHEET1, A2 SHEET2, etc) and use a FOR loop to cycle through the named sheets.
I have an excel workbook I'm working on, and it needs to be protected. I have some groups that can be expanded or hidden, but once the sheet is protected you can no longer use the little button to hide or expand the group.
Any way to transfer the sheet protection from one worksheet to another worksheet via VBA.
Currently, the user selects a name from a drop down list, it unhides a workbook of the same name, copies from that workbook, and places the copied information in an area of the sheet with the drop down list. I would like the protection settings (in this case, selectivly unlocked cells to allow users to easily tab through and not make changes to data that should not be changed) to be copied with this information. Is this possible?
I have a piece of code with a couple of errors. The code selects a certain row and then copies and inserts this row on the last line with data, the number of rows is decided by the user via a form. The line of code with the error is highlighted, think it is a syntax/grammar error.
Code:
Sub InsertRows()
'
' Inserting Rows
ActiveSheet.Unprotect
FormRowInsert.Show
Application.ScreenUpdating = False
[code]....
I have a workbook with about 70 sheets, some are specific for data entry, others for data review.
I share the workbook with my data entry team, and lock the review sheets so that no one can modify the data on them or change the formating. End users review the final protected sheets for the information that is appropriate to them.
I need to track the status of the protection of each sheet, such that I can edit the sheets as needed, then protect / lock them once I am done.
Is there a way to indicate, in a cell within the indvidual sheets, the status of protection for that sheet?
For example, if the sheet is protected, the Cell would read "LOCKED" and if the sheet is not protected, the cell would read "EDIT".
I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?
View 4 Replies View RelatedI have a summary sheets for my report that my department no longer wants to use. Rather they want to have a longer, more detailed version of the same thing, but rather than 1 sheet summarizing it all, they want it 1 widget, 1 sheet.
How can I have my Checksheet tab (see upload), turned into multiple tabs. In the specific example I provided, there are 10 widgets, so therefore I created the result in 10 tabs.
How can I get a macro to turn Checksheet tab into the next 10 tabs (see excel upload)?
I have attached the excel sheet of how my summary sheet is, and how it's supposed to look like.
I have this macro that I use for taking information based column B grouping it together and putting in a separate worksheet.
What I would like to do is in the master sheet (sheet1) to create a hyperlink for those listed in column B so I can click on it and it takes me right to that worksheet.
Sub exporttoWS()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
Sheets("Sheet1").Select
Range("A1").Select
'looking at the full length of the file
LR = Range("A" & Rows.Count).End(xlUp).Row
'sheet needs to be named sheet1, all data should begin on row 3
[code].......
I have this macro that I am using but want to automate the file it uses. This file has the last 5 text/digits change each month, e.g.
Internet_GB_Report_Feb07
Internet_GB_Report_Mar07
Clearly the next file would be Apr07. I would like the macro to identify this automatically and use the correct file without having to go into the macro every month and change the last 5 text/digits. If you have any ideas I would be very grateful to hear from you. The Macro is below:
[/code]strPath = "c:Internet"
strFilename = "INTERNET_GB_FEB07"
strThisWkb = ActiveWorkbook.Name
intReports = Workbooks(strThisWkb).Sheets("Lookups").Cells(1, 2)
Code:
i have this macro made by another member here that looks for "T" in column 10 and deletes entire row if in that row has a "T". I need it modified to also delete if it contains "FT" and also erase if cells is blank.
Sub Macro8()
Sheets("FORMERS").Select
'deletes entire rows based on a text on one row (this case errases all that have a "T" in column 10 (column J)
Dim DeleteValue1 As String
Dim rng1 As Range
Dim calcmode1 As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With...........................................
I use the merge button a lot for a group of cells in a column. However then I have to edit the formula in the merged cells.
I'm hoping there is a macro that can be made to bring these together.
Here's an example of what I do:
select range C2:C4. Cell C2 contains the formula =sum(B2)
merge these cells.
edit formula in new merged cell to be =sum(B2:B4)
Can I group this process into one macro?
I am trying to Protect a Sheet to lock the width of just one column. Does the lock apply to all the columns in the worksheet or can it apply to a single column only. I can't seem to get this to lock just one column-if it is possible.
View 1 Replies View Related