I have one spreadsheet that is locked and I cannot modify, with a button I want to press from a fresh new one that I have created.
The first one is private and cannot modify it.
Now, what code should I use in mine so I can "click" the button of the other one when I press it?
I want to create a completely uneditable locked workbook.
I know how to do the simple stuff like unlocking cells for the purpose of entering data values and locking the rest of the sheet.
What I want to do is also lock out users from changing headers, footers, etc.
Ultimately, what I'd like is, when the file is launched by the user, all that is displayed is the sheet with no toolbars and no menu bar. Finally, the last thing, is to have 'print' button (with obvious macro built in).
Simply put, this is an electronic version of a paper worksheet, so no saving is required either.
I have had a workbook that has 2 sheets.
When I protect the main sheet with a password, the unlocked cells are also locked.
When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.
When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.
I also am using Freeze Panes. Removed the Freeze and still no good.
If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.
If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.
I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.
View 6 Replies View RelatedI'm using three different workbooks for the macro I've designed. The macro works exactly as I planned it would, but I'm getting a curious side-effect of running the macro. I have over 5,000 workbooks that I need to open, unprotect, change (mostly by copy/paste), and reprotect. It's a fairly straight forward macro, but it is my first, and I must be overlooking something. When the macro is complete, cells that were not modified via the macro in any planned/anticipated way are being unlocked. This has left me puzzled and frustrated. The files are opened, unprotected, the cells are copied over as planned, reprotected, and closed/saved. Everything works perfectly, but the cells in parts of the workbook (Filename) (which were locked before the macro ran) have been unlocked and are no longer protected when the workbook is reprotected. Again, these cells were not within the ranges modified, and I'd rather not have to format every cell in every one of the 5 sheets of (Filename) to be locked.
File names are:
"Finished Goods Inventory TREE (ToDMSI).xls" (alias: Workfile - sheet providing file path to be updated)
"Random_workbook_selected_from_previous_filename.xls" (alias: FileName - the copy-to file being updated)
"MACRO TEST BOM Master.xls" (the copy-from file always open)
Comma Delimited table layout in "Finished Goods Inventory TREE (ToDMSI).xls":
Col A, Col B, Col C, Col D
Customer ID,Item Code,File Name, Directory
4FRE01,4FRE01-0001,4FRE01-0001.xls,4FRONT
Here's the
Public FileName
Public Workfile
Public ItemCode
Public CustCode
Sub OpenBOMSeq()
i'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
View 8 Replies View RelatedI have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
View 4 Replies View RelatedI have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
I have this nice macro that copies data from another workbook. I want to make it to also add the filename of the workbook (for example data.xlsx) it copies the data from, in a cell on the actual workbook (for example on sheet "Combined", Cell A10). What should I add to my macro?
View 3 Replies View RelatedI am creating a spreadsheet to provide to a group of about 40 people. The spreadsheet allows each member to track the referrals they receive and give. I need to plan on creating updates for the spreadsheet and want to make it so they can easily transfer their data to the most current version in the future. Here is a breakdown of the workbook:
Worksheets:
Members
Lists
Referrals Given
Referrals Received
Totals
Formulas
I need to copy the data that they have input from the following worksheets:
Referrals Given
Referrals Received
Here is a breakdown of the "Referrals Received" worksheet:
A1:V4 - Data that will not change and includes many named cells
A5:Nxxx - Dynamic Named Range where they input their data
O5:Vxxx - Contains formulas
I need to be able to copy
A5:Vxxxxx
from the old workbook to the new
Some side items to note:
There is VBA in the worksheet of
Private Sub Worksheet_Change(ByVal Target As Range)
and included in the code is an Unprotect & Protect code to always keep the sheet protected
I figure whatever works for this sheet will work for all the sheets.
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
View 2 Replies View RelatedI need to copy several macros from the Personal Macro workbook to a new workbook that I need to take to another computer. I need to be able to run the macros on that computer. I can only record macros and make minor changes in the VB editor. I do not know VB code.
View 5 Replies View RelatedI have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that:
A B C
DAYTRIPCUST
I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed.
How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
I try to move the cursor using the keyboard arrows it stays on the cell it is on and the whole spreadsheet page moves across the screen in the direction of the arrow. So how do I restore cursor movement to the arrows.
View 3 Replies View RelatedIn keeping with their monopoly on code, Microsoft has, of course, locked all of the included XL add-ins (the code part).
I was interested in looking at how some of these add-ins functioned, seems like it could be fairly educational.
how to unlock the modules that come with XL's add-ins?
I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
View 2 Replies View Relatedthe following scenario.
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file()
Dim tDate As String
Dim FileSaveName As String
Dim fPath As String
tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy")
'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
[Code]...
I have a spreasheet with one locked collum that I don't want to be edited. Is there any way of filtering while this collum is locked? It seems to be that once it locks the collum it also locks the filters?
View 6 Replies View Relatedhow to lock a formula in a cell, protect the sheet but still allow it to recalculate when new data is entered in a unlocked cell that feeds the formula? I am using Excel 2003.
View 5 Replies View Relatedkindly help in jumping locked cell in a proteced worksheet..
View 9 Replies View RelatedSub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ErrHan:
Exit Sub
End Sub
I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.
I recently added two excel files to my startup, I receive the file in use error that the personal.xls is locked for editing box when the second file starts to execute. I can open them separately using their icons with no problem, what's the problem with opening them using the startup function. XP professional version 2002 SP2.
View 9 Replies View RelatedIn the Properties for every Text box I have on my forms it's not letting me change any of the colours but I can change the fonts etc, I'm using Excel 2003.
View 3 Replies View RelatedUsing vba, I want to be able to tell if the vba code is locked. Using the below function I can tell most of the time. The only problem is if the " Lock project for viewing" isn't checked the below function will say it isn't protected. Is there a way to tell if it is protected even if they didn't check "Lock project for viewing".
Function ProtectedVBProject(ByVal wb As Workbook) As Boolean
' returns TRUE if the VB project in the active document is protected
Dim VBC As Integer
VBC = -1
On Error Resume Next
VBC = wb.VBProject.VBComponents.Count
On Error Goto 0
If VBC = -1 Then
ProtectedVBProject = True
Else
ProtectedVBProject = False
End If
End Function
Any way to make it so that a cell can remain locked but be able to click a hyperlink? I need the cell to remained locked so nobody can alter the hyperlink name and value but I would like users to be able to click it and have it open in their browser.
View 9 Replies View RelatedI have an application that I made for a company. the editing is locked on each tab except for the blanks for people to either check mark a box or fill in the space provided for an answer to each question. There are around 30 tabs. Sometimes i open already completed applications since most of the answerer will be the same. My question is. is there a way to select say 6 tabs and clear out all the data that someone had typed in (check marks, answers in space provided?)
View 4 Replies View RelatedI have created a spreadsheet that will be viewed/used by various users (read only as I have protected the sheet except from three fields), although I would like to give them the ability to copy the contents of a specific cell that is protected.
I have tried to give them the ability to select the locked cells and then copy, but that means that the formulae in the cell is copied as well.
how I can let people copy a locked cell without the formulae?